7 ways to track Database Schema changes – Part 6 – Event Notifications

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

-Marek

Share it: