SQL Server Agent is an incredibly useful tool, however, isn't something included with SQL Server Express edition. On Windows many use Task Scheduler to provide a similar service to run automated tasks on SQL Server Express. In a similar method, this article will explain how to use crontab to achieve the same idea on SQL Server on Linux.
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 both SQL Server 2017 Express for Linux and mssql-tools installed. Other Linux distributions will be able to achieve similar results however the syntax for commands may differ. I have also included the user account and directory that I am using when running commands in bash, so please take note of these when following the instructions; both the directory and login do change.
Creating a Service Account
We need to create an account on Ubuntu and then a SQL login. The sections below detail this process.
The Ubuntu Account
Out of the box, SQL Server on Linux does not support connections using an AD connection and many of us using SQL Server on Linux will be using SQL Authentication instead. This means, unlike Task Scheduler, we can't run the scheduled job as an AD account and rely on Kerberos for authentication. Therefore, first we need to create a user to allow us to have a user for crontab to run under, and a login on SQL Server to connect as. Firstly, we'll create the agent user on the Linux side:
thoma@lxdbox:/$ sudo useradd sqlagent -r thoma@lxdbox:/$ cd /var/opt thoma@lxdbox:/var/opt$ sudo mkdir sqlagent thoma@lxdbox:/var/opt$ sudo chown sqlagent: sqlagent/ thoma@lxdbox:/var/opt$ sudo chmod 770 sqlagent/ thoma@lxdbox:/var/opt$ sudo usermod -d /var/opt/sqlagent sqlagent thoma@lxdbox:/var/opt$ sudo chmod g+s sqlagent/
This creates a system account, and then creates a directory in the default installation location for SQL Server on Linux (/var/opt/mssql), and then changes the new account's home directory to that directory. The last command means that files created in the folder will inherit the group permissions, so that if anyone creates files in the folder, the account can still access and write to them (as can other members of the group).
We also want to add ourselves to be able to control the files for the new account so we can add ourselves to the new group (and then force the permissions) by using usermod and newgrp:
thoma@lxdbox:/var/opt$ sudo usermod -aG sqlagent thoma thoma@lxdbox:/var/opt$ newgrp sqlagent thoma@lxdbox:/var/opt$ groups sqlagent sudo thoma mssql thoma@lxdbox:/var/opt$ cd sqlagent/
The SQL Login
For the SQL Login, we'll need to create a login which has appropriate permissions. For the purposes of this test, I'm simply going to provide the Login with db_owner permissions on a test database:
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
Obviously change the password (and language) to something appropriate for your instance and ensure you set permissions relevant to your instance. We also need to store these details somewhere as we can't use Kerberos authentication. We're going to create a directory named ".secret" to store this (only those who are members of the sudo and sqlagent groups will be able to access this) to store the username and password. Although not ideal, this is a somewhat common method of login storage on Ubuntu (for those of you that have Azure Data Studio installed, check your ~/.sqlsecrets/sqlsecrets.json file).
To create the credentials file for the Agent account use the following commands:
thoma@lxdbox:/var/opt/sqlagent$ mkdir .secret thoma@lxdbox:/var/opt/sqlagent$ echo 'Login="sqlagent"' >> .secret/.sqlagent thoma@lxdbox:/var/opt/sqlagent$ echo 'Password="AA9DA9E7-9271-47D4-918B-5827CF5D87AA"' >> .secret/.sqlagent
Create a script to run the tasks
Now we have an account and SQL login we need create a script we can use in crontab that'll run our sql commands. Firstly create the file and then open it in your preferred CLI text editor (personally, I use nano):
thoma@lxdbox:/var/opt/sqlagent$ touch agent.sh thoma@lxdbox:/var/opt/sqlagent$ sudo chmod 770 agent.sh thoma@lxdbox:/var/opt/sqlagent$ nano agent.sh
Then, insert the following into the text file:
#!/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 the file exists
if [ -f "$file" ]; then
#Use sqlcmd to run the file, and store the output to 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 of 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
fiWe can now test if this works by creating the needed directories and creating a test sql file to run:
thoma@lxdbox:/var/opt/sqlagent$ mkdir log thoma@lxdbox:/var/opt/sqlagent$ mkdir sql thoma@lxdbox:/var/opt/sqlagent$ echo "PRINT @@VERSION;" > ./sql/version.sql thoma@lxdbox:/var/opt/sqlagent$ sudo su sqlagent #As we need to run as the sqlagent account sqlagent@lxdbox:~$ cd ~ #though we should already be there sqlagent@lxdbox:~$ ./agent.sh version.sql sqlagent@lxdbox:~$ cat ./log/20190524.log #Note to use the correct date for when you are doing this
For my instance, the result is the following output:
/*** Starting Agent task version.sql at Fri May 24 19:22:36 UTC 2019 ***//*** Completed Agent task version.sql at Fri May 24 19:22:36 UTC 2019 with the following output (if any). ***/Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) May 15 2019 19:14:30 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)
Setting up crontab
Now that we have the accounts and login, and a script to run, we now need to finally set up crontab. I won't lie, I didn't find an "elegant" way to do this, so someone else may know a better way; if so please do share it in the discussion and I'll be happy to test and update this article.
Any, first we'll get a crontab file created. As we'll currently impersonating sqlagent, run the below command:
sqlagent@lxdbox:~$ crontab -e
Select your preferred CLI text editor and then at the bottom of the file add the below and save the file:
# SQL Agent Tasks go below here:
This will create a crontab file, which we're going to copy to sqlagent's home directory:
sqlagent@lxdbox:~$ cp /var/spool/cron/crontabs/sqlagent crontab
Then edit your file and add the following at the very bottom of the file (after the last comment you made):
# Update crontab jobs - Do not remove * * * * * crontab ~/crontab 2> ~/lastinstall.cron
This (once pushed to the actual crontab) will mean that the settings in crontab's home directory will be copied every minute. If, for whatever reason, the copy fails (for example an invalid argument for a time) then this will be stored in the lastinstall.cron file for review. Now we just need to actually push through the update once:
sqlagent@lxdbox:~$ crontab ~/crontab sqlagent@lxdbox:~$ exit #returns us to our normal user
Now that that's all done we can begin adding some tasks. We could start by creating a backup for a database once a day. Create .sqlfile in the sql directory in sqlagent's home directory and put the relevant SQL in the file:
BACKUP DATABASE lxd TO DISK = '/var/opt/mssql/backups/lxd.bak';
And then edit the crontab file and add the below after the appropriate comment:
0 1 * * * ~/agent.sh backup_lxd.sql
This will run the above sql (saved as backup_lxd.sql) every day at 01:00. If we then check the contents of the relevant log file we'll see the results of the backup task:
/*** Starting Agent task backup_lxd.sql at Fri May 25 01:00:01 UTC 2019 ***//*** Completed Agent task backup_lxd.sql at Fri May 25 01:00:01 UTC 2019 with the following output (if any). ***/Processed 304 pages for database 'lxd', file 'lxd' on file 3. Processed 2 pages for database 'lxd', file 'lxd_log' on file 3. BACKUP DATABASE successfully processed 306 pages in 0.036 seconds (66.406 MB/sec).
Additional Notes
If you are using the @reboot special time specification, I suggest adding a delay. @reboot will run when the host starts up, not SQL Server. Therefore you may want to allow a few minutes for the instance to be running as well:
@reboot sleep 180 && ~/agent.sh version.sql #Sleeps for 180 seconds before running the task
If you want to have multiple files be run one after the other, then (as seen above) you can use && to cause tasks to be run one after the other. For example:
0 * * * * * ~/agent.sh UpdateClients.sql && ~/agent.sh UpdateOrders.sql
This would run the UpdateClients.sql file first, and then the UpdateOrders.sql file.
Limitations
Unlike SQL Server Agent, which can run a couple of times a minute, crontab only runs once a minute, so that's the most frequent task you are able to do (using * * * * *). Also, because you're using a task to update crontab, it's likely that the earliest a task will be run is the minute after the minute you update the crontab file. So, if you edit the crontab file to do something at 10:00, but save the file at 09:59:30 then it's likely the task won't be run until 10:00 the following day.
crontab doesn't have a way of running tasks on something like the 2nd Monday of the Month (that I can think of). If you need to do something like this you'll like need to create a task that runs every Monday and checks to see if the day of the month is between the 8th and 14th, or create multiple instances of the same task. For example:
#Run at 09:00 on the 2nd Monday of the Month #Run on the 8-14 when day is a Monday 0 9 8 * 1 ~/agent.sh MonthlyFigures.sql 0 9 9 * 1 ~/agent.sh MonthlyFigures.sql 0 9 10 * 1 ~/agent.sh MonthlyFigures.sql 0 9 11 * 1 ~/agent.sh MonthlyFigures.sql 0 9 12 * 1 ~/agent.sh MonthlyFigures.sql 0 9 13 * 1 ~/agent.sh MonthlyFigures.sql 0 9 14 * 1 ~/agent.sh MonthlyFigures.sql
If you need the agent to access other files, you'll either need to add the sqlagent as a member of the group (sudo usermod -aG {group name} sqlagent) or add permissions for the sqlagent group to be able the read/write/execute the file. If the SQL Instance needs access to something, you need to grant the mssql user or group access (tasks that interact with the file system from the SQL Server are not run under the credentials of sqlagent).
You also should remember to check that crontab has successfully installed the file after you make any edits, as if you don't none of your automated tasks will run.
Bash is case sensitive as well, so if you provide a filename with a character in the wrong case then the task won't work. If you do make a spelling mistake for your .sq; file you'll see this in your log file. If you mistype ~/agent.sh you won't, however you will be able to see it in the syslog file (/var/log/syslog).
Additional material
Crontab documentation:
Final Full crontab file after article:
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $) # Edit this file to introduce tasks to be run by cron. # # Each task to run has to be defined through a single line # indicating with different fields when the task will be run # and what command to run for the task # # To define the time you can provide concrete values for # minute (m), hour (h), day of month (dom), month (mon), # and day of week (dow) or use '*' in these fields (for 'any').# # Notice that tasks will be started based on the cron's system # daemon's notion of time and timezones. # # Output of the crontab jobs (including errors) is sent through # email to the user the crontab file belongs to (unless redirected). # # For example, you can run a backup of all your user accounts # at 5 a.m every week with: # 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/ # # For more information see the manual pages of crontab(5) and cron(8) # # m h dom mon dow command # SQL Agent Tasks go below here: # m h dom mon dow command # SQL Agent Tasks go below here: @reboot sleep 180 && ~/agent.sh version.sql 0 1 * * * ~/agent.sh backup_lxd.sql # Update Crontab jobs - Do not remove * * * * * crontab ~/crontab 2> ~/lastinstall.cron
