Last active
September 17, 2020 02:10
-
-
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/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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).