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