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
Best Answer
Step 1: Create a service to receive the notifications and a queue for it:
Note that I'm using
msdb
, this is not an accident. Because the server level event notifications are sent frommsdb
it is much better if you create the opposite conversation endpoint (the target) also inmsdb
, which implies that the destination service and the queue must also be deployed inmsdb
.Step 2: create the event notification processing procedure:
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:
Error
andEndDialog
messages appropriately by ending the dialog from it's side. Not doing so results in handle leaks (sys.conversation_endpoints
grows)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.Besides, this code also does some good-practice code with regard to the task at hand (monitoring DBM):
post_time
(when was the notification sent?),start_time
(when did the action that triggered the notificaton start?) andprocessing_time
(when was the notification processed?).post_time
andstart_time
will likely be identical or very close, butprocessing_time
can be seconds, hours, days apart frompost_time
. the interesting one for audit is usuallypost_time
.post_time
and theprocessing_time
are different, it should be obvious that a DBM monitoring task in an even notification activated procedure has no business looking atsys.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.Step 3: attach the procedure to the queue:
Repeating the above steps on all instances involved in mirroring ensures you get notified no matter which instance is the principal.