Schedule SQL Agent Job With Availability Group

January 28, 2016

There was a tweet that came last night from Lohit about “need guidance on how to enable a set of jobs on secondary AG as soon as failover happens from primary to secondary AG”.  I have implemented a solution for most (I am using “most”  literally because I have not done this for all jobs) of the agent jobs so jobs will run always on the primary node.  For example, I want to run DBCC CHECKDB, recycle error log on primary and all readable secondaries.  That way for the jobs that I want to run on the primary node only  I do not have to do anything after failover. This will work provided you always keep your jobs and schedule in sync between nodes.

Most of my reading/code for this solution and post came from Michael K. Campbell’s (B|T)  post here.

I am using a function to check who is primary?  I am acknowledging that this code is from Michael’s blog.

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE FUNCTION dbo.fn_hadr_group_is_primary ( @AGName sysname )
RETURNS BIT
AS
BEGIN

DECLARE @PrimaryReplica sysname;

SELECT @PrimaryReplica = hags.primary_replica
FROM sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE ag.name = @AGName;

IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary

RETURN 0; -- not primary

END;
GO

Then I use this function in my SQL Agent job step to check if I should run the job or skip if I am not primary.  Below is an example of one of the job steps collecting index fragmentation information. I have the same code on all Replica nodes.

--assuming function is in DBA database
IF DBA.dbo.fn_hadr_group_is_primary('AG_Name') = 1
BEGIN
EXEC [dbo].[Usp_GetIndexFragDetails]
END
ELSE
BEGIN
-- we're not in the Primary - exit gracefully:
PRINT 'This is not the primary replica - exiting with success';
END