2016-11-30

SQL Server Event Handling: Event Notifications

This is part of a series of posts that explores Event Handling for the SQL Server Database Engine:

SQL Server Event Handling: DDL Events

In the last Event Handling post, I looked at handling SQL Server events synchronously with DDL triggers. However, there are many events that can't be handled synchronously within the scope of a transaction--DDL triggers can't be used for those. Take the AUDIT_CHANGE_DATABASE_OWNER event, for instance. If you attempt to create a DDL trigger for it, you'll get this error:


Msg 1082, Level 15, State 1, Procedure trgChangeDBOwner, Line 5
"AUDIT_CHANGE_DATABASE_OWNER" does not support synchronous trigger registration.

In this post, I'll look at using Event Notifications to handle events asynchronously. As we'll see, it's more complex to handle events this way than it is with DDL triggers. Event Notifications require some additional objects, and related T-SQL scripts will generally be longer. Hopefully the example shown here will be usable as a framework to help you get started.

Architecture

Briefly, here are the steps needed to create an Event Notification:

  1. Create a QUEUE
  2. Create a SERVICE on the QUEUE
  3. Create an EVENT NOTIFICATION for one or more events to the SERVICE
  4. Create an activation STORED PROCEDURE to process records in the QUEUE
When an Event Notification is created, one or more conversations is created between the SQL Server database engine and a Service. I tend to think of these as "message channels". When the related event occurs, SQL Server calls the EVENTDATA() function, returning the event information results (as a variable of type XML) to a Service. The Service in turn writes the information to a Queue. A Queue is a FIFO data structure. Conceptually it is similar to a table of rows and columns. You can SELECT from it, but you can't directly insert or update its rows. You "delete" rows from a Queue via the RECEIVE statement.

I was purposefully brief (and maybe even vague) in the previous paragraph. It was out of necessity, though. My understanding of the Event Notification architecture and the Service Broker has some gaps. If I got anything wrong, or described things poorly, please leave a comment below. The internet will thank you.

Ok, on to the scripting. Let's create an Event Notification for events that change the owner of a database. We'll start with the two T-SQL scripts on the tabs below.

Create Database

This creates a separate database, in which the various objects will be created. If you want to use the scripts and do your own testing, you can simply drop the database when you're finished. Note that the database is set to TRUSTWORTHY. As I recall, this was necessary to execute msdb.dbo.sp_send_dbmail. (There's probably a better way to handle this via certificates, but I digress.)

Create Objects

This is where the necessary objects related to Event Notifications are created. The last statement enables the Queue (STATUS = ON). After the objects are created, if we take a peek at the Object Explorer in SSMS, delving down into "Service Broker" will reveal the Queue and Service objects.


--Objects for event notifications will be created in a separate database.
IF DB_ID('ENTesting') IS NULL
 CREATE DATABASE ENTesting;
GO

--Enable service broker.
ALTER DATABASE ENTesting
SET ENABLE_BROKER;
GO

ALTER DATABASE ENTesting
SET TRUSTWORTHY ON;
GO
USE ENTesting
GO

--Create a queue for database owner change events.
CREATE QUEUE queChangeDBOwnerNotification
GO

--Create a service for database owner change events.
CREATE SERVICE svcChangeDBOwnerNotification
ON QUEUE queChangeDBOwnerNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Create the event notification for database owner change events on the service.
CREATE EVENT NOTIFICATION enChangeDBOwnerEvents
ON SERVER
WITH FAN_IN
FOR AUDIT_CHANGE_DATABASE_OWNER
TO SERVICE 'svcChangeDBOwnerNotification', 'current database';
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification 
WITH STATUS = ON
GO


Now let's test the Event Notification and look a little bit at what's happening behind the scenes. Run these commands, which change the ownership of [ENTesting] multiple times:

ALTER AUTHORIZATION ON DATABASE::ENTesting TO sa;
ALTER AUTHORIZATION ON DATABASE::ENTesting TO sa;
ALTER AUTHORIZATION ON DATABASE::ENTesting TO sa;


Now select all records from the Queue:

USE ENTesting
GO

SELECT *
FROM dbo.queChangeDBOwnerNotification;


The ownership of the database didn't actually change three times, but we attempted to change it three times. So you should see three records, similar to the following:


EVENTDATA()

The [message_body] column can be cast as XML. This is the event information returned by the EVENTDATA() function. (Check out the Schemas Returned section of the MSDN EVENTDATA documentation and/or the Microsoft SQL Server XML Schemas web page for more information.) The query in the first tab returns the XML, which you can click to view from within SSMS. The second tab is a sample of what the XML looks like:

USE ENTesting
GO

SELECT CAST(message_body AS XML)
FROM dbo.queChangeDBOwnerNotification

<EVENT_INSTANCE>
 <EventType>AUDIT_CHANGE_DATABASE_OWNER</EventType>
 <PostTime>2016-11-30T10:29:50.090</PostTime>
 <SPID>60</SPID>
 <TextData>ALTER AUTHORIZATION ON DATABASE::ENTesting TO sa;</TextData>
 <DatabaseID>15</DatabaseID>
 <TransactionID>91395833</TransactionID>
 <NTUserName>dmason</NTUserName>
 <NTDomainName>CELTICS</NTDomainName>
 <HostName>BostonGarden</HostName>
 <ClientProcessID>1804</ClientProcessID>
 <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
 <LoginName>CELTICS\dmason</LoginName>
 <StartTime>2016-11-30T10:29:50.090</StartTime>
 <Success>1</Success>
 <ServerName>BostonGarden\DBA</ServerName>
 <ObjectType>16964</ObjectType>
 <NestLevel>0</NestLevel>
 <ObjectName>ENTesting</ObjectName>
 <DatabaseName>ENTesting</DatabaseName>
 <OwnerName>dbo</OwnerName>
 <TargetUserName />
 <DBUserName>dbo</DBUserName>
 <LoginSid>AQUAAAAAAAUVAAAA53DCrRi9KvnePLdohQQAAA==</LoginSid>
 <TargetLoginName>sa</TargetLoginName>
 <TargetLoginSid>AQ==</TargetLoginSid>
 <RequestID>0</RequestID>
 <XactSequence>257698037770</XactSequence>
 <EventSequence>1139187</EventSequence>
 <IsSystem />
 <SessionLoginName>CELTICS\dmason</SessionLoginName>
</EVENT_INSTANCE>


Now remove an item from the Queue via the RECEIVE command:

USE ENTesting
GO

RECEIVE TOP(1) *
FROM queChangeDBOwnerNotification


You should see one record in the results pane, similar to the following.


If you select all records from the Queue again, there should only be two remaining:

USE ENTesting
GO

SELECT *
FROM dbo.queChangeDBOwnerNotification;


You can receive all of the records in the Queue if you wish. Just omit the TOP clause:

USE ENTesting
GO

RECEIVE *
FROM queChangeDBOwnerNotification
The Queue should now be empty.


Finally, we get to the good part! Let's create an activation stored procedure. Within a WHILE (1 = 1) loop, there is a WAITFOR...RECEIVE statement that retrieves one message at a time from the QUEUE. The loop continues until there are no more messages left. When a message is retrieved, the event information is stored in the @MsgBody variable. It can be used for just about anything. Use your imagination. In this example, variables are created to hold individual event data items. Then they are used to send an email/alert. After the stored procedure is created, we enable activation on the Queue.

USE ENTesting
GO

CREATE PROCEDURE dbo.ReceiveChangeDBOwnerEvent
/*****************************************************************************
* Name     : dbo.ReceiveChangeDBOwnerEvent
* Purpose  : Runs when there is a AUDIT_CHANGE_DATABASE_OWNER event.
* Inputs   : None
* Outputs  : None
* Returns  : Nothing
******************************************************************************
* Change History
* 01/08/2015 DMason Created
******************************************************************************/
AS
SET NOCOUNT ON
DECLARE @MsgBody XML

WHILE (1 = 1)
BEGIN
 BEGIN TRANSACTION

 -- Receive the next available message from the queue
 WAITFOR (
  RECEIVE TOP(1) -- just handle one message at a time
   @MsgBody = CAST(message_body AS XML)
  FROM queChangeDBOwnerNotification
 ), 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
  ROLLBACK TRANSACTION;
  BREAK;
 END 
 ELSE
 BEGIN
  --Do stuff here.
  BEGIN TRAN
  --Log to table.
  --Check available disk space.
  --Send an email/alert.
  --Etc.

  DECLARE @Cmd VARCHAR(1024);
  DECLARE @EventType VARCHAR(256);
  DECLARE @ServerName VARCHAR(256);
  DECLARE @StartTime VARCHAR(256);
  DECLARE @LoginName VARCHAR(256);
  DECLARE @HostName VARCHAR(256);
  DECLARE @ApplicationName VARCHAR(256);
  DECLARE @Success VARCHAR(256);

  SET @Cmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(1024)');
  SET @EventType = @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(256)');
  SET @ServerName = @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(256)');
  SET @StartTime = @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(256)');
  SET @LoginName = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(256)');
  SET @HostName = @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(256)');
  SET @ApplicationName = @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(256)');
  SET @Success = @MsgBody.value('(/EVENT_INSTANCE/Success)[1]', 'VARCHAR(256)');

  DECLARE @MailBody NVARCHAR(MAX);
  DECLARE @Subject NVARCHAR(255);

  SET @Subject = @@SERVERNAME + ' - ' + @EventType;
  SET @MailBody = 
   '<table border="1">' +
   '<tr><td>Server Name</td><td>' + @ServerName + '</td></tr>' +  
   '<tr><td>Start Time</td><td>' + @StartTime + '</td></tr>' +  
   '<tr><td>Login Name</td><td>' + @LoginName + '</td></tr>' +  
   '<tr><td>Host Name</td><td>' + @HostName + '</td></tr>' +  
   '<tr><td>Application Name</td><td>' + @ApplicationName + '</td></tr>' + 
   '<tr><td>Command Succeeded</td><td>' + @Success + '</td></tr>' + 
   '</table><br/>' +
   '<p><b>Text Data:</b><br/>' + REPLACE(@Cmd, CHAR(13) + CHAR(10), '<br/>') +'</p><br/>';

  EXEC msdb.dbo.sp_send_dbmail 
   @recipients = 'You@YourDomain.com', 
   @subject = @Subject,
   @body = @MailBody,
   @body_format = 'HTML',
   @exclude_query_output = 1;
  
  COMMIT TRANSACTION
   
  /*
   Commit the transaction.  At any point before this, we 
   could roll back - the received message would be back 
   on the queue AND the response wouldn't be sent.
  */
  COMMIT TRANSACTION
 END
END
GO

ALTER QUEUE dbo.queChangeDBOwnerNotification 
WITH 
ACTIVATION ( 
 PROCEDURE_NAME = dbo.ReceiveChangeDBOwnerEvent, 
 STATUS = ON, 
 MAX_QUEUE_READERS = 1, 
 EXECUTE AS OWNER) 
GO


Change the database owner once again:

ALTER AUTHORIZATION ON DATABASE::ENTesting TO sa;


After a moment or two, our notification email should arrive:


Event Types

The list of event types that can be used with Event Notifications can be obtained with a simple query on the sys.event_notification_event_types DMV. You may recognize that some of the events are DDL events, and can be used with DDL triggers (there is a separate query for those events).

Note that some events only have scope at the server level. Other events have server or database scope: Event Notifications for these can be created to handle events that occur in a specific database, or anywhere within the SQL instance. Also, there is a hierarchy of events and Event Groups. Each event belongs to an Event Group (parent_type), which belongs to another Event Group, etc. At the top of the hierarchy is an Event group with no parent (parent_type IS NULL).


SHARE