·
2 min read

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

Avatar photo
SQL Server Team

Related Posts