AlwaysOn Availability Groups and SQL Server Jobs, Part 12: Issues with Enabling and Disabling Batch Jobs

There obviously has to be some sort of Logic running behind the scenes that will Enable/Disable jobs as needed (and especially during/after/around failovers).

We obviously need some way of keeping track of which Jobs should be Enabled/Disabled based upon their affinity with the Availability Group database being targeted. Or, in other words, if we have 18 SQL Server Agent jobs deployed on a server but only three of them are Batch Jobs that target one of our Availability Group (AG) databases, we can't just blindly assume that we can Enable/Disable all 18 jobs on a server based upon whether or not the Server in question is hosting the Primary Replica we're targeting. Instead, we only want to toggle the Enabled/Disabled status of our three AG jobs.

Further, just because we've been able to differentiate our three AG-level jobs from the other 15 jobs on the server doesn't mean that we can make blind assumptions about which of those jobs should be enabled on a server after a failover occurs. For example, assume that of our three AG-Level jobs, one of them has been intentionally disabled by administrators for, say, a few days. Assume, likewise, that Server1 is currently hosting the Primary Replica of our target database. Then, assume that a day after we disable one of those three jobs, we fail over. We CAN logically assume that once we failover that all three AG-level jobs on Server1 should be disabled. But, we can't blindly assume that all three AG-Level jobs should be enabled on Server2. (Or, if we did assume that we'd obviously run into issues.) Granted, we might try to query Server1 to see what the state of those three jobs were, but the whole idea of Availability Groups and failover is the notion that Server1 most likely won’t be available to query if/when a failover occurs. So, we need some other means of tracking the state of AG-Level Jobs (i.e., whether they've been explicitly Disabled or not by Admins so that they can REMAIN disabled after a failover).

Identifying AG-Level Databases. First, to identify which SQL Server Agent Jobs on a given server are batch jobs (or target an Availability Group database), we’ll use a pattern or technique from SQL Server Database Mirroring—exposed by the SQL Server Customer Advisor Team—of creating custom SQL Server Agent Job Categories—where the name of the Category matches the Availability Group name itself, and (then) where Jobs that target the Availability Group (or databases within it) are linked or associated with the custom Category in question.So, for example, if I have an Availability Group called SSV (with a single database in it, also called SSV4), and I have a Batch Job that I want to associate with the SSV4 database, I'd start by right clicking on the SQL Server Agent > Jobs node, selecting "Manage Job Categories," and create a new Job Category entitled SSV (i.e., targeting the name of the Availability Group NOT the database). I'd then go into the batch job in question and specify that it should belong to the SSV category, as shown below:  And, from this point on, it's very easy to tell that this SQL Server Agent Job is associated with an Availability Group.

Identifying Enabled/Disabled AG-Level Jobs. While the SQLCAT article mentioned above does provide logic (or code) that showcases how to enable/disable Jobs based upon what SQL Server Agent Job Category they belong to, it doesn't address the need to account for SQL Server Agent Jobs that might've been temporarily—but explicitly—Disabled by administrators for whatever reason. To address this issue, we'll end up with an approach that involves a state table—or a simple table that will keep track of Job Names and their Enabled/Disabled status. This, of course, will impose some additional complexity, and cause a number of additional sub-concerns that will need to be addressed as well. The good news, though, is that storage of this table is pretty simple—as we can put it into one of our Availability Group databases (i.e., in this case I'd put it in the SSV4 database) and that means that whatever Server is currently hosting the SSV Availability Group will have access to this state data.