Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This procedure detects whether the specified DB is primary or secondary in DB Mirroring or Availability Groups, and disables or enables a list of jobs accordingly. Run this from a job in both servers.
USE [SomeNonHadrDB]
GO
IF OBJECT_ID('ChangeJobStatusBasedOnHADR', 'P') IS NOT NULL DROP PROCEDURE ChangeJobStatusBasedOnHADR
GO
/*
--Sample usage:
EXEC ChangeJobStatusBasedOnHADR @DBName = 'DB_to_use_as_primary_indicator'
*/
CREATE PROCEDURE ChangeJobStatusBasedOnHADR
@DBName SYSNAME = NULL
AS
SET NOCOUNT ON;
DECLARE @NeedToEnableJobs BIT;
IF DB_ID(ISNULL(@DBName, DB_NAME())) IS NULL
BEGIN
RAISERROR(N'Provided @DBName is invalid: %s', 16,1,@DBName);
RETURN;
END
IF EXISTS (
SELECT
hars.role_desc, d.name
FROM
sys.databases d
INNER JOIN
sys.dm_hadr_availability_replica_states hars
ON
d.replica_id = hars.replica_id
WHERE
database_id = DB_ID(ISNULL(@DBName, DB_NAME()))
AND hars.role_desc = 'SECONDARY'
)
--Not Primary Server
SET @NeedToEnableJobs = 0
ELSE IF EXISTS (
SELECT
hars.role_desc, d.name
FROM
sys.databases d
INNER JOIN
sys.dm_hadr_availability_replica_states hars
ON
d.replica_id = hars.replica_id
WHERE
database_id = DB_ID(ISNULL(@DBName, DB_NAME()))
AND hars.role_desc <> 'SECONDARY'
)
--Yes Primary Server
SET @NeedToEnableJobs = 1
ELSE
BEGIN
--Not in a HADR session
RAISERROR(N'Provided @DBName is not in an Availability Group: %s', 16,1,@DBName);
RETURN;
END
DECLARE @CurrJob NVARCHAR(500)
DECLARE JobsToUpdate CURSOR
READ_ONLY FORWARD_ONLY
FOR
select name
from msdb..sysjobs
where name in
(
'Job name 1',
'Job name 2',
'Job name 3',
'Job name 4',
'Job name 5'
)
and [enabled] <> @NeedToEnableJobs
OPEN JobsToUpdate
FETCH NEXT FROM JobsToUpdate INTO @CurrJob
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR(N'Changing job status for "%s"', 0, 1, @CurrJob) WITH LOG;
EXEC msdb.dbo.sp_update_job @job_name=@CurrJob, @enabled=@NeedToEnableJobs
FETCH NEXT FROM JobsToUpdate INTO @CurrJob
END
CLOSE JobsToUpdate
DEALLOCATE JobsToUpdate
GO
USE [SomeNonMirroredDB]
GO
IF OBJECT_ID('ChangeJobStatusBasedOnMirroring', 'P') IS NOT NULL DROP PROCEDURE ChangeJobStatusBasedOnMirroring
GO
/*
--Sample usage:
EXEC ChangeJobStatusBasedOnMirroring @DBName = 'DB_to_use_as_primary_indicator'
*/
CREATE PROCEDURE ChangeJobStatusBasedOnMirroring
@DBName SYSNAME = NULL
AS
SET NOCOUNT ON;
DECLARE @NeedToEnableJobs BIT;
IF DB_ID(ISNULL(@DBName, DB_NAME())) IS NULL
BEGIN
RAISERROR(N'Provided @DBName is invalid: %s', 16,1,@DBName);
RETURN;
END
IF EXISTS (
SELECT
d.name, dbm.*
FROM
sys.databases d
INNER JOIN
sys.database_mirroring dbm
ON
d.database_id = dbm.database_id
WHERE
dbm.mirroring_role_desc <> 'PRINCIPAL'
AND dbm.database_id = DB_ID(ISNULL(@DBName, DB_NAME()))
)
--Not Primary Server
SET @NeedToEnableJobs = 0
ELSE IF EXISTS (
SELECT
d.name, dbm.*
FROM
sys.databases d
INNER JOIN
sys.database_mirroring dbm
ON
d.database_id = dbm.database_id
WHERE
dbm.mirroring_role_desc = 'PRINCIPAL'
AND dbm.database_id = DB_ID(ISNULL(@DBName, DB_NAME()))
)
--Yes Primary Server
SET @NeedToEnableJobs = 1
ELSE
BEGIN
--Not in a HADR session
RAISERROR(N'Provided @DBName is not member in a Database Mirroring session: %s', 16,1,@DBName);
RETURN;
END
DECLARE @CurrJob NVARCHAR(500)
DECLARE JobsToUpdate CURSOR
READ_ONLY FORWARD_ONLY
FOR
select name
from msdb..sysjobs
where name in
(
'Job name 1',
'Job name 2',
'Job name 3',
'Job name 4',
'Job name 5'
)
and [enabled] <> @NeedToEnableJobs
OPEN JobsToUpdate
FETCH NEXT FROM JobsToUpdate INTO @CurrJob
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR(N'Changing job status for %s', 0, 1, @CurrJob) WITH LOG;
EXEC msdb.dbo.sp_update_job @job_name=@CurrJob, @enabled=@NeedToEnableJobs
FETCH NEXT FROM JobsToUpdate INTO @CurrJob
END
CLOSE JobsToUpdate
DEALLOCATE JobsToUpdate
GO
@dusmantap1974

This comment has been minimized.

Copy link

commented Nov 14, 2018

Hi Team,
I have implemented alwayson availability group in our invironment. I want job should run on the primary server when swithes role. So please provide steps for implementation ChangeJobStatusBasedOnHADR.sql. Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.