Enabling SQL Server Agent on Linux

I spent a good ten minutes staring at the red "Agent XPs disabled" icon on a fresh Linux instance before I remembered that SQL Server 2017 on Linux ships without the Agent. It's a separate package, mssql-server-agent, that installs alongside the engine. Once it's there you get the same jobs, schedules, operators and alerts as on Windows, which is what you need for scheduled backups, index maintenance and log shipping.

I'm assuming SQL Server 2017 (GA build 14.0.1000 or later) is already set up with mssql-conf setup, the Microsoft repository is registered, and you have sqlcmd from mssql-tools or SSMS 17.3 on a Windows box.

Step 1: Install the Agent package

Ubuntu 16.04:

sudo apt-get update
sudo apt-get install -y mssql-server-agent
sudo systemctl restart mssql-server

Red Hat Enterprise Linux 7 / CentOS 7:

sudo yum install -y mssql-server-agent
sudo systemctl restart mssql-server

The Agent runs inside the mssql-server process on Linux; there is no separate SQLSERVERAGENT service to start. The restart is what activates it.

Step 2: Confirm Agent XPs are enabled

sqlcmd -S localhost -U sa -P 'YourSaPassword' -Q "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Agent XPs';"

run_value should now be 1. If it is 0, the Agent package was not picked up; check /var/opt/mssql/log/errorlog for a line containing SQLServerAgent.

Step 3: Database Mail (optional, but I always do it)

A job that fails silently is worse than no job. Database Mail works on Linux since CU2, so failure notifications go out the same way as on Windows. Enable it and create a profile:

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQLAlerts',
    @email_address = 'sqlalerts@example.com',
    @display_name = 'SQL Server on Linux',
    @mailserver_name = 'smtp.example.com',
    @port = 587,
    @enable_ssl = 1,
    @username = 'sqlalerts@example.com',
    @password = 'SmtpPassword';

EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'default';
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'default', @account_name = 'SQLAlerts', @sequence_number = 1;
EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'default', @principal_id = 0, @is_default = 1;

Then tell the Agent which profile to use. On Linux this is done with mssql-conf rather than through the SSMS Agent properties dialog:

sudo /opt/mssql/bin/mssql-conf set sqlagent.databasemailprofile default
sudo systemctl restart mssql-server

Step 4: Create a job

A minimal nightly full backup job, scheduled at 02:00, created entirely with T-SQL so it works from sqlcmd:

USE msdb;
EXEC dbo.sp_add_job @job_name = N'Nightly Full Backup', @enabled = 1;

EXEC dbo.sp_add_jobstep
    @job_name = N'Nightly Full Backup',
    @step_name = N'Backup all user databases',
    @subsystem = N'TSQL',
    @database_name = N'master',
    @command = N'
DECLARE @name sysname, @sql nvarchar(max);
DECLARE c CURSOR FOR SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;
OPEN c; FETCH NEXT FROM c INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N''BACKUP DATABASE '' + QUOTENAME(@name) + N'' TO DISK = N''''/var/opt/mssql/backup/'' + @name + N''_'' + CONVERT(nvarchar(8), GETDATE(), 112) + N''.bak'''' WITH COMPRESSION, CHECKSUM, INIT;'';
    EXEC (@sql);
    FETCH NEXT FROM c INTO @name;
END
CLOSE c; DEALLOCATE c;';

EXEC dbo.sp_add_schedule
    @schedule_name = N'Daily 02:00',
    @freq_type = 4, @freq_interval = 1,
    @active_start_time = 020000;

EXEC dbo.sp_attach_schedule @job_name = N'Nightly Full Backup', @schedule_name = N'Daily 02:00';
EXEC dbo.sp_add_jobserver @job_name = N'Nightly Full Backup', @server_name = N'(local)';

Make sure the backup directory exists and is owned by the mssql user:

sudo mkdir -p /var/opt/mssql/backup
sudo chown mssql:mssql /var/opt/mssql/backup

Run it once

Don't wait until 02:00 to find out. Start the job now and check the history:

EXEC msdb.dbo.sp_start_job N'Nightly Full Backup';
WAITFOR DELAY '00:00:10';
SELECT j.name, h.run_date, h.run_time, h.run_status, h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name = N'Nightly Full Backup' AND h.step_id = 0
ORDER BY h.instance_id DESC;

run_status 1 means success. You should also see the .bak files with ls -l /var/opt/mssql/backup. In SSMS, the Agent node now shows a green arrow and the job appears under Jobs.

If it doesn't work

• Msg 15281: SQL Server blocked access to procedure 'dbo.sp_add_job' of component 'Agent XPs' because this component is turned off. The package is installed but the engine was not restarted, or it is not installed at all. Run dpkg -l mssql-server-agent (or rpm -q mssql-server-agent) and restart mssql-server.
• Job step fails with Cannot open backup device '/var/opt/mssql/backup/...'. Operating system error 13 (Permission denied). The directory is not writable by the mssql user; fix ownership as shown in Step 4.
• E: Unable to locate package mssql-server-agent. The Microsoft repository is not registered or apt-get update was not run. Re-add the mssql-server-2017.list repository for your distribution.
• Job notifications never arrive. Check msdb.dbo.sysmail_event_log for SMTP errors, and confirm sqlagent.databasemailprofile in /var/opt/mssql/mssql.conf matches the profile name exactly (it is case-sensitive).

Agent settings live in mssql.conf instead of the properties dialog, and paths in job steps use forward slashes. Otherwise it feels like Windows.