Event Notifications in SQL Server for Tracking Changes

By: Arshad Ali | Read Comments (6) | Print Arshad is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft. Related Tips: More

Problem

As a DBA, we often need a way to track what events are occurring (changes that are being made) on SQL Server or at the database level. So what is a way to track these changes without much impact on SQL Server performance?

Solution

SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level. This can be done with DDL triggers or SQL traces, but event notifications have the advantage of being asynchronous in nature and running outside of the scope of the transaction and hence it can run inside (or be a part of) a database application to capture defined events without using the resources allocated to the transaction.

Event notification is a programmatic alternative to using DDL triggers or SQL traces and executes in the response to different DDL statements, SQL traces, Service Broker events (like QUEUE_ACTIVATION or BROKER_QUEUE_DISABLED) and then sends this information, in XML format, to the SQL Server Service Broker (SSB) service. In other words, when you create a notification, SQL Server tracks the defined events and the occurrence of that event is written to the SSB service and then you can retrieve this information asynchronously from the SSB queue when needed. Discussion of Service Broker is out of the scope of this tip, but you can learn more about SQL Server Service Broker here.

Let's get started...

In Script #1, I am first checking if Service Broker is enabled at the database level and if not then I am enabling it. Next I am creating a SSB queue and the SSB service will use this queue to store messages coming to this service. This service uses the built-in contract (a contract defines a type of message that can be sent to the SSB service), http://schemas.microsoft.com/SQL/Notifications/PostEventNotification, which is specifically for event notification. Note: you will need exclusive access to the database to run the alter database command.

In Script #2, I am creating two database level notifications. The first will notify when there is a create table command execution and the second will notify when there is an alter table command execution. You can also create a notification event group; for example you can create a single notification with DDL_TABLE_EVENTS to track all events of a table like creation, alteration and drop.

In Script #3, I am creating a server level notification that will be raised whenever an error is raised at the SQL Server instance level. You can query the sys.server_event_notifications catalog view to check if a notification exists on the server or not.

In Script #4, let's verify if these event notifications are actually working. In this script, I am creating a table, then altering it, which will be tracked by both the database level event notifications we created above and then I am using RAISERROR (WITH LOG clause is required to be tracked by server level event notification) to generate an error in SQL Server which will be tracked by our last server level event notification.

In Script #5, event notifications send tracked information to the SSB service in XML format; you can query the queue to see the tracked information, but you need to use the RECEIVE command to retrieve messages from the queue, process them and remove them from the queue as shown below. With the RECEIVE command you can specify the number of records you want to retrieve at one time. In this script, I am using a TOP(1) command to retrieve the top 1 record form the queue and displaying the information that it contains. Event notification sends information in XML format (remember we used the built-in contract with the service we created that defines that only XML data can be written to the service) and that's the reason I am casting the message body to an XML data type. Since I am using a TOP(1) clause with the RECEIVE command, I ran the below command 3 times, since there were 3 records in the queue. You can see the result in the image below the query. You can also use a looping construct to read all the records from the queue instead of running the same script multiple times.

In Script #6, you can use this code to cleanup and drop all the objects, in the reverse order of their creation, which we created for this demonstration.

Permission Requirements

• In order to create a database level event notification, you need to have CREATE DATABASE DDL EVENT NOTIFICATION permissions in that database. To drop it, you must be the owner of the event notification or have ALTER ANY DATABASE EVENT NOTIFICATION permission in that database.
• In order to create a server level notification, you need to have CREATE DDL EVENT NOTIFICATION permissions on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.
• In order to create an event notification to track SQL traces, you need to have CREATE TRACE EVENT NOTIFICATION permission on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.
• In order to create a queue scoped event notification you need ALTER permission on the queue. To drop it, you must be the owner of the event notification or have ALTER permission on the queue.

Notes

• You can query sys.event_notification_event_types to get the list of all the events on which you can create event notifications or you can view DDL events, DDL event groups, SQL trace events, SQL trace event groups.
• To see the differences between event notifications and triggers click here and differences between event notifications and SQL traces click here.
• You cannot directly alter a notification, you need to drop and re-create it.

Next Steps

• Review SQL Server 2005 Service Broker Sample tip.
• Review SQL Server Service Broker example on how to configure, send and receive messages tip.
• Review Understanding Event Notifications on technet.
• Review CREATE EVENT NOTIFICATION T-SQL command on technet.

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code