Managing Jobs On SQL Server AlwaysOn Availability Groups

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.

Advertisements

One thought on “Managing Jobs On SQL Server AlwaysOn Availability Groups

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s