Introduction

By implementing a DDL Auditing solution you can capture data definition language events (CREATE TABLE, ALTER PROCEDURE etc), allowing you to answer important questions like who changed what and when.  It can give you a complete history of changes that have been made to objects in your database, detailing when each change was made and who the change was made by.

Auditing of DDL events can be done automatically, providing you with a lightweight, zero effort solution that documents what changes have occurred in your server/database.

The purpose of this article is to share some of my own experience implementing a DDL auditing solution and to introduce you to the tool I developed to manage it, the T-SQL DDL Code History Tool.   This can be downloaded for free on my website http://www.wisesoft.co.uk.

Here are some of the benefits provided by the tool:

•          Tracks DDL Events (schema modifications)
•          Explorer style user interface.  Similar to SQL Server Management Studio
•          See a history of schema changes for any given object in a database.
•          See a history of schema changes made by any given user.
•          See what recent changes have been made on your server/database.
•          Advanced search/filter capabilities when required.
•          Recycle Bin node for objects that no longer exist in the database or databases that no longer exist on the server.
•          Create a point in time DDL Snapshot of your database.
•          Integrates with Source Gear Diff Merge to provide diff comparisons. 
•          Clipboard compare feature to allow compare of any object from any DB - current or DDL history
•          Quick compare option to compare an object in the current database with an object of the same name in another database.
•          Quick compare option to compare the schema of two databases.
•          Export option
•          Red Gate SQL Compare Integration

Before I discuss how the tool works in more detail, we will look at the options available for capturing DDL events.

Capturing DDL Events

The following methods can be used to capture DDL events:

•          DDL Triggers
•          Event Notifications
•          SQL Trace

DDL Triggers

DDL Triggers were introduced in SQL Server 2005 and they are a popular choice for implementing DDL auditing solutions.  They are very similar to AFTER DML triggers which are familiar territory for most DBAs.  An EVENTDATA() function is used within the trigger to retrieve XML that describes the DDL event.  Here is a very basic example:

CREATE TRIGGER tDDLAudit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @EventDataXML XML
-- EVENTDATA() function returns XML describing the DDL event
SET @EventDataXML = EVENTDATA()
-- Shred the XML and store in an audit table (add additional columns for PostTime, ObjectName etc as required)
INSERT INTO dbo.AuditTable(LoginName,EventType,EventDataXML)
SELECT @EventDataXML.value('(/EVENT_INSTANCE/LoginName)[1]','SYSNAME') AS LoginName,
@EventDataXML.value('(/EVENT_INSTANCE/EventType)[1]','SYSNAME') AS EventType,
@EventDataXML

The XML returned from the EVENTDATA() function when creating a stored procedure might look something similar to this:

<EVENT_INSTANCE>
    <EventType>CREATE_PROCEDURE</EventType>
    <PostTime>2012-01-19T12:35:09.847</PostTime>
    <SPID>63</SPID>
    <ServerName>SQL2012</ServerName>
    <LoginName>david.wiseman</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>MyDatabase</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>MyProcedure</ObjectName>
    <ObjectType>PROCEDURE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON"ANSI_NULL_DEFAULT="ON"ANSI_PADDING="ON"QUOTED_IDENTIFIER="ON"ENCRYPTED="FALSE"/>
        <CommandText>CREATE PROC dbo.MyProcedure
AS 
SELECT 1
        </CommandText>
    </TSQLCommand>
</EVENT_INSTANCE>

A DDL trigger fires after the DDL operation has completed and runs within the same transaction that fired the DDL event.  As DDL triggers run within the scope of the transaction that fired the DDL event it’s possible to rollback the transaction, preventing the DDL event from being committed to the database. The use of DDL triggers goes beyond auditing; for example you could use it to enforce naming conventions. 

Note: In SQL Server 2008 Microsoft introduced the declarative management framework (DMF) which is a better way to enforce naming conventions etc. If you are running SQL Server 2005 or if you want to implement something outside the scope of the DMF, then DDL Triggers are an option to consider.

For auditing purposes the fact that DDL triggers fire synchronously and have the ability to rollback the transaction is not desirable.  We only want to capture and store the DDL events that have occurred – not to prevent them in any way.  We also need to consider that the client will need to wait for the logic in the DDL trigger to complete, slowing down DDL operations.

If you want to capture DDL events in one database and store those events in another database there are some security restrictions that you will need to work around when using DDL Triggers.  Erland Sommarskog wrote an excellent article that will point you to a few different solutions that you can use. 

Older versions of my auditing solution logged DDL events to tables in the same databases that generated the events.  I later decided that I wanted to log events in a separate database and I used certificate signed stored to solve the cross database access problem.  This worked, but it wasn’t a great solution.  The cross-database access issue is one of the reasons I switched to using event notifications.

Event Notifications

Event Notifications were also introduced in SQL Server 2005 and they offer some benefits over DDL triggers for auditing solutions.  Event Notifications make use of the asynchronous programming infrastructure provided by the Service Broker. 

Event notifications are sent to a service broker service and stored in a queue to be processed.  It’s possible to process events automatically as they arrive in the queue using broker activation.  The payload sent to the broker queue is an XML message that is identical to the one generated by the EVENTDATA() function used in DDL triggers.

The processing of DDL events is decoupled from the events that fire them.  This means that it’s not possible to rollback the transaction that generated the DDL event as that transaction has already been committed.  This isn’t a bad thing for an auditing system as it reduces the performance impact on the clients generating the DDL events.  It also means that if an error occurs during the processing of the DDL event, it doesn’t cause the DDL event to fail.  The event isn’t necessarily lost in this case as it’s possible to take the event off the queue as part of the transaction that processes the event.  So if the processing fails, the event remains on the queue.  If a message retrieved from the queue is rolled back 5 times, poison message handling will kick in, disabling the queue and manual intervention will be required to fix the issue.

In SQL Server 2005 it’s not possible to create a server level DDL trigger to capture database level DDL events for all databases on your server.  If you want to capture database level events for every database using DDL Triggers, you either need to create a trigger in each database or upgrade to SQL Server 2008.  One of the advantages of event notifications is that it allows you to create a single event notification at server level to capture database level events for every database on your server, even if you are still using SQL Server 2005.

Event Notifications also make it easier to capture DDL events in one database and process those events in a different database (or even another server).  My first attempt at an auditing solution was done using DDL Triggers, but I switched over to using event notifications.  Event notifications have the advantage of been asynchronous, easier to setup when logging in a separate database and provide the ability to capture database level events at server level in SQL Server 2005.  Event Notifications are used in the T-SQL DDL Code History Tool and we will see how this works later in the article.

SQL Trace

The default trace captures a limited amount of data about DDL events and a report is available in management studio called “Schema Changes History” that you can use to see recent activity on your server.  The report can tell you what’s changed recently and who made the change, but the DDL command is not captured.  It’s also possible to create your own traces, but DDL triggers and event notifications provide a more complete solution for auditing DDL Events. 

T-SQL DDL Code History Tool

The T-SQL DDL Code History tool makes it easy to configure DDL event auditing using event notifications. To get started, please download the tool from my website: http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/installation.aspx

After loading the tool, you connect to your SQL server instance using the connect button on the toolbar.  DDL events can be tracked at the server level for every database on your server or you can enable tracking for specific databases.  To track events at the server level, right-click the server node and select “Configure DDL Event Capture”.  If you want to track events for a specific database, expand the tree and select the “Configure DDL Event Capture” context menu for that database instead. 

Note: If you want to track events for a specific database, you need to make sure the service broker is enabled on that database.  This step isn’t required for tracking events at the server level as the msdb database is already enabled for the service broker.  To enable the service broker you must have exclusive access to the database so the application will prompt you to do this instead of running the command for you.

The first time you configure DDL event capture it will prompt you to create a new database called “zzCodeHistory” which will be used to store our DDL events.  The design of the database is a star schema, a pattern commonly used in data warehouses.  Here is a quick overview of the table design:

The events table is used as a fact table with a grain of 1 row for each DDL event fired.  The objects, logins and eventtypes tables are dimension tables with a one-to-many relationship with the events fact table.  The EventData table is a junk dimension table with a one-to-one relationship with the Events table.  The EventData table preserves the XML generated for the event in its original format.  This table is accessed when the application needs to retrieve the T-SQL command text for a specific event or when the “Event Data” hyperlink is clicked to display the XML for an event. 

The critical elements of the database that enable it to be the target of an event notification are the “DDLEvent_Queue” service broker queue, the "http://www.wisesoft.co.uk/zzCodeHistory/DDLEventService" service and the “DDLEventQueue_Receive” stored procedure which is used to process events from the queue.  The “DDLEvent_Queue_Receive” procedure is specified as an activation stored procedure on the “zzCodeHistory.DDLEvent_Queue” so it automatically runs when there is work in the queue to process.  The stored procedure simply takes events off the queue and inserts the XML into the zzCodeHistory.EventData table. You can see the relevant portion of the database DDL below:

CREATE QUEUE zzCodeHistory.DDLEvent_Queue
GO
CREATE SERVICE [http://www.wisesoft.co.uk/zzCodeHistory/DDLEventService]
ON QUEUE zzCodeHistory.DDLEvent_Queue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
/*
Created By:  David Wiseman
Website:  http://www.wisesoft.co.uk
Description:
DDL Events are captured using event notifications and are placed in a service broker queue.  This stored procedure
is the activation stored procedure to take messages off the queue and insert the captured XML Event data into an audit table
(zzCodeHistory.EventData).  A trigger exists on this table that handles the rest of the processing.
*/CREATE PROCEDURE [zzCodeHistory].[DDLEventQueue_Receive]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @tDDLEvents TABLE(
    EventDataXML XML
);
-- Keep processing messages until timeout (no more messages to process)
WHILE 1=1
BEGIN;
       -- Transaction includes rcv msgs, process, and write to table
       BEGIN TRAN;
       -- Get next 1000 messages from queue.  Timeout after 1 second if no messages are on the queue
       WAITFOR (RECEIVE TOP(1000) message_body
                    FROM zzCodeHistory.DDLEvent_Queue             
     INTO @tDDLEvents
                     ), TIMEOUT 1000;
       -- Exit if not messages returned
       IF @@ROWCOUNT = 0
       BEGIN;
              ROLLBACK TRAN;
              BREAK;
       END;
    -- A trigger will fire on insert into EventData, shredding the XML data and populating various other tables (events, objects etc)
       INSERT INTO zzCodeHistory.EventData(EventDataXML)
       SELECT EventDataXML
       FROM @tDDLEvents
    WHERE EventDataXML IS NOT NULL;
       -- Empty table variable ready to process next batch of events
       DELETE FROM @tDDLEvents;
       COMMIT TRAN;
END;
GO
ALTER QUEUE zzCodeHistory.DDLEvent_Queue
WITH
ACTIVATION
(STATUS=ON,
  PROCEDURE_NAME = [zzCodeHistory].[DDLEventQueue_Receive], 
  MAX_QUEUE_READERS = 1,
  EXECUTE AS OWNER) ;
GO

It’s worth noting that a regular database DML trigger exists on the zzCodeHistory.EventData table which is used to shred the XML data from the event notification, populating the zzCodeHistory.Events table and related dimension tables.  

After creating the database, you will then be prompted to create the event notification. 

In this case we are capturing the event group "DDL_EVENTS" which includes events generated at server level (CREATE LOGIN, DROP DATABASE etc) as well as database level events (CREATE PROCEDURE, ALTER TABLE etc) for every database on the server.  The events and event groups available to capture are documented here.

If you preview the command, it will look something similar to this:

CREATE EVENT NOTIFICATION zzCodeHistory_DDLEvent_Notification
ON SERVER
WITH FAN_IN
FOR DDL_EVENTS
TO SERVICE 'http://www.wisesoft.co.uk/zzCodeHistory/DDLEventService',
'8cb6a875-9cc1-4828-875e-d2a0fb079a01'

The event notification points to the service broker service that exists in the zzCodeHistory database.  The GUID in the script is the broker specifier for the zzCodeHistory database and this will be different on your database.  The application automatically provides the correct GUID for you, but you can also use the following query to identify the GUID to use in your database.

SELECT name,service_broker_guid
FROM sys.databases

After creating the event notification, your DDL audit configuration is now complete.  Whenever a DDL event occurs it will generate an event notification.  This will get put on the transmission queue and then forwarded to the service that exists in the zzCodeHistory database.   When the message arrives in the queue, the activation stored procedure will run automatically, inserting the XML event data into the EventData table.  A DML trigger on the EventData table will fire, shredding the XML and populating the other tables in the database.

What’s cool about the tool?

The T-SQL DDL Code History Tool not only makes it easier to configure a DDL auditing solution, but it also makes the data more accessible.  One of the really cool things about the tool is integration with SourceGear DiffMerge, a free file differencing utility.  This makes it easy to see exactly what has been changes have been made to a stored procedure. 

The tool also includes a quick compare feature that allows you to compare a stored procedure in one database with the same stored procedure in a different database.  If you need to compare two stored procedures with different names, you can use the copy to clipboard feature.  The quick compare feature can even be used to compare an entire database, although I would suggest using Red Gate SQL Compare if you need a tool for comparing and synchronizing databases.  The tool also includes SQL Compare integration if you have a license for the professional edition.

In addition to auditing DDL events, the tool also allows you to create a point in time snapshot of your database schema.  One of the limitations of auditing DDL events for an existing database is that you are starting from scratch without any previous history for your stored procedures.  If someone modifies a stored procedure you won’t have a previous event to compare it to, preventing you from seeing exactly what changes were made.   This problem is solved by using DDL snapshots as they provide you with a point in time copy of your database schema that you can use when no previous events are available. 

Download the tool!

The tool is available to download for free on my website:

http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/default.aspx

The link above also includes a video introduction to the tool if you want to see it in action.  

Installation instructions are available here:

http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/installation.aspx

A list of known issues and limitations is available here:

http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/known_issues.aspx

Note: At the time of writing, the version of the tool available to download is CTP1.  Please let me know if you find any bugs or if you have suggestions for new features.