SQL Agent depLoyment Tasks Out Now!

Hello!

In my time since leaving university, I’ve worked across the spectrum, from tester to DBA, it has always been abundantly clear that SQL Agent Jobs are one of those things that are really difficult to get into source control and deploy. Sure, you can script them out, but that doesn’t really factor in changes. And in many places, the phrase “whatever is in prod” has often been the answer to the question “what are the SQL Agent Jobs supposed to look like?” And frequently, relying on msdb being backed up as been the backup process for SQL Agent Jobs. If we spoke about databases in the same way, and I’m sure you have worked at places that do, we know we’d have a real big problem. Jobs create less of a problem, but still it’s a sticking point.

Having worked on a PowerShell Module that seeks to automate the deployment for a SSIS project and it’s corresponding folder and environments, it makes sense to have a process that also creates SQL Agent Jobs in much the same way, especially as jobs are the primary method for scheduling/automating dtsx package execution.

With that in mind, earlier this week I published a PowerShell Module on GitHub that I’ve been using for some time. It’s called SQL Agent depLoy Tasks, or salt for short. salt is an attempt to solve the problem of storing SQL Agent Jobs in source control and deploying them DSC-style. Rather than delve too much into it here, I’m keen to write as much as I can in to the readme file on the repo and point people out to that. That way it’s all kept together in one place. We also have a sample project on GItHub that uses both AssistDeploy and salt. As with the salt readme, there is plenty more info in the readme’s for both AssistDeploy and the sample repo.

However, because you’re sat here reading this already, here’s a brief introduction.

With salt, a SQL Agent Job can be extracted from a SQL Server into an XML structure. This file is checked into source control, with either a database or SSIS project, and packaged up for deployment. To extract a SQL Agent Job, consult the readme.

The XML below is a SQL Agent Job that was extracted using Get-SQLAgentAsXml in salt.

That’s a lot of XML. So let’s break this down and talk about the different sections.

First up is the settings you’d tend to find on the General, Notifications and Targets tabs of a SQL Agent Job.

Next up is the schedule. The frequency values determine that it will only run once, daily, at midnight. The start date is 24th October 2017, and it is set to run to the end of time, or the 31st December 9999, whichever comes first…..

Currently the job is disabled.

It’s worth noting that many different types of schedules are supported. FOr examples, if we wanted a job to run Monday, Wednesday and Friday but only every other week, we can set that up also.

Setting up schedules is a very broad task, and I’ve put a lot of effort in trying to make the XML version as legible as possible, and support as many of the different schedules available. I’m not saying it’s perfect though, so any schedules that don’t import/deploy properly can be added and supported no worries!

Finally, steps. The sample xml file only contains one step, but just like with schedules, you can have as many as is permitted. The actions for steps can be altered to “go to step XX” command, if more steps exist. Notioce that package and configuration tabs are correctly set up.

The other two step types that are supported are SQL ad PowerShell. Others can be added (this is an open source project after all…)

To deploy the xml, consult the readme. For information on parameterising, consult the (yep, you guessed it) readme. Any elements that need changing will have an attribute called "Include."

Any questions, let me know!