This is the sixth post from my short series in which I demonstrate you 7 (sic!) different ways to track database schema changes. Thanks to this, you always can easily answer questions about what has changed, who changed it and when.
Originally, I thought that I will publish only 5 posts in this series. However, it seems that I underestimated the multitude of possibilities provided by SQL Server. Today, in this blog post I describe how to use Event Notifications for this purpose.
Here you can find my previous posts from this series:
- Part 1 – Default Trace
- Part 2 – Transaction Log
- Part 3 – Extended Events Session
- Part 4 – DDL Trigger
- Part 5 – SQL Server Audit
Event Notifications
The sixth option is Event Notifications. This functionality has been introduced in SQL Server 2005. It offers a programming alternative to DDL triggers and SQL Trace. Event notifications can be used to log changes or activities occurring on the database. They execute asynchronous actions in response to a variety of DDL and SQL Trace events by sending information about these events (XML-formatted) to a Service Broker service.
Thanks to their asynchronous nature they run out of the scope of the running transaction and don’t consume its resources. Unlike SQL Trace, Event Notifications can be used to perform an action inside an instance of SQL Server in response to a SQL Trace event.
Event Notifications Architecture
To implement an event notification, you must first create a target Service to receive event notifications and then create the Event Notification. To create a Service, you must first create a Queue and have a Contract that specifies what Messages’ Types can be used for conversations with this Service.
Fortunately, Service Broker has already built-in following specific Message Type and Contract for event notifications:
https://schemas.microsoft.com/SQL/Notifications/PostEventNotification
Creating an Event Notification solution
As a first step, we need to ensure that Service Broker is enabled for the database we want to monitor. Run this query to check it.
SELECT [Name], [is_broker_enabled] FROM sys.databases WHERE [name] = 'TrackMyChanges';
If Service Broker is disabled, then you can enable it using this query.
ALTER DATABASE [TrackMyChanges] SET ENABLE_BROKER; GO
The second step is to create a queue that will store all messages generated by our Query Notification solution. Fortunately enough we can provide only the Queue name and we don’t have to set any additional options.
USE [TrackMyChanges] GO CREATE QUEUE dbo.EventNotificationQueue GO
Use below query to confirm the Queue creation.
SELECT * FROM sys.service_queues WHERE name = 'EventNotificationQueue' GO
The third step is to create a Service that will receive our notification messages and place them in our Queue. To do this, we have to provide three things: the name for our new Service, the Queue name that will be used by this service, and a Contract name. As mentioned earlier, we don’t have to create a new contract because we can use the already existing one.
CREATE SERVICE [//TrackMyChanges/EventNotificationService] ON QUEUE dbo.EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO
Run below query to check if the Service is created.
SELECT * FROM sys.services WHERE name = '//TrackMyChanges/EventNotificationService' GO
The fourth and final step is to create an Event Notification that in response to DDL_DATABASE_LEVEL_EVENTS sends their information to our Service. It’s the same event group that we used in Part 4 for DDL trigger.
CREATE EVENT NOTIFICATION NotifyDDLEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE '//TrackMyChanges/EventNotificationService' , 'current database' GO
Run this query to check if Event Notification is created.
SELECT * FROM sys.event_notifications WHERE [name] = 'NotifyDDLEvents'; GO
Now, every DDL event from DDL_DATABASE_LEVEL_EVENTS group is being captured and stored in our queue. You can test it running our test case from this series’ first blog post.
Viewing captured events
Captured Events are stored in a Queue which is actually a table. However, this is a special table. You cannot use INSERT, UPDATE, and DELETE statements to modify its data.
You can use the SELECT statement to review its current content or RECEIVE statement to receive and remove (default behavior) a message from the queue.
To preview messages in the Queue run following query.
SELECT CAST(message_body AS XML) AS message_in_xml FROM dbo.EventNotificationQueue GO
Here you have an example of one of the messages.
<EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2018-12-02T14:44:09.030</PostTime> <SPID>54</SPID> <ServerName>MAREK-PC\SS2017</ServerName> <LoginName>Marek-PC\Marek</LoginName> <UserName>dbo</UserName> <DatabaseName>TrackMyChanges</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>NewTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE dbo.NewTable ( Id int IDENTITY NOT NULL, Col1 varchar(20) NULL, Col2 varchar(20) NULL )</CommandText> </TSQLCommand> </EVENT_INSTANCE>
Use this query to receive a message from the Queue.
DECLARE @MessageContent XML; WAITFOR ( RECEIVE TOP(1) @MessageContent = CONVERT(XML, message_body) FROM dbo.EventNotificationQueue ), TIMEOUT 1000; SELECT @MessageContent.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType, @MessageContent.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)' ) as PostTime, @MessageContent.value('(/EVENT_INSTANCE/SPID)[1]', 'int' ) as SPID, @MessageContent.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) as ServerName, @MessageContent.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) as DatabaseName, @MessageContent.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) as LoginName, @MessageContent.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)' ) as SchemaName, @MessageContent.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)' ) as ObjectName, @MessageContent.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)' ) as ObjectType, @MessageContent.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS TSQLCommand
Note that every execution of this query returns you subsequent event from the queue. You can use the SELECT statement to see that the number of messages waiting in the queue has decreased.
Persisting changes in a user table
If you don’t feel comfortable with storing information about schema changes in Service Broker Queue you can very easily create an automatic solution that saves this data in a table.
To do it, we need to create a table where we will insert our data. We can reuse the DatabaseLogs table that was introduced in Part 4.
CREATE TABLE [dbo].[DatabaseLogs]( [Id] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NOT NULL CONSTRAINT DF_DatabaseLogs_DateTime DEFAULT (GETDATE()), [ServerName] [nvarchar](128) NULL, [ServiceName] [nvarchar](128) NULL, [SPID] [int] NULL, [SourceHostName] [nvarchar](128) NULL, [LoginName] [nvarchar](128) NULL, [UserName] [nvarchar](128) NULL, [SchemaName] [nvarchar](128) NULL, [ObjectName] [nvarchar](128) NULL, [TargetObjectName] [nvarchar](128) NULL, [EventType] [nvarchar](128) NULL, [ObjectType] [nvarchar](128) NULL, [TargetObjectType] [nvarchar](128) NULL, [EventData] [xml] NULL, CONSTRAINT [PK_DatabaseLogs] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) GO CREATE NONCLUSTERED INDEX nix_DatabaseLogs ON [dbo].[DatabaseLogs] ([DateTime] ASC) INCLUDE ([Id]); GO
To move events from the queue to the table we need to create an Activation Stored Procedure for our Queue. The queue automatically starts the Activation Stored Procedure every time when a message enters the queue. Here you can find an example of such a stored procedure.
CREATE PROCEDURE dbo.usp_EventNotificationActivation AS BEGIN SET NOCOUNT ON; DECLARE @messageContent XML; DECLARE @messageTypeName sysname; WHILE (1=1) BEGIN BEGIN TRANSACTION; WAITFOR ( RECEIVE TOP (1) @messageContent = CAST(message_body AS XML), @messageTypeName = message_type_name FROM dbo.EventNotificationQueue ), TIMEOUT 5000; IF (@@ROWCOUNT = 0) BEGIN ROLLBACK TRANSACTION; BREAK; END IF @messageTypeName = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' BEGIN BEGIN TRY INSERT dbo.DatabaseLogs ( [DateTime] , [ServerName] , [ServiceName] , [SPID] , [SourceHostName] , [LoginName] , [UserName] , [SchemaName] , [ObjectName] , [TargetObjectName] , [EventType] , [ObjectType] , [TargetObjectType] , [EventData] ) VALUES ( @messageContent.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)' ) , @@SERVERNAME , @@SERVICENAME , @messageContent.value('(/EVENT_INSTANCE/SPID)[1]', 'int' ) , HOST_NAME() , @messageContent.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) , @messageContent.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)' ) , @messageContent.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)' ) , @messageContent.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)') , @messageContent.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(128)') , @messageContent.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') , @messageContent.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)') , @messageContent.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(128)') , @messageContent ); END TRY BEGIN CATCH SET @messageContent= NULL; END CATCH END; COMMIT TRANSACTION; END END;
Now, when the stored procedure is created we need to add it to our queue.
ALTER QUEUE dbo.EventNotificationQueue WITH ACTIVATION ( STATUS = ON, MAX_QUEUE_READERS = 10, PROCEDURE_NAME = dbo.usp_EventNotificationActivation, EXECUTE AS SELF) ; GO
Starting from now, every new event inserted to the queue triggers procedure execution that process all messages waiting in the queue. You can easily check it executing any schema change in your test database.
SELECT * FROM dbo.DatabaseLogs;
What was captured by Event Notifications?
Event Notifications capture exactly the same information as DDL Triggers. This is because they are triggered in response to the same DDL events and have access to the same set of data. We know what object was changed, when it was done, and by whom. We also have access to the SQL query that was executed.
Data retention
Event Notifications don’t provide almost any built-in retention options. Captured events are stored in the queue as messages. These messages are there until they are received (or permanently if you set the queue to retain messages). If you save messages to the table, then you have exactly the same situation as with DDL Triggers. If you want to purge old data then you need to implement such functionality by yourself.
Event Notifications summary
- Contains a lot of details
- Info WHAT exactly was changed
- Access to the executed SQL statement
- Info WHEN object was changed
- Info by WHO object was changed
- Data can be logged to the user table
- Easy to set up
- Possibility to set additional filters
- Easy viewing
- Asynchronous events’ processing
- No default data retention options
- No access to the old object definition
-Marek
Share it:
Nice intro article on my favorite hidden feature in SQL Server! Whoever, you really don’t want to keep it in the queue – it’s not meant for that, think of it more like a message queue where the messages flow through.
One other MASSIVE advantage is that you can go multi-server. For instance, all of my production instances deliver their messages to another machine. That machine saves to a table, produces reports, alerts on changes in real-time, and imports into TFS. And cross-server is still screamingly fast – under 1/10th of a second.
Plus, you can trace non-scheme changes. Errorlogs, severity errors, file growth, etc. once you use it, you keep finding more things. We figured out a way to prevent people from taking backups to non-supported locations and kill them. There’s a ton you can do – the activated stored procedure is king!