How to Configure Agent Jobs for SQL Server on Linux

SQL Server on Linux supports a scheduling mechanism for scripts or tasks using SQL Server agent jobs. For example, suppose you want to run full backups daily at 22:00 or a transaction log backup every 10 minutes. You can use SQL Server agent jobs to schedule these tasks for you.

Use cases of SQL Server agent jobs include:

• Database maintenance tasks such as Index rebuild, statistics update, and consistency check

• Configure backup and restore tasks

• Application related jobs

• Database health Alerts. We can configure database health alerts for monitoring and trigger email notifications to DBAs for proactive actions.

So, how do we configure jobs in SQL Server on Linux? You can utilize SQL Server Management Studio, Azure Data Studio, or T-SQL scripts for configuring these jobs.

You can refer to the article Use SSMS or Azure Data Studio for SQL Server on Linux for more details about using Azure Data Studio and SQL Server Management Studio.

We will be using SQL Server Management Studio to create and schedule a database backup job for this blog.

Configure SQL Server on Linux agent job in SQL Server Management Studio

To begin, launch the SSMS and specify the Linux SQL IP addresshost name and credentials (user name and password).

You get SSMS connections for SQL Server on Linux. Currently, it shows the status as SQL Server Agent (Agent XPs disabled).

You will get the Agent XPs disabled error in SQL Server on Linux if you have not enabled SQL Server agent for Linux.

To enable it, launch the terminal and run the following commands:

$ sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

$ sudo systemctl restart mssql-server

Once SQL Service is restarted, refresh the SSMS connection to expand the SQL Server agent folder. It has the following subfolders:

• Jobs

• Alerts

• Operators

• Error logs

Right-click on the Jobs folder and Create a new job.

The new job configuration has the following steps.

General

The “General” page requires the following inputs:

• Job Name: Enter a suitable agent job name.

• Job Owner: Select a job owner (login) account. The account should have permission to execute a job.

• Category: You can select a suitable job category from the drop-down list. It is an optional field and does not impact job execution.

• Description: It is advisable to specify a description of the job for documentation purposes.

Steps

We specify the job task using the T-SQL script in the job step. For example, suppose I want to take a full database backup for [TutorialDB], we can use the following Script.

Add a new job step, enter the job step name, and specify the following T-SQL script in the command window.

BACKUP DATABASE TutorialDB TO Disk='TurialDB.bak' WITH Stats=10, COMPRESSION

The step type should be Transact-SQL script (T-SQL) for executing a SQL script in the job step.

Next, click on “Advanced.” The advanced section enables you to configure job step success and failure action. If there are multiple job steps in a single job, you can execute the required job step based on the outcome of the job step. For example, if job step 1 fails, you will terminate the job stating “failed.” Similarly, if it is successful, the job should execute further steps.

Sometimes, the job step might fail due to intermittent connection issues. In that case, we want the job to retry and report failure if it is not successful in retries.

You can configure job step retry using the following configuration:

• Retry attempts: How many retry attempts do we want to configure.

• Retry interval (minutes): How many minutes after the job step failure should it retry.

• Output file: You can specify a job text file to record job step output. It is helpful to capture the execution log of the job step in a text file.

Click Ok, and it configures a job step named DB Backup. Click on New and follow the outlined steps to add more job steps.

Schedule

You can configure a job schedule to execute jobs at a frequency of your choice. The job schedule can occur once or recur daily, monthly, or weekly.

The job requires a start date (mandatory) and an end date (optional). For example, we can configure a backup job to run every day at 12:00:00. The schedule does not have an end date, so it will continue to run daily until we disable the job.

Actions

In the action tab, you can configure email or page notifications in case of job success, failure, or completion. Similarly, you want to delete the job automatically after a specific job state.

You can click “Automatically delete the job and choose the required option” when the job succeeds or fails.

Once you have provided the required job information, you can click ok to configure the agent job or click on Script (as highlighted) to generate an equivalent T-SQL script. 

In our case, we get the following T-SQL script for Database Backup Job.

Note: SQL Server stores agent job information, configurations in the system database — MSDB. 

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC  msdb.dbo.sp_add_job @job_name=N'Database Backup Job',

             @enabled=1,

             @notify_level_eventlog=0,

             @notify_level_email=2,

             @notify_level_page=2,

             @delete_level=1,

             @category_name=N'[Uncategorized (Local)]',

             @owner_login_name=N'sa', @job_id = @jobId OUTPUT

select @jobId

GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Database Backup Job', @server_name = N'LINUXSQL'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_jobstep @job_name=N'Database Backup Job', @step_name=N'DB Backup',

             @step_id=1,

             @cmdexec_success_code=0,

             @on_success_action=1,

             @on_fail_action=2,

             @retry_attempts=0,

             @retry_interval=0,

             @os_run_priority=0, @subsystem=N'TSQL',

             @command=N'BACKUP DATABASE TutorialDB to DISK=''TutorialDB.bak'' WITH STATS=10, COMPRESSION',

             @database_name=N'master',

             @flags=0

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @job_name=N'Database Backup Job',

             @enabled=1,

             @start_step_id=1,

             @notify_level_eventlog=0,

             @notify_level_email=2,

             @notify_level_page=2,

             @delete_level=1,

             @description=N'',

             @category_name=N'[Uncategorized (Local)]',

             @owner_login_name=N'sa',

             @notify_email_operator_name=N'',

             @notify_page_operator_name=N''

GO

USE [msdb]

GO

DECLARE @schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Database Backup Job', @name=N'Daily',

             @enabled=1,

             @freq_type=4,

             @freq_interval=1,

             @freq_subday_type=1,

             @freq_subday_interval=0,

             @freq_relative_interval=0,

             @freq_recurrence_factor=1,

             @active_start_date=20220109,

             @active_end_date=99991231,

             @active_start_time=0,

             @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

select @schedule_id

GO

Execute the Script, and you see a new SQL Server agent job, “Database Backup Job.”

SQL Server automatically executes the job as per its schedule. For testing purposes, let’s run it manually. Right-click on the agent job and choose the option, “start job at step”.

The job will start to execute and show a status of failure or success.

To get more details on the job execution, select “View History” after right-clicking on the specific job.

It logs the agent job details, duration, and status for all job executions. You can then monitor this log to investigate in case of job failure.

Wrapping Up

SQL Server agent jobs for SQL Server on Linux are the best way to automate job executions on a specific schedule without manual intervention, particularly for database maintenance tasks, configuring backup and restore tasks, application-related jobs, and database health alerts.

If you’re looking for additional methods to manage your SQL Server database, check out our post on using SSMS or Azure Data Studio for an overview of the features of two of the most popular client tools for SQL Server.

ClusterControl now supports SQL Server 2019, so be sure to follow us on Twitter, LinkedIn, and subscribe to our newsletter for more updates and best practices in the near future.