Below script automates sql jobs enable or disable based on a specific sql job naming convention. Place script in a sql job on each node and run every 60 seconds or whatever schedule you require.
If Always On is Enabled and Primary Node – enable all sql jobs that start with AOAG.
If Always On is Enabled and not Primary Node – disable all sql jobs that start with AOAG.
The sql job naming convention allows for occurrences where you want a sql job to run on a Secondary Node(s) that falls outside of the naming convention.
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
IF EXISTS(
SELECT
ARS.Role_desc AS 'Replica Role'
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = ARCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY')
BEGIN
DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1;' FROM msdb..sysjobs where name like 'AOAG%'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
--Select @sql
--RAISERROR (@sql, 10, 1) WITH NOWAIT
EXEC (@sql)
END
IF NOT EXISTS(
SELECT
ARS.Role_desc AS 'Replica Role'
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = ARCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY')
BEGIN
DECLARE @vsql nvarchar(MAX);
SELECT @vsql = (SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0;' FROM msdb..sysjobs where name like 'AOAG%'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
--Select @vsql
--RAISERROR (@sql, 10, 1) WITH NOWAIT
EXEC (@vsql)
END
END
|
|
|
|
|
|