Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active September 17, 2020 02:10
Show Gist options
  • Save EitanBlumin/0975bb20c1a0abc7739ce32a5af716b3 to your computer and use it in GitHub Desktop.
Save EitanBlumin/0975bb20c1a0abc7739ce32a5af716b3 to your computer and use it in GitHub Desktop.
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. More info here: https://eitanblumin.com/2018/11/06/automatically-enable-or-disable-jobs-based-on-hadr-role/
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
Copy link

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!

@EitanBlumin
Copy link
Author

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!

Hi friend!
First, I must apologize for taking so long to reply.
It appears that I did not receive any notification about your message, and only stumbled upon it by chance.

I have a blog post that I wrote about this solution which is available here:
https://eitanblumin.com/2018/11/06/automatically-enable-or-disable-jobs-based-on-hadr-role/

It has instructions for basic usage of these scripts. I hope they can be of use to you.
Let me know if you require further assistance (writing a comment on the above blog post will probably get my attention much faster).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment