Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?

Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to understand and make everything he says.

So far I think what I need to create a notification event for DATABASE_MIRRORING_STATE_CHANGE, am I correct?

how can I create this event notification to when its triggered inserts a line in a table, that stores a timestamp and an ID that comes from the notification.

so far I'm setting up one alert per ID, each one running a job like this(this example is for ID = 1):

    DECLARE @state AS varchar(50);
    SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
    IF (@state IS null) SET @state = ' ';
    INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), 1, 'Principal synchronized with W ', @state, @@SERVERNAME)

Basically I'm creating an internal log in this database:

CREATE TABLE [dbo].[MirroringAlerts](
    [DateTime] [datetime] NOT NULL,
    [alertID] [smallint] NOT NULL,
    [alertDesc] [nchar](50) NOT NULL,
    [Sync] [nchar](12) NOT NULL,
    [alertCreator] [nchar](128) NULL
) ON [PRIMARY]

But this way...the alerts are not being triggered fast enough...so I'm loosing information...

Can you tell me how to program this behaviour with create event notification for Database Mirroring State Changed event?

Best Regards

share|improve this question

2 Answers 2

Step 1: Create a service to receive the notifications and a queue for it:

use msdb;
go

create queue dbm_notifications_queue;
create service dbm_notification_service
    on queue dbm_notifications_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification dbm_notifications
    on server   
    for database_mirroring_state_change
    to service N'dbm_notification_service', N'current database';
go

Note that I'm using msdb, this is not an accident. Because the server level event notifications are sent from msdb it is much better if you create the opposite conversation endpoint (the target) also in msdb, which implies that the destination service and the queue must also be deployed in msdb.

Step 2: create the event notification processing procedure:

use msdb;
go

create table dbm_notifications_errors (
    incident_time datetime not null,
    session_id int not null,
    has_rolled_back bit not null,
    [error_number] int not null,
    [error_message] nvarchar(4000) not null,
    [message_body] varbinary(max));
create clustered index cdx_dbm_notifications_errors 
    on dbm_notifications_errors  (incident_time);
go

create table mirroring_alerts (
    alert_time datetime not null,
    start_time datetime not null,
    processing_time datetime not null,
    database_id smallint not null,
    database_name sysname not null,
    [state] tinyint not null,
    [text_data] nvarchar(max),
    event_data xml not null);
create clustered index cdx_mirroring_alerts
    on mirroring_alerts (alert_time);   
go      

create procedure dbm_notifications_procedure
as
begin
    declare @dh uniqueidentifier, @mt sysname, @raw_body varbinary(max), @xml_body xml; 

    begin transaction;
    begin try;
        receive top(1)
            @dh = conversation_handle,
            @mt = message_type_name,
            @raw_body = message_body
        from dbm_notifications_queue;
        if N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' = @mt
        begin
            set @xml_body = cast(@raw_body as xml);
             -- shred the XML and process it accordingly
             -- IMPORTANT! IMPORTANT!
             -- DO NOT LOOK AT sys.database_mirroring
             -- The view represents the **CURRENT** state
             -- This message reffers to an **EVENT** that had occured
             -- the current state may or may no be relevant for this **PAST** event
            declare @alert_time datetime
                , @start_time datetime
                , @processing_time datetime = getutcdate()
                , @database_id smallint 
                , @database_name sysname
                , @state tinyint
                , @text_data nvarchar(max);

            set @alert_time = @xml_body.value (N'(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
            set @start_time = @xml_body.value (N'(//EVENT_INSTANCE/StartTime)[1]', 'DATETIME');
            set @database_id = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseID)[1]', 'SMALLINT');
            set @database_name = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
            set @state = @xml_body.value (N'(//EVENT_INSTANCE/State)[1]', 'TINYINT');
            set @text_data = @xml_body.value (N'(//EVENT_INSTANCE/TextData)[1]', 'NVARCHAR(MAX)');

            insert into mirroring_alerts (
                alert_time, 
                start_time,
                processing_time,
                database_id,
                database_name,
                [state],
                text_data,
                event_data)
            values (
                @alert_time, 
                @start_time,
                @processing_time,
                @database_id,
                @database_name,
                @state,
                @text_data,
                @xml_body);
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' = @mt
        begin
        set @xml_body = cast(@raw_body as xml);
        DECLARE @error INT
                , @description NVARCHAR(4000);
        WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
        SELECT @error = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
            @description = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'NVARCHAR(4000)');          

        insert into dbm_notifications_errors(
            incident_time,
            session_id, 
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            0,
            @error,
            @description,
            @raw_body);
            end conversation @dh;
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' = @mt
        begin
            end conversation @dh;
        end
        commit;
    end try
    begin catch
        declare @xact_state int = xact_state(), 
            @error_number int = error_number(), 
            @error_message nvarchar(4000) = error_message(),
            @has_rolled_back bit = 0;
        if @xact_state = -1
        begin
            -- Doomed transaction, it must rollback
            rollback;
            set @has_rolled_back = 1;
        end
        else if @xact_state = 0
        begin
            -- transaction was already rolled back (deadlock?)
            set @has_rolled_back = 1;
        end
        insert into dbm_notifications_errors(
            incident_time,
            session_id, 
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            @has_rolled_back,
            @error_number,
            @error_message,
            @raw_body);
        if (@has_rolled_back = 0)
        begin
            commit;
        end
    end catch
end
go

Writing service broker procedure is not your run-of-the-mill code. One must follow certain standards and is very easy to stray off into quicksand territory. This code shows some good practices:

  • wrap the message dequeue and the processing in a transaction. No brainer, obvious.
  • always check the message type received. A good service broker procedure must handle Error and EndDialog messages appropriately by ending the dialog from it's side. Not doing so results in handle leaks (sys.conversation_endpoints grows)
  • always check if a message was dequeued by RECEIVE. Some samples check@@rowcount after RECEIVE, which is perfectly OK. This sample code relies on the message name check (no message implies NULL message type name) and handles that case implicitly.
  • create a processing errors table. the background nature of SSB activated procedures makes it really difficult to troubleshoot errors if the messages simply vanish w/o a trace.

Besides, this code also does some good-practice code with regard to the task at hand (monitoring DBM):

  • differentiate between post_time (when was the notification sent?), start_time (when did the action that triggered the notificaton start?) and processing_time (when was the notification processed?). post_time and start_time will likely be identical or very close, but processing_time can be seconds, hours, days apart from post_time. the interesting one for audit is usually post_time.
  • since the post_time and the processing_time are different, it should be obvious that a DBM monitoring task in an even notification activated procedure has no business looking at sys.database_mirroring view. That view will show the current state at the moment of processing, which may or may not be related to the event. If the processing occurs a long time after the event is posted (think maintenance downtime) than the issue is obvious, but it can handle also in 'healthy' processing if the DBM changes state very fast and post two (or more) events in a row (which happens frequently): in this situation the processing, as in the code you posted, audit the event as they occur, but will record the current, final, state. Reading such an audit could be very confusing later.
  • always audit the original XML event. This way you can later query this XML for any information that was not 'shredded' into columns in the audit table.

Step 3: attach the procedure to the queue:

alter queue dbm_notifications_queue
with activation (
    status=on,
    procedure_name = [dbm_notifications_procedure],
    max_queue_readers = 1,
    execute as  owner);
share|improve this answer
    
So I should do this in both Partners right? In case of failure in the Principal with no Witness, is there a way to process/check the queue? to know if I have access to all change state situations, or if there is something that has not been logged in my notifications table –  RagnaRock Jan 12 '12 at 11:17
    
You should do it on both partners, right. In case of failure on the Principal if msdb is still online (ie. the failure is a DB failure, not a server failure) then the queue processing will occur. –  Remus Rusanu Jan 12 '12 at 17:46
    
Thanks for the award. At the very least, you now have a copy of "Pro SQL Server 2008 Mirroring" which I hear is a good book on the topic. –  Remus Rusanu Jan 12 '12 at 19:08

I had to buy "Pro SQL Server 2008 Mirroring" after reading chapter 6, I found out the steps to do this are:

check if service broker is enabled

SELECT CASE is_broker_enabled
WHEN 1 Then 'Enabled'
ELSE 'Disabled'
END
FROM sys.databases
WHERE name = 'DataBaseName'

if not, run

ALTER DATABASE DataBaseName set ENABLE_BROKER;

create the stored procedure we want to be triggered when the notification event arrives:

CREATE PROCEDURE dbo.dba_MirroringStateChanged
AS
DECLARE @Message XML,
        @DBName sysname,
        @MirrorStateChange INT,
        @ServerName sysname,
        @PostTime datetime,
        @SPID INT,
        @TextData NVARCHAR(500),
        @DatabaseID INT,
        @TransactionsID INT,
        @StartTime datetime;
SET NOCOUNT ON;
-- Receive first unread message in service broker queue
RECEIVE TOP (1)
@Message = CAST(message_body AS XML)
FROM DBMirrorQueue;

BEGIN TRY
    -- Parse state change and database affected
    -- 7 or 8 = database failing over,
    --11 = synchronizing,
    --1 or 2 = synchronized
    SET @MirrorStateChange =
    @Message.value('(/EVENT_INSTANCE/State)[1]', 'int');
    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');
END TRY
    BEGIN CATCH
        PRINT 'Parse of mirroring state change message failed.';
    END CATCH

IF (@MirrorStateChange IN (1,2,3,4,5,6,7,8,9,10,11,12,13))
BEGIN

    DECLARE @state AS varchar(50);
    SELECT @state = mirroring_state_desc FROM SYS.database_mirroring WHERE mirroring_guid IS NOT NULL;
    IF (@state IS null) SET @state = ' ';
    INSERT INTO MirroringAlerts (DateTime, alertID, alertDesc, Sync, alertCreator) values (SYSDATETIME(), @MirrorStateChange , @TextData , @state, @SERVERNAME);

END

create a queue, to be some kind of middleman between the service and the stored procedure we want to trigger

-- Create Queue if not exists
IF NOT EXISTS (SELECT 1
    FROM sys.service_queues
    WHERE name = 'DBMirrorQueue')
BEGIN
    CREATE QUEUE DBMirrorQueue
    WITH ACTIVATION (
    PROCEDURE_NAME = dbo.dba_MirroringStateChanged,
    MAX_QUEUE_READERS = 1,
    EXECUTE AS OWNER);
END

create the service that will be associated with the event

-- 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

Create a route

-- 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

and then create the Event Notification

-- 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.