-
-
Save EitanBlumin/0975bb20c1a0abc7739ce32a5af716b3 to your computer and use it in GitHub Desktop.
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 |
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).
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!