Microsoft SQL Server 2019 on Ubuntu - SQL Server Agent

Introduction

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, run mssql-conf and set the parameter sqlagent.enabled to true

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 mssql-server :

root@vps$ systemctl restart mssql-server

In the SQL Agent log file :

2019-05-31 13:31:46 - ? [146] Request servicer engine started
2019-05-31 13:31:46 - ? [167] Populating job cache...
2019-05-31 13:31:46 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2019-05-31 13:31:46 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient...
2019-05-31 13:31:46 - ? [133] Support engine started
2019-05-31 13:31:47 - ? [168] There are 1 job(s) [0 disabled] in the job cache
2019-05-31 13:31:47 - ? [170] Populating alert cache...
2019-05-31 13:31:47 - ? [171] There are 0 alert(s) in the alert cache
2019-05-31 13:31:47 - ? [101] 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 :

/var/opt/mssql/mssql.conf

[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 msdb database, no difference compared to SQL Server on Windows :

sqlcmd -Usa
         
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 sysadmin rights. 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, the service 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 :

sqlcmd -Usa
         
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 - ? [157] Refreshing schedule 8 (of job 0x00000000000000000000000000000000) for UPDATE [requested by: sa]
2019-05-31 11:51:41 - ! [298] SQLServer Error: 10054, TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01]
2019-05-31 11:51:41 - ! [298] SQLServer Error: 10054, Communication link failure [SQLSTATE 08S01]
2019-05-31 11:51:41 - ! [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01]
2019-05-31 11:51:41 - ! [298] 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 - ! [000] 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 sstmp binary. Still don’t know why, probably a bug (SQL Server 2019 is installed on a non supported platform here).

mssql@vps$ ssmtp user@example.com
To: user@example.com
From: user@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-conf.

mssql@vps$ /opt/mssql/bin/mssql-conf set sqlagent.databasemailprofile MSSQLVPS

Conclusion

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.