Archive | SQL Agent RSS for this section

Availability Groups – SQL Agent Jobs

One thing that was strange to me is that with the advent of Availability Groups in SQL Server 2012 is that there isn’t a built in way to have SQL Agent Jobs run specifically for the active AG.

The workaround I have gone with is just adding a statement around the Job Step and then deploying the new version to both [all] nodes.

The script is very simple:

IF( select CASE
WHEN primary_replica = @@servername Then 1
ELSE 0
END
from sys.dm_hadr_availability_group_states) = 1
BEGIN
–Here is where you would put the code for your job step
END

And that is it!

Deploy to all of your nodes and you are set moving forward no matter which node becomes the primary.

 

Questions? Comments?  Please post!