Mirroring Job Switcher

,

I wrote this so that jobs associated with a mirrored database will run on the principal and not on the mirror.  Please send your comments on what implications I might be missing - I would appreciate that greatly.

This procedure works in conjunction with a WMI alert you will setup in SQLSEVERAGENT -> Alerts ->Mirroring Status Changed. When the status of a mirrored database changes, a job which runs this proc will be called.
It will then deactivate or reactivate as needed, any jobs which have the same category as the mirrored databases name.

create procedure Mirroring_Job_Switcher
as
Begin
/*
This procedure works in conjunction with a WMI alert you will setup in SQLSEVERAGENT -> Alerts ->Mirroring Status Changed. When the status of a mirrored database changes, a job which runs this proc will be called. 
It will then deactivate or reactivate as needed, any jobs which have the
!!SAME CATEGORY AS THE DATABASE'S NAME!!.
*/
/*
This alert creation is for example only - you will need to customize your job_id number to run your Mirroring Job Switcher.

USE [msdb]
GO
/****** Object:  Alert [Mirroring Status Changed]    Script Date: 04/25/2007 10:56:51 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Mirroring Status Changed', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
		@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE', 
		@job_id=N'YOUR UID HERE'
*/

DECLARE @JOBID UNIQUEIDENTIFIER
DECLARE @JOBS_TO_ACTIVATE CURSOR 
SET @JOBS_TO_ACTIVATE = CURSOR FAST_FORWARD FOR
--find mirrored databases which are principal - join to jobs which are not active and activate
--select j.name,j.enabled,j.job_id,db_name(database_id) as dbname, mirroring_state_desc,mirroring_role_desc 
select j.job_id
from sys.database_mirroring mirrors
,dbo.sysjobs_view j
inner join dbo.syscategories c
on j.category_id = c.category_id
where mirroring_role_desc = 'PRINCIPAL'
and c.name = db_name(mirrors.database_id)
and j.enabled = 0

OPEN @JOBS_TO_ACTIVATE
FETCH FROM @JOBS_TO_ACTIVATE INTO @JOBID
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC sp_update_job @job_id = @JOBID ,   @enabled = 1
   FETCH NEXT FROM @JOBS_TO_ACTIVATE INTO @JOBID
END

CLOSE @JOBS_TO_ACTIVATE
DEALLOCATE @JOBS_TO_ACTIVATE

set @JOBID = null

DECLARE @JOBS_TO_DEACTIVATE CURSOR 
SET @JOBS_TO_DEACTIVATE = CURSOR FAST_FORWARD FOR
--find mirrored databases which are mirrors - join to active jobs, to deactivate
--select j.name,j.enabled,j.job_id,db_name(database_id) as dbname, mirroring_state_desc,mirroring_role_desc 
SELECT j.job_id
from sys.database_mirroring mirrors
,dbo.sysjobs_view j
inner join dbo.syscategories c
on j.category_id = c.category_id
where mirroring_role_desc = 'MIRROR'
and c.name = db_name(mirrors.database_id)
and j.enabled = 1

OPEN @JOBS_TO_DEACTIVATE
FETCH FROM @JOBS_TO_DEACTIVATE INTO @JOBID
WHILE (@@FETCH_STATUS = 0)
BEGIN
	EXEC sp_update_job @job_id = @JOBID ,   @enabled = 0
   FETCH NEXT FROM @JOBS_TO_DEACTIVATE INTO @JOBID
END

CLOSE @JOBS_TO_DEACTIVATE
DEALLOCATE @JOBS_TO_DEACTIVATE

END

Rate

Share

Share

Rate