I love SQL Server 2005.  Everything about it fits and fills every need that I've ever had.. from small scale implementations to large scale replication efforts spanning several hundred miles.

The only beef that I've ever had is how to properly version control the changes developers make to the DDL stuff... stored procedures, triggers, table schema, UDF's, etc..  I know that there is a way that you can integrate that into VSS and TFS, but I refuse to ever use VSS anymore and being a small education based team, we can't afford full blown TFS.  We use Subversion for our source control and to be honest, I couldn't be happier with it.  Much like SQL Server 2005, it flawlessly does everything that I need.

So that leaves me with the only control method of scripting everything out from the DB, and version those scripts with SVN.  A manual process that is very easy to break with no real capability to see who broke it and when.

Rarely has a piece of technology been presented to me that made me go "I need to move that into production NOW!".

A few days ago, one of my developers was surfing through MSDN on an unrelated issue and stumbled upon the fact that SQL Server 2005 supports DDL Triggers.  Now we all know what triggers are. 

<Soapbox mode="On">

Data Manipulation Language (DML) Triggers

Personally, I dislike them.  Not because they are a bad thing in concept, but because they over abused by being used to hide undocumented and undefined business rules.  DML Triggers are often a source of pain because it's not readily apparent that they are acting on a table, and for that reason, can often add hours to trouble shooting code when you are trying to figure out why you are inserting value X into table Y and for some reason it stores value Z instead.  I'm of the firm believer that triggers should only be used for automated data audit trails.  IE, if you insert, update or delete a record from a table, then a trigger would insert a corresponding history record into that tables companion audit/history table.  Anything beyond that is simply begging for trouble.  However, DML triggers aren't what I want to talk about today.

</Soapbox>

Data Definition Language (DDL) Triggers

DDL Triggers are somewhat of a different animal since they work in response to various DDL Events.  These events primarily correspond to the TSQL commands CREATE, ALTER and DROP.  By using the EVENTDATA function in the DDL trigger, we can now automagically create an audit trail of all the changes in our DDL!

The Setup:

Taking from the example found here we first create a DBChangeLog table using this code:

CREATE TABLE [dbo].[DBChangeLog](
    [LogId] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [varchar](256) NOT NULL,
    [EventType] [varchar](50) NOT NULL,
    [ObjectName] [varchar](256) NOT NULL,
    [ObjectType] [varchar](25) NOT NULL,
    [SqlCommandXml] XML NOT NULL,
    [EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()),
    [LoginName] [varchar](256) NOT NULL
) 

The primary difference between the sample in the link provided and the way we're using it is the field that stores the SQL command executed is an XML data type.  The main reason for this is because of the nature of the projects we have here, our stored procedures are MASSIVE.  Many of them weigh in at over 50k when scripted out.  Even at VARCHAR(MAX) 8000 characters just simply isn't enough to store these scripts as they are modified.

The next part is to create the trigger and enable it for use:

CREATE TRIGGER [LOG_DB_OBJECT_CHANGE] ON DATABASE
FOR create_procedure, alter_procedure, drop_procedure
    , create_table, alter_table, drop_table
    , create_function, alter_function, drop_function
AS

    SET NOCOUNT ON

    DECLARE @data XML
    SET @data = EVENTDATA()

    INSERT INTO DBChangeLog(databasename, eventtype, objectname, 
                            objecttype, sqlcommandxml, loginname)
    Select @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
        , @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
        , @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
        , @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
        , @data.query('(/EVENT_INSTANCE/TSQLCommand)[1]')
        , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [LOG_DB_OBJECT_CHANGE] ON DATABASE

As you can see, not really complex or hard to understand.  It does just what a trigger is supposed to do, create an audit trail of data that's modified.. but in this instance, the data is the DDL and it's pulled from the EVENTDATA function.  A rather outstanding side effect of using the XML data type as the storage mechanism for the TSQLCommand is that it keeps the original format of your command intact.  If you stuffed the command into a varchar or nvarchar field, then all your line breaks are lost.

So now that we've had our DDL audit trigger in place, we have a nice trail of data that we can query against:

SELECT 
    logid
    , databasename
    , eventtype
    , objectname
    , objecttype
    , sqlcommandxml.query('(/TSQLCommand/CommandText)[1]')
    , eventdate
    , loginname
 FROM DBCHANGELOG

Using SQL Management Server to execute this query works AMAZINGLY well with the XML return from the query.  It provides a click-able hyperlink that will open the XML text into a new tab.  But a picture (or two) is worth a thousand words :)

So clicking on the hyperlink gives us this:

Now the REAL power of this script comes not in the fact that you know who made the change and where the changes were made, but the fact that you know what changes were made and the order they were made in.  Since you have all of this information at your fingertips, it now becomes a trivial task to put together a migration and implementation script for pushing your code changes from your development environment to your production environment.  Everything is logged, so nothing is forgotten about, changes to the production environment now happen in the order they were made in the development environment, etc, etc, etc.  All in all, we've been running this script in our development, model, and production environments for about 2 weeks now, and rarely have I seen such a level of return on value from such a small level of effort and input.

Our environment is a little different than the one outlined above.  We have several DB's that all work together as part of our software suite.  So I've created a main "Administration" database that holds the DBChangeLog table.  All of the DDL Triggers write their data to Administration..DBChangeLog so that I have the information for ALL the databases in a central spot.

To make things even easier, I've included the SQL script to implement this into your environment that you can download here.

If you have any questions, or find this useful, leave me a comment.  I'd love to hear from you :)