How do you manage SQL jobs that belong to a database?

Question

We just had an issue where we lost msdb on one of our servers along with all of the jobs which we created for a multitude of databases. The fact that we didn't have a backup of msdb is one issue, which I won't get into, but it did make me think about how we develop the jobs that go along with our databases.

What are the different ways that one could manage jobs? I guess ideally they would be in source control, but I've yet to find any tools which do a good job of managing large systems of databases with their associated jobs.

Answer 1

So let us begin with the obvious: Back up your msdb database. Get a maintenance plan going, back it up regularly, and let us never lose our jobs or job history again.

This also leads to the second part of my answer which is: jobs and their steps are just rows in a table. They have steps which invoke subsystem commands using arguments provided in the job step row. The only way ultimately to "manage" the jobs is by managing the SQL that creates / modifies / deletes them. And because there's surprisingly little customization for a given job step, most people have just stuck to static SQL scripts for deployment.

Build your job in dev, test it, script it out to SQL, store it as a post-deployment file in your database project (your databases are in source control ... right?) and you've got the path of least resistance to SQL Agent job management.

Maybe someday in the future there will be ways to "compare" jobs in a project to a target server job a la Schema Compare - again, it's just SQL, shouldn't be too hard to reconcile - and then publish changes (even bidirectionally!) but for now you're just stuck with scripting.

Answer 2

There are a couple ways to approach this:

• Maintain your agent jobs in source control by scripting out the entire job creation and storing that script. The script can be stored in your version control repository or maintained with third party tools such as Red Gate Source Control.
• Keep your agent jobs themselves as minimal, only calling stored procedures. Then version control the stored procedures so that the Agent Job is maintained only as a scheduling/execution utility.
• Maintain full documentation on your jobs and schedules that is inventoried on a periodic basis. I recommend this free script that I've used in the past for creating my Agent job run book.

Ultimately, what you use agent jobs for will determine the best approach. In most of my environments, Agent jobs are used strictly for maintenance tasks. We have canned scripts and stored procedures for these tasks, so we maintain one set of scripts to deploy the jobs. In the event of total msdb failure, I simply redeploy all my jobs (along with other management objects) from my script repository. If your jobs are more unique to the server, you'll need to maintain more specific job documentation.

Answer 3

We build SQL Agent jobs in the SQL Server Management Studio UI on a developer machine. Then, when we are done, we script it out and delete the job. We add the script to source control and include it in our product's install program. Because it is in source control, we can manage how updates are created and released.

Once installed in a production system, nobody touches the job by hand. Any change to the job must be coded in the script, checked in, and tested first before delivering to the customer system.

(Experienced customers do sometimes modify schedules or setup alerts or the like.)