SCRIPT: AAG jobs first step

 IF (SELECT
        ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    WHERE ag.name = 'AAG_name' -- Availability group name
    AND ars.is_local = 1) = 'PRIMARY'
BEGIN
    PRINT 'PRIMARY'
END
ELSE
BEGIN
    PRINT 'SECONDARY'
       DECLARE @JobID uniqueidentifier
    SELECT @JobID = $(ESCAPE_NONE(JOBID));
    EXEC msdb.dbo.sp_stop_job @job_id = @JobID;
END

Comments