One of my customers asked me how to source control and deploy their SQL Server Agent Jobs. I spent some time figuring it out this morning. This is what I learned.

Part 1 – Scripting out a job

First, we need to script out the job. To test this I created a test job on my machine using SQL Server Management Studio (SSMS):

Then I tried scripting it out from SSMS as a DROP and CREATE:

The resulting script could be used to drop and create the job. However there are two issues:

1. The job history is deleted – which could be annoying for troubleshooting:
   
2. The script may only works once, because on some versions of SQL the DROP statement uses the @job_id parameter. Each time the job is recreated the job ID changes. Hence, the script only works the first time you run it:

These issues can be fixed/mitigated by updating the default script generated by SSMS as follows:

1. Using the @delete_history=0 parameter in drop statement. Unfortunately, the history will still not be visible in the SSMS GUI (I can’t see a way to fix this). However, the history will be persisted in the sysjobhistory table in the msdb database. This means that your team will need to get used to looking up the job history data from here instead of the wizard in SSMS. You probably also want to make sure you have appropriate backup processes for msdb:
   
2. Changing the auto generated script to use the @job_name parameter instead of the @job_id parameter. This will make the script re-runnable because job_name will be consistent, where job_id changes each time you run the script.

I’ve shared a full example of a .sql script that you can look at in more detail here:

Feel free to run this against a test/dev server multiple times. You should observe the same behaviour as I have.

Part 2 – Scripting out *ALL THE JOBS*

Going through SSMS and scripting out all the jobs 1 by 1 is likely to be a pain in the ass. Boring, repetitive, slow, error prone. No-one wants that job. So I tried to figure out a nice way to automate it. In my research I came across this blog post. (Thank you Phil Factor (b|t) – you are awesome!):

https://www.red-gate.com/simple-talk/sql/t-sql-programming/database-deployment-the-bits-agent-jobs-and-other-server-objects/

It includes a PowerShell script that you can run against a list of servers and it will script out a whole bunch of server objects – not just Agent Jobs, but also Triggers, Backup Devices, Linked Servers, Logins, User-Defined Messages, Alerts, Operators, Shared Schedules, Proxy Accounts and Target Servers.

I ran this on my machine, and after a bit of head scratching I got it working. It was lightning fast:

The agent job scripts will still need tweaking to make sure that each one uses the appropriate @delete_history=0 and @job_name parameters, but regardless, this script should save a significant amount of time and risk.

I’ve not spent any time looking at the scripts that were generated for other sorts of server objects (Linked Servers, Logins etc), but it seems there is plenty of opportunity to do something similar for those too. Phil Factor talks about each object in more detail in his post. Thanks Phil!

Part 3 – Deploying *ALL THE JOBS*

This bit I have not tested, but I’m pretty confident it will work.

Basically, we need to put all the SQL Agent Job .sql scripts into a git repo. Then we need a PowerShell script that executes each .sql script against the necessary target databases. If you use SSDT, you might prefer to use a post deployment script to do this. That bit should be reasonably straight forward. I’ll leave that as a task for the user since I’m short on time.

You probably want to put some thought into whether your agent jobs are scoped to a particular database, general server admin for a specific server, or whether you want them to be standardised across many servers since this may affect where you choose to put your jobs ion source control and on what schedule you want to deploy them.

Of course, it goes without saying, that deployment from source control should be automated and the source controlled version of the job is God. Any manual fixes to jobs on your estate should be reverted to the source control version on a regular basis. If you want to update the job – you have to do it through source control. The only way to enforce that behaviour, is to enforce that behaviour. The manual production hackers will soon get the message if all their manual hacks keep dissappearing! 😛

Edit: Another Gotcha!

Following completing the PoC above for my customer, I started to script out all their agent jobs using this process. Their agent jobs it includes the UID of the job schedule. This causes various problems, that are well articulated in this blog post by Chris Sommer (b|t):

How the SQL Agent Job schedule_uid broke my heart, and my jobs!