Event Notifications


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:

  • DDL triggers execute in the scope of the transaction of the action that causes them to fire and trigger code executes synchronously. If required, a DDL trigger can roll back the entire transaction. Event notifications, on the other hand, do not execute in the scope of the transaction of the action that causes them to fire, and hence they cannot roll back the transaction. Event notifications are processed asynchronously, using the Service Broker technology.

  • In addition to database and server events, event notifications also support responding to a subset of SQL trace events.

  • DDL triggers execute on the same server. Because event notifications are based on Service Broker, the events may be processed on a different server.

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.




Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net