Running Jobs Only on Primary Replica in Availability Group

Transitioning from a failover cluster configuration to an Availability Group configuration brings with it all kinds of “fun” challenges. One such challenge that you may not have considered is the handling of jobs on whatever server is Primary, along with secondary servers. Let’s briefly discuss a potential challenge and an option to address it.

From One to Many

With Availability Groups, there are multiple copies of the databases. There are also multiple copies of logins. And there are also multiple copies of SQL Server jobs.

If you recently setup your jobs on each of your servers, it’s possible that they run smoothly on whatever replica is Primary at the time. When you might encounter errors is when jobs try updating databases on a server that is not the Primary replica. You might notice your jobs failing with errors like:

“Failed to update database “Database_Name” because the database is read-only.”

A Potential Solution

What do you do when you only want jobs to run when the Availability Group Replica is the Primary? One option is to add a job step to your jobs that will check the status of the replica. This can be accomplished by querying sys.dm_hadr_availability_group_states to find the primary_replica value. I added a job step for each job with the following script:

IF (SELECT primary_replica FROM sys.dm_hadr_availability_group_states WHERE group_id = (SELECT group_id FROM sys.availability_groups)) = @@SERVERNAME
BEGIN
	PRINT 'Running on Primary replica.'
END
ELSE
BEGIN
	RAISERROR('This server is not the Primary replica.', 16, 1);
END

This job step will run, continue on if the replica is Primary, or “fail” if it is not the Primary.

Handling “Failure”

There is yet another piece to this puzzle you may want to consider. You may want to specify how that job step is handled when the server is not the Primary replica and the job “fails” as expected. Consider updating the job step “On failure action” from “Quit the job reporting failure” to “Quit the job reporting success” instead. This can save your inbox from filling up with unnecessary job failure e-mails.

Thanks for reading!