The previous section illustrates how you can use DDL triggers to capture server and database events. DDL triggers have two limitations. First, the trigger code is executed synchronously and hence it does not yield a scalable solution. Second, it does not allow capturing some DDL events (such as CREATE DATABASE). In addition, let's say you wanted to have the DDL trigger handler code executed on a different server. You could not do that by just using DDL triggers. Event notifications allow you to handle database, server, and subsets of SQL trace events asynchronously. The CREATE EVENT NOTIFICATION T-SQL statement can be used to create an event notification object. You might wonder how the events are handled asynchronously. The answer involves Service Broker. With the CREATE EVENT NOTIFICATION statement, you provide the name of a Service Broker service to which the specified database, server, or SQL trace events will be posted. The events or messages posted to the Service Broker queue are then handled asynchronously. The event information is posted as XML to the specified Service Broker queue. Service Broker is a new technology introduced in SQL Server 2005 that brings the reliable and asynchronous messaging platform closer to the database engine. It is discussed in detail in Chapter 14, "SQL Server 2005 Service Broker." Here is a summary of how event notifications differs from DDL triggers:
The following is the CREATE EVENT NOTIFICATION syntax: CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASEQUEUE <queue_name> [ WITH FAN_IN ] FOR { event_type | event_group } [ ,...n ] TO SERVICE <'broker_service'> , {<'broker_instance_specifier'> | 'current database' } [;] With the ON clause, you specify whether you want to handle database or server events or events on specified queue. With the FOR clause, you specify which events or event groups you want to listen on. With the TO SERVICE clause, you specify the name of the Service Broker service to which the event XML messages should be posted. The service and queue should already be created. If the Service Broker service uses the activation feature to execute a stored procedure when a message is posted in the queue, the stored procedure should also be already created, as you will see in the following example. If the WITH FAN_IN option is specified, SQL Server will group the same events and send them all together as a single event. SET NOCOUNT ON GO USE [AdventureWorks]; GO -- 1.Create the audit table IF OBJECT_ID('dbo.tblAudit') IS NOT NULL DROP TABLE dbo.tblAudit; GO CREATE TABLE dbo.tblAudit (eventXMLData XML null); GO -- 2. Create the stored procedure IF OBJECT_ID('dbo.sproc_Audit') IS NOT NULL BEGIN DROP PROCEDURE dbo.sproc_Audit; END GO CREATE PROCEDURE dbo.sproc_Audit AS BEGIN DECLARE @eventDataXML varbinary(max); RECEIVE TOP(1) @eventDataXML = message_body FROM [AdventureWorks].dbo.AuditQueue IF CAST(@eventDataXML as XML) IS NOT NULL BEGIN INSERT INTO dbo.tblAudit (eventXMLData) VALUES (CAST(@eventDataXML as XML)); END END GO -- 3. Create the service broker queue and service IF EXISTS(SELECT * FROM sys.services WHERE name = 'AuditService') DROP SERVICE AuditService; GO IF OBJECT_ID('dbo.AuditQueue') IS NOT NULL AND EXISTS(SELECT * FROM sys.service_queues WHERE name = 'AuditQueue') DROP QUEUE dbo.AuditQueue; GO CREATE QUEUE dbo.AuditQueue -- Activation turned on WITH STATUS = ON, ACTIVATION ( -- The name of the proc to process messages for this queue PROCEDURE_NAME = AdventureWorks.dbo.sproc_Audit, -- The maximum number of copies of the proc to start MAX_QUEUE_READERS = 1, -- Start the procedure as the user who created the queue. EXECUTE AS SELF ) ON [DEFAULT] ; GO CREATE SERVICE AuditService ON QUEUE AuditQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO -- 4. Setup event notification CREATE EVENT NOTIFICATION ENDBAudit ON SERVER FOR CREATE_DATABASE, DROP_DATABASE TO SERVICE 'AuditService', 'current database'; GO -- 5. Try out the event notification IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'TestDB1') BEGIN DROP DATABASE TestDB1; END GO CREATE DATABASE TestDB1; GO IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'TestDB1') BEGIN DROP DATABASE TestDB1; END GO --WAITFOR few seconds WAITFOR DELAY '00:00:05'; GO SELECT * FROM dbo.tblAudit; GO -- 6. Cleanup DROP EVENT NOTIFICATION ENDBAudit ON SERVER ; GO IF OBJECT_ID('dbo.tblAudit') IS NOT NULL DROP TABLE dbo.tblAudit; GO IF EXISTS(SELECT * FROM sys.services WHERE name = 'AuditService') DROP SERVICE AuditService; GO IF OBJECT_ID('dbo.AuditQueue') IS NOT NULL AND EXISTS(SELECT * FROM sys.service_queues WHERE name = 'AuditQueue') DROP QUEUE dbo.AuditQueue; GO The script first creates an audit table and then a stored procedure that will be activated whenever an event message is posted to the Service Broker queue. This stored procedure retrieves the message from the queue, casts it as XML, and inserts a row into the audit table. Next, a Service Broker queue and service is created. The CREATE EVENT NOTIFICATION statement then uses this service to monitor CREATE_DATABASE and DROP_DATABASE server events. To try out the event notification mechanism, the script then creates and drops a test database. The SELECT statement on the audit table should show the two rows, containing the event data as XML documents. The last step in the script does the clean-up and removes the objects created. When you run this script, you see two records in the audit table, containing the following XML document: <EVENT_INSTANCE> <EventType>CREATE_DATABASE</EventType> <PostTime>2005-06-26T17:44:38.077</PostTime> <SPID>53</SPID> <ServerName>DDGXP\JUNECTP</ServerName> <LoginName>DDGXP\Darshan Singh</LoginName> <DatabaseName>TestDB1</DatabaseName> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE DATABASE TestDB1;</CommandText> </TSQLCommand> </EVENT_INSTANCE> <EVENT_INSTANCE> <EventType>DROP_DATABASE</EventType> <PostTime>2005-06-26T17:44:38.127</PostTime> <SPID>53</SPID> <ServerName>DDGXP\JUNECTP</ServerName> <LoginName>DDGXP\Darshan Singh</LoginName> <DatabaseName>TestDB1</DatabaseName> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>DROP DATABASE TestDB1;</CommandText> </TSQLCommand> </EVENT_INSTANCE> You can view the metadata for database, object, or assembly event notifications by using the sys.event_notifications catalog view; and you can view the metadata for server event notifications by using the sys.server_event_notifications catalog view. Some other catalog views related to event notifications include sys.event_notification_event_types, sys.trace_events, sys.events, and sys.server_events. |