Automatic Failover - an incomplete solution

Automatic failover with Database Mirroring is great, but what about all of the other things that you need to happen when the database fails over? There is no built-in support in Database Mirroring for anything external to the database. This is where automatic activation in the Service Broker comes to the rescue.

Service Broker and Automatic Activation

The Service Broker can be used to automatically activate a stored procedure when certain events occur. The stored procedure would need to process the messages in the Service Broker's queue and respond accordingly. Setting this up requires the following database objects stored externally to the mirrored database:

1. Stored procedure to process the messages in the queue
2. Service Broker queue
3. Service Broker service
4. A route for the Service Broker service
5. An Event Notification for the DATABASE_MIRRORING_STATE_CHANGE event

Automatic activation stored procedure

Your stored procedure will need to read from the Service Broker queue and process the messages. The event will be sent as an XML message and will need to be parsed to determine how the database mirroring state has changed. For the following example, the Service Broker queue that I am going to create will be named DBMirrorQueue. This sample code reads the first message in the queue and parses the message. Based ont he parsed message, you will need to decide whether you should take any action.

Declare @Message XML,
    @DBName sysname,
    @MirrorStateChange int,
    @ServerName sysname,
    @PostTime datetime,
    @SPID int,
    @TextData nvarchar(500),
    @DatabaseID int,
    @TransactionsID int,
    @StartTime datetime

/* Receive first unread message in service broker queue */
Receive Top (1) @Message = Cast(message_body as XML)
From DBMirrorQueue;

/* Parse type of state change and database affected */
Set @MirrorStateChange = @Message.value('(/EVENT_INSTANCE/State)[1]', 'int'); -- 7 or 8 = database failed over, 11 = synchronizing, 1 or 2 = synchronized
Set @DBName = @Message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
Set @ServerName = @Message.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname');
Set @PostTime = @Message.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
Set @SPID = @Message.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
Set @TextData = @Message.value('(/EVENT_INSTANCE/TextData)[1]', 'nvarchar(500)');
Set @DatabaseID = @Message.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int');
Set @TransactionsID = @Message.value('(/EVENT_INSTANCE/TransactionsID)[1]', 'int');
Set @StartTime = @Message.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime');

Service Broker Queue

Creating a Service Broker queue named DBMirrorQueue:

/* Create Queue if not exists */
If Not Exists (Select 1 From sys.service_queues Where name = 'DBMirrorQueue')
  Begin
    Create Queue DBMirrorQueue
        With Status = On,
        Retention = Off,
        Activation (Procedure_Name = dbo.DBA_MirroringStateChanged,
            Max_Queue_Readers = 1,
            Execute As Self);
  End

Service Broker Service

Creating a Service Broker Service named DBMirrorService on the BMMirrorQueue using the built-in PostEventNotification service type:

/* Create Service if not exists */
If Not Exists (Select 1 From sys.services Where name = 'DBMirrorService')
  Begin
    Create Service DBMirrorService
        On Queue DBMirrorQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
  End

Service Broker Route

Creating a Service Broker route named DBMirrorRoute for the DBMirrorService service:

/* Create Route if not exists */
If Not Exists (Select 1 From sys.routes Where name = 'DBMirrorRoute')
  Begin
    Create Route DBMirrorRoute
        With Service_Name = 'DBMirrorService',
        Address = 'Local';
  End

Event Notification

DATABASE_MIRRORING_STATE_CHANGE is not one of the events that can be automatically trapped. The event is available as a manually created Event Notification.

Creating an event notification named DBMirrorStateChange to raise a notification for the DATABASE_MIRRORING_STATE_CHANGE event:

/* Create Event Notification if not exists */
If Not Exists (Select 1 From sys.server_event_notifications Where name = 'DBMirrorStateChange')
  Begin
    Create Event Notification DBMirrorStateChange
        On Server
        For DATABASE_MIRRORING_STATE_CHANGE
        To Service 'DBMirrorService', 'current database';
  End