AlwaysOn Availability Groups and SQL Server Jobs, Part 6: High-Level Options and Rationale for Handling Batch Jobs

Server/Instance-Level Jobs. Or jobs that are run against the server itself—for things like SQL Server Agent job history truncation, index maintenance/corruption checks (typically fired against all active databases on the server), etc.

Availability Group (AG) Level Jobs. Or jobs that specifically target databases that are part of an Availability Group—or jobs which I’m calling ‘batch jobs’ for the sake of these posts.

Backups. Which are fairly similar in some ways to server/instance-level jobs—but not quite the same (as they share some similarities to jobs that target AG-level databases).

TARGETING: With this line of thought—or approach to tackling batch jobs—the idea is that if we can somehow get each job to know if it’s running against the active replica, then our job is mostly done. In other words, if we can basically inject some logic into the start or beginning of each SQL Server Agent Job (or Job Step) that says "If this code is running on a host that owns the primary replica for the target database/Availability Group, then go ahead and run—and if NOT, then go ahead and just short-circuit or exit execution." If that were possible, then Jobs management across servers would be pretty easy—you’d just need to modify existing jobs to do an if check at the start of execution, and then just ensure that this code/logic was synchronized or replicated across all servers (and stayed that way through subsequent changes and modifications to jobs/schedules/etc.).

DYNAMICALLY ENABLING/DISABLING: If it’s not possible (or feasible) to inject targeting (or if-checks) into existing jobs, then—unfortunately – we need to take a bit of a different approach: one where we dynamically enable or disable jobs based upon whether they hosted on the same server or not as the Active Replica of the targeted database. In this sense, a bit of logic or code could be used to make sure that jobs hosted/found on servers that are NOT hosting the primary replica are disabled—that logic’s easy enough. By the same token, however, we can’t assume that jobs targeting an AG database found on server hosting the primary replica should all, by the same logic, be enabled – because such an approach wouldn’t account for situations where a job had been explicitly disabled and should then, by definition, be disabled on a new host if/when a failover occurs. Consequently, the only real way to tackle the ability to dynamically enable/disable jobs means that a state table or some other mechanism of capturing, defining, and enforcing job enabled/disabled states needs to be thrown into the mix (thus complicating things and adding an additional moving part into your HA solution—which you typically want to keep as simple as possible).