Brief Introduction to Service Broker Event Notification

Event Notification is integrated into Service Broker. It can capture SQL events asynchronously in the SQL Server instance and route event messages to a specific queue. With minimal system overhead, you can track events that occur in SQL Server instances, for example, user logon, stored procedure compilation, permission modification, and object processing, such as Create/Alter/Drop events for databases, assemblies, roles, or tables ).

Unlike creating your own Service Broker application, because you can control the initiator components, you only need to create the queue and Service Broker components after using event notifications. Message Types and conventions of the initiator component used to capture and send Event Notifications) have been built in SQL Server.

Use create event icationicationhttp: // msdn.microsoft.com/en-us/library/ms189453.aspx)

With regard to SQL Trace, I believe many people have used Profiler, which is the query tracker provided by SQL Server. For more instructions on SQL Trace and profiler-event tracker, see MSDN: http://msdn.microsoft.com/en-us/library/ms187929.aspx

[Important Note]: The Event Notification function is similar to SQL Trace, except that it is asynchronous and has the least impact on the overall performance of SQL Server instances.

The following example shows how to capture all the Create Login, Alter Login, and Drop Login commands executed in the SQL Server instance through event notification.

/************************** Downmoon 3w@live.cn *********** ************/

-- Capturing Login Commands
-- Create a sample database
If exists (SELECT name FROM sys. databases WHERE name = 'eventtracking ')
Drop database EventTracking
GO
Create database EventTracking
GO

USE EventTracking
GO
-- Create a queue
Create QUEUE SQLEventQueue
With status = ON;
GO

-- Create a service and associate it with the built-in Event Notification conventions
Create service [// logs/TrackLoginModificationService]
On queue SQLEventQueue
([Http://schemas.microsoft.com/ SQL /icationications/posteventnotification]);
GO

-- Query sys. databases in the system directory View
Select service_broker_guid
From sys. databases
WHERE name = 'eventtracking'
/*
Service_broker_guid
18FD2712-E551-4B6A-BC88-58E16D8D5BCD
*/

-- Create event notifications within the Server scope to track creation, modification, and deletion of all login names

---- Drop event notification EN_LoginEvents
---- ON Server;

Create event notification EN_LoginEvents
ON SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
To service '// logs/TrackLoginModificationService ',
'18fd2712-E551-4B6A-BC88-58E16D8D5BCD ';

-- Test the new event notification and create a login name
---- DROP login TrishelleN
---- Go

Create LOGIN TrishelleN with password = 'ar! 3i2ou4'
GO

-- Query the queue using Select or Recieve (where Recieve will delete event messages in the queue)
Select cast (message_body as xml) EventInfo
FROM dbo. SQLEventQueue

/*
<EVENT_INSTANCE>
<EventType> DROP_LOGIN </EventType>
<PostTime> 2011-05-05T17: 10: 50.610 </PostTime>
<SPID> 52 </SPID>
<ServerName> Alibaba \ AGRONET09 </ServerName>
<LoginName> login \ Administrator </LoginName>
<ObjectName> TrishelleN </ObjectName>
<ObjectType> LOGIN </ObjectType>
<DefaultLanguage> us_english </DefaultLanguage>
<DefaultDatabase> master </DefaultDatabase>
<LoginType> SQL Login </LoginType>
<SID> ukcT55ooZECq0 + bpBuvi1A ==</SID>
<TSQLCommand>
<SetOptions ANSI_NULLS = "ON" ANSI_NULL_DEFAULT = "ON" ANSI_PADDING = "ON"
QUOTED_IDENTIFIER = "ON" ENCRYPTED = "FALSE"/>
<CommandText> DROP login TrishelleN
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
*/

 

Summary

This example demonstrates how to set Event Notifications by performing the following steps:

1. Create a queue in an existing database

2. Create a new service bound to the new queue and built-in Event Notification conventions

3. Use the create event notification command to track one or more events or EVENT groups

The Event Notification feature provides a low-overhead Method for tracking SQL Server instances, databases, or Service Broker application-level activities. As you can see in the example, tracking events only requires a very small amount of code. This new feature is particularly useful for IT security or business-level audits. For example, when a logon name creation event is captured, the user who created the logon name and the SQL login name type are also captured), the default database, language, and Security Identifier of the new logon name.