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.
Briefly, here are the steps needed to create an Event Notification:
- Create a QUEUE
- Create a SERVICE on the QUEUE
- Create an EVENT NOTIFICATION for one or more events to the SERVICE
- Create an activation STORED PROCEDURE to process records in the QUEUE
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.
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.)
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:
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:
<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 queChangeDBOwnerNotificationThe 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)', 'VARCHAR(1024)'); SET @EventType = @MsgBody.value('(/EVENT_INSTANCE/EventType)', 'VARCHAR(256)'); SET @ServerName = @MsgBody.value('(/EVENT_INSTANCE/ServerName)', 'VARCHAR(256)'); SET @StartTime = @MsgBody.value('(/EVENT_INSTANCE/StartTime)', 'VARCHAR(256)'); SET @LoginName = @MsgBody.value('(/EVENT_INSTANCE/LoginName)', 'VARCHAR(256)'); SET @HostName = @MsgBody.value('(/EVENT_INSTANCE/HostName)', 'VARCHAR(256)'); SET @ApplicationName = @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)', 'VARCHAR(256)'); SET @Success = @MsgBody.value('(/EVENT_INSTANCE/Success)', '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:
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).