SQL Server on Linux: Running jobs with SQL Server Agent
In keeping with our goal to enable SQL Server features across all platforms supported by SQL Server, Microsoft is excited to announce the preview of SQL Server Agent on Linux in SQL Server vNext Community Technology Preview (CTP) 1.4.
SQL Server Agent is a component that executes scheduled administrative tasks, called “jobs.” Jobs contain one or more job steps. Each step contains its own task such as backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company databases every weekday after hours, you can automate doing so by scheduling an Agent job to run a backup at 22:00 Monday through Friday.
We have released SQL Server Agent packages for Ubuntu, RedHat Enterprise Linux, and SUSE Linux Enterprise Server that you can install via apt-get, yum, and zypper. Once you install these packages, you can create T-SQL Jobs using SSMS, sqlcmd, and other GUI and command line tools.
Here is a simple example:
- Create a job
CREATE DATABASE SampleDB ;
USE msdb ;
GO
EXEC dbo.sp_add_job
@job_name = N’Daily SampleDB Backup’ ;
GO
- Add one or more job steps
EXEC sp_add_jobstep
@job_name = N’Daily SampleDB Backup’,
@step_name = N’Backup database’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE SampleDB TO DISK = \
N”/var/opt/mssql/data/SampleDB.bak” WITH NOFORMAT, NOINIT, \
NAME = ”SampleDB-full”, SKIP, NOREWIND, NOUNLOAD, STATS = 10′,
@retry_attempts = 5,
@retry_interval = 5 ;
GO
- Create a job schedule
EXEC dbo.sp_add_schedule
@schedule_name = N’Daily SampleDB’,
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 233000 ;
USE msdb ;
GO
- Attach the schedule and add the job server
EXEC sp_attach_schedule
@job_name = N’Daily SampleDB Backup’,
@schedule_name = N’Daily SampleDB’;
GO
EXEC dbo.sp_add_jobserver
@job_name = N’Daily SampleDB Backup’,
@server_name = N'(LOCAL)’;
GO
- Start job
EXEC dbo.sp_start_job N’ Daily SampleDB Backup’ ;
GO
Limitations:
The following types of SQL Agent jobs are not currently supported on Linux:
- Subsystems: CmdExec, PowerShell, Replication Distributor, Snapshot, Merge, Queue Reader, SSIS, SSAS, SSRS
- Alerts
- DB Mail
- Log Shipping
- Log Reader Agent
- Change Data Capture
Get started
If you’re ready to get started with SQL Server on Linux, here’s how to install the SQL Server Agent package via apt-get, yum, and zypper. And here’s how to create your first T-SQL job and show you to use SSMS with SQL Agent.
Learn more
- Read detailed documentation.
- Register for the next Engineering Town Hall webinar.
- If you have a workload ready to run on SQL Server v.Next, sign up for the SQL Server Early Adoption Program (EAP).
