SQL Server Agent is a powerful tool for running automated tasks in SQL Server. However, it is not included in the SQL Server Express edition. On Windows, many users rely on Task Scheduler to achieve similar functionality in SQL Server Express. In this article, we will explore how to use crontab to accomplish the same idea in SQL Server on Linux.

Please note that this article is specific to Ubuntu and was written using a lxd container running 16.04.6 LTS (on a Ubuntu 18.04.2 LTS host) with SQL Server 2017 Express for Linux and mssql-tools installed. While other Linux distributions can achieve similar results, the syntax for commands may differ.

Creating a Service Account

First, we need to create an account on Ubuntu and a corresponding SQL login. We’ll start by creating the agent user on the Linux side:

sudo useradd sqlagent -r
cd /var/opt
sudo mkdir sqlagent
sudo chown sqlagent: sqlagent/
sudo chmod 770 sqlagent/
sudo usermod -d /var/opt/sqlagent sqlagent
sudo chmod g+s sqlagent/

This creates a system account and a directory in the default installation location for SQL Server on Linux (/var/opt/mssql). We also add ourselves to the new group to control the files for the new account.

Next, we’ll create a SQL login with appropriate permissions:

USE master;
GO
CREATE LOGIN sqlagent WITH PASSWORD = 'AA9DA9E7-9271-47D4-918B-5827CF5D87AA', DEFAULT_LANGUAGE = BRITISH, CHECK_EXPIRATION = OFF;
GO
USE lxd;
GO
CREATE USER sqlagent FROM LOGIN sqlagent;
ALTER ROLE db_owner ADD MEMBER sqlagent;
GO

Make sure to change the password and set permissions relevant to your instance. We’ll store these login details in a directory named “.secret” for security purposes.

Create a Script to Run the Tasks

Now that we have an account and SQL login, we need to create a script that can be used in crontab to run our SQL commands. Create a file named “agent.sh” in the sqlagent’s home directory:

touch agent.sh
sudo chmod 770 agent.sh
nano agent.sh

In the “agent.sh” file, insert the following code:

#!/bin/bash
# Get the credentials from the file
source .secret/.sqlagent
# Read the parameter (name of the .sql file to run)
#$1 = name of sql file to run
file="sql/$1"
# Get start date and determine log file to write to
starttime=$(date)
log="log/$(date +%Y%m%d).log"
echo "/*** Starting Agent task $1 at $starttime ***/" >> $log
# Check if the file exists
if [ -f "$file" ]; then
    # Use sqlcmd to run the file and store the output in a variable
    output=$(/opt/mssql-tools/bin/sqlcmd -U $Login -P $Password -i "$file")
    endtime=$(date)
    if [ -z $output ]; then
        # Task completed with no output
        echo "/*** Completed Agent task $1 at $endtime ***/" >> $log
    else
        # Task completed with output
        echo "/*** Completed Agent task $1 at $endtime with output ***/" >> $log
        echo "$output" >> $log
    fi
else
    # File was not found, so add an error to the log
    echo "/*** Failed Agent task $1, file was not found ***/" >> $log
fi

You can now test if this works by creating the necessary directories and a test SQL file to run:

mkdir log
mkdir sql
echo "PRINT @@VERSION;" > ./sql/version.sql
sudo su sqlagent # Switch to the sqlagent account
cd ~ # Make sure we are in the home directory
./agent.sh version.sql
cat ./log/20190524.log # Note: Use the correct date for when you are doing this

Setting up Crontab

Now that we have the accounts, login, and script, we can finally set up crontab. We’ll start by creating a crontab file:

crontab -e

Select your preferred CLI text editor and add the following line at the bottom of the file:

# SQL Agent Tasks go below here:

This will create a crontab file. Copy this file to the sqlagent’s home directory:

cp /var/spool/cron/crontabs/sqlagent crontab

Edit the crontab file and add the following line at the very bottom:

# Update crontab jobs - Do not remove
* * * * * crontab ~/crontab 2> ~/lastinstall.cron

This will copy the settings in the crontab’s home directory every minute. If the copy fails, the error will be stored in the “lastinstall.cron” file for review. Push the update to crontab:

crontab ~/crontab
exit # Return to the normal user

Now you can start adding tasks to crontab. For example, to create a daily backup for a database, create a .sql file in the sql directory and put the relevant SQL code in the file:

BACKUP DATABASE lxd
TO DISK = '/var/opt/mssql/backups/lxd.bak';

Edit the crontab file and add the following line after the appropriate comment:

0 1 * * * ~/agent.sh backup_lxd.sql

This will run the SQL code in the “backup_lxd.sql” file every day at 01:00. Check the contents of the relevant log file to see the results of the backup task.

Limitations and Additional Notes

There are a few limitations and additional considerations to keep in mind when using crontab for automated tasks in SQL Server on Linux:

• Crontab runs once a minute, so the most frequent task you can schedule is every minute using “* * * * *”.
• Tasks scheduled with crontab may not run immediately after saving the crontab file. There may be a delay of up to a minute.
• Crontab does not have built-in support for running tasks on specific dates, such as the 2nd Monday of the month. You may need to create multiple instances of the same task or use a task that checks the date and conditionally runs the desired task.
• If the SQL instance needs access to files, you need to grant the appropriate permissions to the mssql user or group.
• Remember to check that crontab has successfully installed the file after making any edits.
• Be mindful of case sensitivity when providing file names in the script.

Using crontab for automated tasks in SQL Server on Linux provides a flexible and reliable solution for scheduling and running tasks. While it may have some limitations compared to SQL Server Agent, it offers a viable alternative for SQL Server Express editions on Linux.

For more information on crontab, refer to the crontab(5) and cron(8) manual pages.

Thank you for reading!