Managing SQL Agent Jobs

There are some things I really like about SQL Agent Jobs, which is why I’m secretly quite fond of this month’s T-SQL Tuesday topic, from Andy Levy.

Of course, there are some things that I don’t like about SQL Agent. For example, I’ve never been a big fan of the Operator stuff. I’m sure there are organisations that leverage every feature of it, including pagers, but that has never been me or my clients. Reports that show the status of the jobs are generally more useful than simply relying on Job Notifications (although this also varies from client to client), but on the whole, it’s an area I’m okay, but not writing home about.

The thing I’m going to write about today though – one of my favourite aspects of SQL Agent Jobs – is that it’s all so scriptable.

I know people like graphical representations of things. Give me a graphical representation of a query plan rather than a text-based one, sure (although there are still times when I’d rather have it in text), and Azure Data Factory only really seems to make proper sense when it’s shown graphically. But text seems to suit SQL Agent Jobs.

And after all, SQL Agent Jobs are for running scripts. While I’ve seen some jobs over the years that could really use a graphical representation to make it clear how the steps interact (and yes, probably created some like that too when necessary), it’s still really useful to be able to script the jobs out.

The text-based concept is why BIML was so popular for dealing with SSIS packages, and why TMDL is getting serious traction in the Power BI world. People like graphics, but if you want to really stretch the power and scale of a tool, graphics only take you so far. (I’m sure you can all give examples of where this is not the case, but I’d counter that useful graphics are almost always stored as text – consider an SVG image – and consider that proper power comes with code that can be written across the metadata.)

With SQL Agent Jobs, each task is a script. Even if those scripts are “Run this program”, they’re still scripts. And jobs, together with each task within it, can be scripted and checked into source control. A quick bit of PowerShell can show something like this, where I’m assuming $server is a variable containing an SMOServer object. (Your mileage may vary if you have characters in the job names that aren’t allowed in files)

…or you can use dbatools to do it. That’s an excellent repository for all kinds of DBA tasks.

And once you have the text for your SQL Agent Jobs, you can check it into source control and hopefully start managing your jobs with nice CI/CD patterns and the like. Personally, as much as I like CI/CD, I still like to script out jobs to make sure things are how they were left. You know, in case some well-meaning person has tweaked something that will get overwritten by the next deployment.

So perhaps weirdly, I’m going to sing the praises of the text-based nature of SQL Agent Jobs, and the fact that this aspect of them enhances my ability to manage them and ultimately look after my customers’ environments better.

@robfarley.com@bluesky (previously @rob_farley@twitter)