A previous article has been published about how installing SQL Server 2019 on Ubuntu 18.04 : Microsoft SQL Server 2019 on Linux Ubuntu 18.04, installation and configuration.
And the SQL Server agent on Linux ? Yes it is ready, BUT… there is one major inconvenient described in this article.
Enabling SQL Server agent
Enabling SQL Server agent is very easy. As user
mssql-conf and set the parameter
mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.enabled true
This parameter is a static one, the service
mssql-server must be restarted.
Some other custom parameters can be set before restarting the service (error logging verbosity, log file…) :
mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.errorlogfile /opt/mssql/dba/srvmssql/log/sqlagent.log mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.errorlogginglevel 4 mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.startupwaitforalldb 0
Restart the service
root@vps$ systemctl restart mssql-server
In the SQL Agent log file :
2019-05-31 13:31:46 - ?  Request servicer engine started 2019-05-31 13:31:46 - ?  Populating job cache... 2019-05-31 13:31:46 - +  An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2019-05-31 13:31:46 - ?  Starting SQLServerAgent Monitor using '' as the notification recipient... 2019-05-31 13:31:46 - ?  Support engine started 2019-05-31 13:31:47 - ?  There are 1 job(s) [0 disabled] in the job cache 2019-05-31 13:31:47 - ?  Populating alert cache... 2019-05-31 13:31:47 - ?  There are 0 alert(s) in the alert cache 2019-05-31 13:31:47 - ?  SQLServerAgent service successfully started
There are very few parameters for SQL Server agent, run
mssql-conf list to get the list :
mssql@vps$ /opt/mssql/bin/mssql-conf list | grep 'sqlagent'
sqlagent.databasemailprofile SQL Agent Database Mail profile name sqlagent.enabled Enable or disable SQLAgent sqlagent.errorlogfile SQL Agent log file path sqlagent.errorlogginglevel SQL Agent logging level bitmask - 1=Errors, 2=Warnings, 4=Info sqlagent.startupwaitforalldb Set to 1 (default) if SqlAgent should wait for all databases on startup; set to 0 to wait for MSDB only
These settings are stored in the file
/var/opt/mssql/mssql.conf in the section
[sqlagent] enabled = true errorlogfile = /opt/mssql/dba/srvmssql/log/sqlagent.log errorlogginglevel = 4 startupwaitforalldb = 0
Inconvenients : security, controlling SQL Server Agent
When enabled, SQL Server Agent threads within SQL Server engine run in
no difference compared to SQL Server on Windows :
exec sp_who2 go
51 ... NT AUTHORITY\NETWORK SERVICE vps msdb AWAITING COMMAND ... 05/31 11:24:49 SQLAgent - Generic Refresher 52 ... NT AUTHORITY\NETWORK SERVICE vps msdb AWAITING COMMAND ... 05/31 11:00:38 SQLAgent - Email Logger 56 ... NT AUTHORITY\NETWORK SERVICE vps msdb AWAITING COMMAND ... 05/31 11:25:01 SQLAgent - Job invocation engine
Now first issue : SQL Server agent runs with the builtin system login
NT AUTHORITY\NETWORK SERVICE with
There is (for the moment ?) no level of control in order to change the service account for SQL Server agent.
Second one : how to restart SQL Server Agent without having to restart the whole server MS SQL Server ? For the moment, no solution,
mssql-server must be restarted and that’s a big issue.
Maybe in the next versions these 2 issues will be solved as they are in fact intimely correlated.
Disabling/enabling the SQL Server agent with
mssql-conf does not work as it is a static parameter, the service
mssql-server has to be restarted.
As a workaround, a test has been conducted by killing only SQL Server Agent subprocesses :
kill 51 go kill 52 go kill 56 go
No luck, SQL Server agent is unable to restart properly on its own with many errors reported in cascade. In any case, this solution is not clean.
2019-05-31 11:51:41 - ?  Refreshing schedule 8 (of job 0x00000000000000000000000000000000) for UPDATE [requested by: sa] 2019-05-31 11:51:41 - !  SQLServer Error: 10054, TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01] 2019-05-31 11:51:41 - !  SQLServer Error: 10054, Communication link failure [SQLSTATE 08S01] 2019-05-31 11:51:41 - !  SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] 2019-05-31 11:51:41 - !  SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_help_schedule', database 'msdb', schema 'dbo'. [SQLSTATE 42000] 2019-05-31 11:51:41 - !  Failed to retrieve schedule 8 (for job 0x00000000000000000000000000000000) from the server ...
Building and running a job
About building, scheduling and running jobs, no difference compared to Windows, everything seems perfect with T-SQL commands and with the SQL Server Management Studio 18 graphical user interface.
Just one issue with emails to be sent through the SQL Server Database Mail daemon (alerting…), unable to implement this feature used by many companies. Emails are
stuck in the queue with a timeout error. There was no firewall problem or bad parameters (SMTP server name, port…) as emails could be sent successfully with
binary. Still don’t know why, probably a bug (SQL Server 2019 is installed on a non supported platform here).
mssql@vps$ ssmtp email@example.com To: firstname.lastname@example.org From: email@example.com Subject: Test Test from MSSQL mssql@vps$
However, this feature is supported : DB Mail and Email Alerts with SQL Agent on Linux.
The email profile for the SQL Server agent is set with
mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.databasemailprofile MSSQLVPS
One missing major feature for SQL Server agent on Linux : the ability to control the SQL Server Agent (start/stop/restart) without
restarting the service
mssql-server, it is fully integrated in the engine unlike SQL Server Agent on Windows defined as
a separate service.
In environments using intensely SQL Server jobs, that’s a big issue : if a job is stuck for any reason, as SQL Server Agent is not a separated service, the whole SQL Server engine must be restarted even if we just want to restart the SQL Server Agent in order to solve the issue. Maybe in the next versions, it may change.
Database mail feature could not be used successfully but surely for another reason (unsupported platform…). To be continued.