This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

-- Enabling/Disabling SQL agent jobs on AO instances.

-- <Raghavender Chavva>

-- <20^th March,2014>

-- Last Modified: NA

-- <This query is not suitable for SQL Server versions which are below SQL Server 2012 and SQL Servers which are not in Always On>

--******************************************************************************

--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED

--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

--* PARTICULAR PURPOSE.

--*

--******************************************************************************

-- < This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.>

-- Step 1: -- ---checking instance replica status (here Primary)

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY') 

begin

-- Step 2: -- Colleting the job names

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history')

-- select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

--- Step 3: Enabling the required jobs.

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

-- Step 4: -- ---checking instance replica status (here Secondary)

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY')

–- Step 5:  Colleting the job names

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog')

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

–- Step 6: Disabling the jobs

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0'

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end

this script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

Below is the script with comments, please let me know if I need to add more comments:

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY')   ---checks whether the instance is primary or not

begin

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history') –- Colleting the job names

--select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY') ---checks whether the instance is secondary or not

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog') –- Colleting the job names

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0' –- Disabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end