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.
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