Monitoring Changes in Your Database Using DDL Triggers

Introduction

Additions, deletions, or changes to objects in a database can cause a great deal of hardship and require a dba or developer to rewrite existing code that may reference affected entities. To make matters worse tracking down the problematic alteration(s) may be synonymous to locating the needle in the haystack. Utilizing a DDL trigger in conjunction with a single user created table, used to document such changes, can considerably minimize the headaches involved in tracking and locating schema changes.

Creating the Table and DDL TRIGGER

The first step in implementing such a tracking strategy is to create a table that will be used to record all DDL actions fired from within a database. The below code creates a table in the AdventureWorks sample database that will be used to hold all such DDL actions:

USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID        INT PRIMARY KEY IDENTITY(1,1),
Command    NVARCHAR(1000),
PostTime   NVARCHAR(24),
HostName   NVARCHAR(100),
LoginName  NVARCHAR(100)
)
GO

After creating the table to hold our DDL events it is now time to create a DDL trigger that will be specific to the AdventureWorks database and will fire on all DDL_DATABASE_LEVEL_EVENTS:

CREATE TRIGGER Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
    'NVARCHAR(100)')
SET @hostname = HOST_NAME()
INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)
 VALUES(@cmd, @posttime, @hostname, @loginname)
GO

The purpose of the trigger is to capture the EVENTDATA() that is created once the trigger fires and parse the data from the xml variable inserting it into the appropriate columns of our AuditLog table. The parsing of the EVENTDATA() is rather straight forward, except for when extracting the command text. The parsing of the command text includes the following code:

SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

The need for the LTRIM and RTRIM is to strip all leading and trailing white space while the REPLACE is used to remove the carriage return that is added when if using the scripting wizard from SSMS. This will provide the future ability to use SSRS string functions to further parse the command text to offer greater detail.

Once the table and trigger have been created you can test to assure that it is working properly:

UPDATE STATISTICS Production.Product
GO
CREATE TABLE dbo.Test(col INT)
GO
DROP TABLE dbo.Test
GO
-- View log table
SELECT *
FROM dbo.AuditLog
GO

The results of the above query should are shown below:

Zoom in   |   Open in new window

Conclusions

By creating a table to hold all DDL actions and a database level DDL trigger we can successfully capture all DDL level changes to our database and provide greater ability to track and monitor any such change.

As performance of any such action(s) is most often the deciding factor as to whether implement such change control, I have limited excessive parsing or formatting in the above trigger. Consider this the first step, documenting. Later I will post how to utilize reporting services to provide reports showing:

1. DDL action, CREATE, ALTER, DELETE, etc

2. The schema and object affected

3. Workstation executing DDL statements

4. Drill down report to show object dependencies

That will use the documenting objects created above to provide greater insight and detail external of your production environment.

By David Dye, 2008/10/02