With the release of SQL Server vNext CTP 1.4 SQL Agent was released for use on Linux. To install it on Ubuntu you need to upgrade your SQL Server to CTP 1.4. On Ubuntu you do this with
sudo apt-get update sudo apt-get install mssql-server
Once you have CTP 1.4 you can install SQL Agent as follows
sudo apt-get update sudo apt-get install mssql-server-agent sudo systemctl restart mssql-server
for different flavours of Linux follow the steps here
Once you have done that you will see that the Agent is now available
So now I can schedule backups and maintenance for my Linux SQL databases using the agent. I immediately turned to Ola Hallengrens Maintenance Solution I downloaded the SQL file and ran it against my Linux server once I had changed the path for the backups to a directory I had created at /var/opt/mssql/backups notice that it is specified using Windows notation with C:\ at the root
SET @CreateJobs= 'Y' -- Specify whether jobs should be created. SET @BackupDirectory = N'C:\var\opt\mssql\backups' -- Specify the backup root directory. SET @CleanupTime = 350 -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted. SET @OutputFileDirectory = NULL -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used. SET @LogToTable = 'Y' -- Log commands to a table.
The stored procedures were created
and the jobs were created
Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell
First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs
Import-Module sqlserver $cred = Get-Credential $Jobs = Get-SqlAgentJob -ServerInstance LinuxvVNext -Credential $cred $jobs |ft -auto
Once the jobs were in the variable I decided to filter out only the jobs that are calling the stored procedures to perform the backups, DBCC and Index optimisation and loop through them first. Backups are the most important after all
## Find the jobs we want to change foreach($Job in $jobs.Where{$_.Name -like '*DATABASES*'})
Then it is simply a case of replacing the sqlcmd text in the command to return it to T-SQL, adding the database name (I installed Ola’s stored procedures into the master database and changing the subsystem to use T-SQL instead of CmdExec
## replace the text as required
$job.jobsteps[0].command = $job.jobsteps[0].command.Replace('sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "' , '').Replace('" -b','')
## Change the subsystem
$job.jobsteps[0].subsystem = 'TransactSQL'
## Add the databasename
$job.jobsteps[0].DatabaseName = 'master'
## Alter the jobstep
$job.jobsteps[0].Alter()
We can check that it has done this using PowerShell
$Jobs = Get-SqlAgentJob -ServerInstance LinuxvVNext -Credential $cred
foreach ($Job in $jobs.Where{$_.Name -like '*DATABASES*'}) {
foreach ($step in $Job.JobSteps) {
$step | Select Parent, Name, Command, DatabaseName, Subsystem
}
}
or by looking in SSMS if you prefer
Now lets run the jobs and check the history using Get-SqlAgentJobHistory
Get-SqlAgentJobHistory -ServerInstance linuxvnextctp14 -Credential $cred | select RunDate,StepID,Server,JobName,StepName,Message|Out-GridView
Which pretty much matches what you see in SSMS
and if you look in the directory you see the files exactly as you would expect them to be
We still need to change the other jobs that Ola’s script create. If we look at the command steps
We can see that the CommandLog Cleanup job can use the same PowerShell code as the backup jobs, the sp_delete_backuphistory and sp_purgejobhistory jobs need to refer to the msdb database instead of master. For the moment the Output File Cleanup job is the one that is not able to be run on Linux. Hopefully soon we will be able to run PowerShell job steps and that will be resolved as well
Here is the full snippet of code to change all of the jobs
$server = 'Linuxvnextctp14'
$cred = Get-Credential
$Jobs = Get-SqlAgentJob -ServerInstance $server -Credential $cred
## Find the jobs we want to change
foreach ($Job in $jobs) {
if ($Job.Name -like '*DATABASES*' -or $Job.Name -like '*CommandLog*') {
## replace the text as required
$job.jobsteps[0].command = $job.jobsteps[0].command.Replace('sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "' , '').Replace('" -b', '')
## Change the subsystem
$job.jobsteps[0].subsystem = 'TransactSQL'
## Add the databasename
$job.jobsteps[0].DatabaseName = 'master'
## Alter the jobstep
$job.jobsteps[0].Alter()
}
if ($Job.Name -like '*history*') {
## replace the text as required
$job.jobsteps[0].command = $job.jobsteps[0].command.Replace('sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "' , '').Replace('" -b', '')
## Change the subsystem
$job.jobsteps[0].subsystem = 'TransactSQL'
## Add the databasename
$job.jobsteps[0].DatabaseName = 'msdb'
## Alter the jobstep
$job.jobsteps[0].Alter()
}
}
