The function I use fn_hadr_group_is_primary from this post is a UDF (User Defined Function) that we create in the master database. The function gets an Availability Group name as an input parameter and returns a Boolean value indicating weather this instance is a primary replica.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- fn_hadr_group_is_primary USE master; GO IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL DROP FUNCTION dbo.fn_hadr_group_is_primary; 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; |
Use the fn_hadr_group_is_primary function within a new job step to find if this sql instance is a Primary replica. If this is not a primary replica we issue a stop job request while identifying the current job name using SQL Server Agent Tokens
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Detect if this instance's role is a Primary Replica. -- If this instance's role is NOT a Primary Replica stop the job so that it does not go on to the next job step DECLARE @rc int; EXEC @rc = master.dbo.fn_hadr_group_is_primary N'my-ag'; IF @rc = 0 BEGIN; DECLARE @name sysname; SELECT @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))')); EXEC msdb.dbo.sp_stop_job @job_name = @name; PRINT 'Stopped the job since this is not a Primary Replica'; END; |