/*****************************************************************************
*
*   Summary: Use SQL Event Notifications to automate enabling and
*            disabling SQL Agent jobs when a database mirroring failover
*            occurs changing the role of the servers.
*
*   Date: September 7, 2011
*
*   SQL Server Versions:
*         2005, 2008, 2008 R2
*        
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved.
*
*   For more scripts and sample code, check out
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give
*    due credit.
*
*
*   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.
*
******************************************************************************/

 
/* Cleanup

:CONNECT sql2k12-svr1

USE msdb;

DROP PROCEDURE [dbo].[ProcessDBMStateChangeEventNotifications];

DROP EVENT NOTIFICATION CaptureMirroringStateChanges ON SERVER

DROP ROUTE MirroringStateChangeRoute

DROP SERVICE MirroringStateChangeService

DROP QUEUE MirroringStateChangeQueue

EXEC msdb.dbo.sp_delete_job @job_name=N'TestJob', @delete_unused_schedule=1
GO


:CONNECT sql2k12-svr2

USE msdb;

DROP PROCEDURE [dbo].[ProcessDBMStateChangeEventNotifications];

DROP EVENT NOTIFICATION CaptureMirroringStateChanges ON SERVER

DROP ROUTE MirroringStateChangeRoute

DROP SERVICE MirroringStateChangeService

DROP QUEUE MirroringStateChangeQueue

EXEC msdb.dbo.sp_delete_job @job_name=N'TestJob', @delete_unused_schedule=1
EXEC msdb.dbo.sp_delete_job @job_name=N'TestJob', @delete_unused_schedule=1
GO



GO

*/

-- Ensure sql2k12-svr1
SELECT @@SERVERNAME;

USE msdb;
GO

-- Create an Activation Procedure for the Queue
CREATE PROCEDURE [dbo].[ProcessDBMStateChangeEventNotifications]
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
DECLARE @message_body xml
DECLARE @email_message nvarchar(MAX)
WHILE (1 = 1)
BEGIN
    -- Receive the next available message FROM the queue
    WAITFOR (
        RECEIVE TOP(1) -- just handle one message at a time
            @message_body=message_body
            FROM dbo.[MirroringStateChangeQueue]
    ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
        BEGIN
            BREAK
        END
 
    DECLARE @PreviousState INT, @NewState INT, @DatabaseID INT
   
    SELECT @PreviousState = 
		@message_body.value('(/EVENT_INSTANCE/IntegerData)[1]', 'int' ),
            @NewState = 
            @message_body.value('(/EVENT_INSTANCE/State)[1]', 'int' ),
            @DatabaseID =
             @message_body.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int' )
           
    --SELECT
    --CASE @PreviousState
    --    WHEN 0 THEN 'Null Notification'
    --    WHEN 1 THEN 'Synchronized Principal with Witness'
    --    WHEN 2 THEN 'Synchronized Principal without Witness'
    --    WHEN 3 THEN 'Synchronized Mirror with Witness'
    --    WHEN 4 THEN 'Synchronized Mirror without Witness'
    --    WHEN 5 THEN 'Connection with Principal Lost'
    --    WHEN 6 THEN 'Connection with Mirror Lost'
    --    WHEN 7 THEN 'Manual Failover'
    --    WHEN 8 THEN 'Automatic Failover'
    --    WHEN 9 THEN 'Mirroring Suspended'
    --    WHEN 10 THEN 'No Quorum'
    --    WHEN 11 THEN 'Synchronizing Mirror'
    --    WHEN 12 THEN 'Principal Running Exposed'
    --    WHEN 13 THEN 'Synchronizing Principal'
    --    ELSE 'Unknown'
    --END AS PreviousState
   
    DECLARE @jobname NVARCHAR(128)
 
	-- Database Starting Up as Principal Role
    IF (@PreviousState = 0 AND @NewState = 13) 
    BEGIN
        -- Enable the Backup and Maintenance Jobs
        SELECT  @jobname = MIN(name)
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN 
        ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
 
        WHILE @jobname IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_update_job @job_name = @jobname, @enabled=1;
 
            SELECT  @jobname = MIN(name)
            FROM msdb.dbo.sysjobs
            WHERE name NOT IN 
            ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
              AND name > @jobname
        END
    END
   
	-- Failover has occurred
    IF (@PreviousState IN (1,2) AND @NewState IN(7,8))  
    BEGIN
        -- Disable the Backup and Maintenance Jobs
        SELECT  @jobname = MIN(name)
        FROM msdb.dbo.sysjobs
        WHERE name NOT IN 
        ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
 
        WHILE @jobname IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_update_job @job_name = @jobname, @enabled=0;
 
            SELECT  @jobname = MIN(name)
            FROM msdb.dbo.sysjobs
            WHERE name NOT IN 
            ('syspolicy_purge_history', 'Database Mirroring Monitor Job')
              AND name > @jobname
        END
    END
 
END
GO
 
--  Create a service broker queue to hold the events
CREATE QUEUE [MirroringStateChangeQueue]
    WITH STATUS=ON,
         ACTIVATION
            (PROCEDURE_NAME = [ProcessDBMStateChangeEventNotifications],
             MAX_QUEUE_READERS = 1,
             EXECUTE AS OWNER);
GO

--  Create a service broker service receive the events
CREATE SERVICE [MirroringStateChangeService]
    ON QUEUE [MirroringStateChangeQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
 
CREATE ROUTE [MirroringStateChangeRoute]
    WITH SERVICE_NAME = 'MirroringStateChangeService',
    ADDRESS = 'LOCAL';
GO
 
CREATE EVENT NOTIFICATION [CaptureMirroringStateChanges]
    ON SERVER
    WITH FAN_IN
    FOR DATABASE_MIRRORING_STATE_CHANGE
    TO SERVICE 'MirroringStateChangeService', 'current database';
GO

-- Let's create a sample job on the principal and mirror

:CONNECT sql2k12-svr1
USE [msdb]

EXEC msdb.dbo.sp_add_job N'TestJob', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'SQLSKILLSdemos\Administrator'

GO

:CONNECT sql2k12-svr2
USE [msdb]

EXEC msdb.dbo.sp_add_job N'TestJob', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'SQLSKILLSdemos\Administrator'

GO

-- Check the status of the job in SSMS - is it enabled?

-- Let's perform a failover to the mirror

:CONNECT sql2k12-svr1
USE master;

ALTER DATABASE CreditReporting
SET PARTNER FAILOVER;

GO

-- What is the TestJob status now?

-- Fail back (may have accumulated redo operations from last demo)
:CONNECT sql2k12-svr2
USE master;

ALTER DATABASE CreditReporting
SET PARTNER FAILOVER;

GO

-- What is the TestJob status now?
