SQL Server AlwaysOn Availability groups are one of the best things to happen to SQL Server over the past few years, and open the door to a high availability SQL Server architecture in a virtualized environment and more flexibility in your overall HA/DR plan. One of the challenges that this presents however, is in the area of SQL Server job management. Because the two (or more) instances in an AAG are truly separate instances, you have to account for this in your SQL Server Agent job management strategy. The same jobs need to be created on all nodes in the AAG that the database could failover to and they need to account for the fact that there are multiple nodes servicing the databases. One way of accomplishing this is by wrapping your job within code to determine if the node is the primary or secondary node in the AAG.
The following code (credit to this StackExchange post) can be used in your job to determine if it’s on the primary replica, and then execute a stored procedure (or whatever you need it to do) if it is. If it’s not, it simply prints a message and exits. You’ll want to replace
<AAG_Listener_Name> with the name of your AAG Listener.
-- Check to see if this is the primary replica in the AAG IF (SELECT ars.role_desc FROM sys.dm_hadr_availability_replica_states ars INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id WHERE ag.name = '<AAG_Listener_Name>' AND ars.is_local = 1) = 'PRIMARY' BEGIN -- This server is the primary replica exec stored_procedure_to_execute END ELSE BEGIN -- This server is not the primary replica PRINT 'This server is not the primary replica, bypassing job execution' END
There are other ways to accomplish this, but this is a simple, straightforward approach that has worked well in my experience.
thank you for share it!