How to set up a SQL Server Job via SSDT
SQL server agent is used for many things. From maintenance tasks to health monitoring to do ETL/ ELT. Creating solutions with SQL server often involves creating jobs. As DevOps, we want to have all our configuration in the form of code. Using SQL Server Data Tools this can be done for tables, view, procedure, and so on. But, how to do this with Server agent jobs?
As we create SQL Server Solutions as code, to make them re-deployable and versionable, we might get to the point where we want to declare server agent jobs. This is not implemented by default in SSDT, so how to define a job in T-SQL code?
Defining a job using T-SQL
The first thing we need to solve is to create a job in SQL. After Job creation, we need to create a Schedule and add the schedule to the job.
It does not make sense to run a job without steps, hence we need to create one or more steps for the job.
The creation of the job can be done using T-SQL and the procedure msdb.dbo.sp_add_job. The minimum requirement here is to give it a name. This name should be unique since we’ll use it, later on, to update the job and add steps and schedules. A good idea is to describe what the job will do and maybe add it to a category of jobs.
The job is the container, that links the tasks that need to be done, to a schedule, a user context, and an SQL server.
Next, we need to add some job steps using T-SQL and the procedure msdb.dbo.sp_add_jobstep. Here we can use the job name to identify the job. A step_name, a step_id, and something to execute are needed as parameters for this procedure. A SQL server agent job can contain one or more steps.
To create the job schedule [Create Schedule] using T-SQL msdb.dbo.sp_add_jobschedule. The job schedule defines, when and how often the job runs. If your job is only started manually, the schedule would be one.
Finally, we need a jobserver, where we need one server per job. The job server finally ties the job to a server. Adding the jobserver, we can add the job to the server we deploy the job to, or to a different server. Using T-SQL the job server is created via msdb.dbo.sp_add_jobserver.
Now that does work for the first time we run the script. But we want to keep around the script to create the job and not delete it after we created the job in the PostDeploymetScript. Let’s first try to circumvent the creation of the job, the jobsteps, the job schedule, and the job server.
Though this is easily done, it introduces a new problem. How to apply changes?
Well, it might be tempting to just drop and create the jobs, servers, and stuff every time we deploy. That might work for jobs like the following:
Why Drop and Create is a bad idea
Now that we dropped the whole job and recreated it, we lost all the history and probably created some orphan data. The dropped history is a bad idea especially in a productive environment, where you want to track errors that occur.
Update or edit a job, when deploying a new version
When the Job is created it is easy to check if the job already exists.
If we know that the job exists, we can either create it like above or update it with new information. In the example for the job, but it is possible for the jobsteps and the schedules as well. Now we can redeploy the solution with ease.
But we’ve introduced a new flaw, now we need to define our job name, jobstep parameters, and job schedule parameters in multiple locations. That’ll be a mess over time.
Using a temporary stored procedure to deploy the jobs
Let’s put all the code that we need to define our agent jobs into stored procedures and pass the configuration as tables into these procedures. Then we can easily define the jobs, steps, and schedules as tables in the post-deployment scripts.
For creation and updating jobs it works like the following. A similar procedure can be created for steps, schedules, and the jobservers.
Please find the Procedures in the following GIST (pretty lengthy code, please scroll down for more info):
For more than one SQL Server Database project the creation and dropping of the temporary procedures should be moved to their on-setup and tear-down files.
A bit of a flaw is that the user that deploys the database definition to the server needs to have CREATE, INSERT, SELECT, and ALTER permissions on several tables in the system database msdb.
Conclusions
We have seen it is possible to create and update agent jobs using T-SQL code. It also enables us to create the server agent jobs that belong to a certain database project within the code of the database. This helps us to create whole SQL server database projects in a DevOps fashion.
We have also seen that the setup process of the jobs can be split from the definition. Therefore, it is possible to define the jobs, steps, schedules, etc. in the PostDeployment script and include generic setup and tear-down scripts.
How do you setup your server agent jobs? Do you do DevOps with your SQL Servers?
If you have question put them down below in the comments, or send me a message via Twitter or LinkedIn.
If the code was helpful consider:
