Schedule SQL Agent Job With Availability Group

There was a tweet came last night from Lohit about “need guidance on how to enable a set of jobs on secondary AG as soon as failover happen from pri to sec 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 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 primary node only  I do not have 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 step collecting index fragmentation information. I have 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

 

 

FacebookTwitterGoogle+Share