AlwaysOn Availability Groups and SQL Server Jobs, Part 14: Establishing Synchronization Checks

When compared to AlwaysOn Failover Cluster Instances (FCIs) or clustered deployments of SQL Server, one of the main problems with AlwaysOn Availability Groups is that a single SQL Server database (or group of databases) is made to straddle multiple hosts—meaning that any server-level dependencies found in that database need to be mirrored across all servers that will host the database. Happily, the notion of Contained Databases (while still, arguably, not nearly as mature as most people would like them to be) helps address some of these issues and concerns—like Logins and users—but, sadly, falls short with other concerns such as what to do when we need to target databases from SQL Server Agent Jobs for one reason or another.

Consequently, once you throw AlwaysOn Availability Groups into the mix, you’ll need to make sure that any jobs you’ve defined (which target your Availability Group databases) will need to by synchronized across all servers so that details about execution time, job ownership, alerting, and, of course, the actual operations to be performed, are all perfectly synchronized across all servers in the topology.

Which, in turn, poses two questions:

Options for Managing Job Synchronization

Technically speaking, there are a couple of different options for keeping SQL Server Agent Jobs synchronized across servers. One of the more immediate options that might come to mind would be to use SQL Server Integration Services (SSIS) and the Transfer Jobs Tasks to keep jobs synchronized.

Only, the SSIS Transfer Jobs Task was designed to transfer jobs—not keep them synchronized. Arguably, it even provides some challenges even when it comes to merely transferring (or moving) jobs from one instance to another—given that there are limitations as to how the Enabled/Disabled statuses of transferred jobs should be handled (options for managing these states are pretty lame, as we can only define whether All jobs transferred should be enabled/disabled on the target/destination server).

Accordingly, a better technique for actually synchronizing SQL Server Agent Jobs between servers is, strangely enough, to script them at one server and run them at other servers. Technically speaking, once you’ve synchronized all jobs, it shouldn’t be too frequent that large numbers of them will fall out of synchronization with each other. Granted, you will occasionally modify some jobs—and that’ll entail a bit of effort. But it’s nothing too terrible:

Sadly, you won’t be able to use the Script Job as > DROP and CREATE . . . because that logic drops Jobs by their Job ID (and that won’t usually be the same across all of your servers). Likewise, if you want to retain job history, then dropping and scripting over-the-top of your old jobs isn’t a good idea—and you’ll need to manually make all of the changes to ALL of your jobs.

Otherwise, I find that scripting is hard. In fact, two tricks that help with scripting quite a bit are as follows:

Options for Determining Job Non-Convergence

Otherwise, once you’ve tackled the means of synchronizing jobs when you find them out of sync with each other, then the only question is how you go about detecting such a problem. And, sadly, that means you’ll need to end up letting the servers somehow talk to each other. Or, you’ll need to set up some sort of third party process or application that can connect to all the servers hosting your Availability Group and query Jobs details to see if there are any problems.

Personally, I think that Linked Servers are the way to go in this case—as you’re already ensuring that the servers hosting you Availability Group(s) are talking to each other anyhow. As such, in my next post, we’ll look at some of the security concerns we’ll need to address to let code running on, say, the primary server reach out to other servers in your topology and query details about Jobs. Then, in subsequent posts we’ll look at the actual synchronization checkup logic I’ve used as well.

Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5: Setting Up Failover Alerts
Part 6: High-Level Options and Rationale for Handling Batch Jobs
Part 7: Detecting Primary Replica Ownership
Part 8: Dynamic Detection Gotcha #1
Part 9: Dynamic Detection Gotcha #2
Part 10: Dynamic Detection Gotcha #3
Part 11: A Fork in the Road
Part 12: Issues with Enabling and Disabling Batch Jobs
Part 13: Creating and Populating a Batch Job State Table