There are several tools that you can use to help track database changes, so that come release you avoid an error or omission in the database scripts that brings your system crashing to its knees. However keeping change scripts up to date requires a lot of discipline. Tools such as Redgate’s SQL Compare can validate that one database schema matches another and produce change scripts to align them. This is all useful stuff, but some operations such as renaming tables and columns cannot be picked up using this technique. As a result I mainly use these types of tool to verify changes have been successfully applied. You are still left with the problem that a lack of discipline can mean cancelling the release. There is another method you can use in conjunction to the comparison tools however. When I first heard about the ability in SQL 2005 to capture DDL [Data Definition Language] changes I immediately thought about how I could apply the feature to the problem of tracking DB changes.
I got hold of an example script from here and modified it like so:
— Creates an event notification for a SQL 2005 database
— and sends a message to a Service Broker queue
— whenever a database level event is triggered in that database.
— This is a Service Broker feature.
— This allows you to track all DDL changes to a database
— Note that sp_rename’s and data changes are not captured
— Only works on SQL 2005 >
— Put your db name here
IF EXISTS (SELECT * FROM sys.event_notifications WHERE name = N‘DatabaseLevelEventNotifcation’)
DROP EVENT NOTIFICATION DatabaseLevelEventNotifcation ON DATABASE;
IF EXISTS (SELECT * FROM sys.services WHERE name = N‘DatabaseLevelEventService’)
DROP SERVICE [DatabaseLevelEventService];
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N‘DatabaseLevelEventQueue’)
DROP QUEUE [dbo].DatabaseLevelEventQueue;
— If there isn’t already a queue to hold
— Event Notifications, create one:
CREATE QUEUE [dbo].[DatabaseLevelEventQueue] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY];
— Bind a service to the preceding queue; we’ll
— use this service for our event notifications:
CREATE SERVICE [DatabaseLevelEventService] AUTHORIZATION [dbo] ON QUEUE [dbo].[DatabaseLevelEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
— Now, create an event notification that sends event
— messages to the DatabaseLevelEventQueue
— whenever a database level event is triggered in the
— current database:
CREATE EVENT NOTIFICATION DatabaseLevelEventNotifcation
TO SERVICE ‘DatabaseLevelEventService’,‘current database’;
— Rollback all statements if any of them failed.
print ‘Unable to set up database level event notifcation’
——————— Do some work ———————————–
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[TEMPTABLEXYZ]’) AND type in (N‘U’))
DROP TABLE [dbo].[TEMPTABLEXYZ]
CREATE TABLE TEMPTABLEXYZ (ID INT IDENTITY PRIMARY KEY);
ALTER TABLE temptablexyz
ADD newone nvarchar(50) not null
——————— See what we have done ———————————–
select CAST(message_body AS XML) command from [dbo].DatabaseLevelEventQueue
— Click on a command to see it in the xml viewer
The script creates a queue. It then creates a service bound to the queue. The SQL Service Broker uses the name of the service to route and deliver messages to the correct queue in the specified format. All well and good so far, but for now the queue will remain empty. We need to subscribe to a DDL event raised by the database to populate the queue. We do this by subscribing the service to the desired event / event group [in our case database level events] through the CREATE EVENT NOTIFICATION statement.
We do some simple work and select the messages from the queue. Output for the CREATE TABLE statement is shown below
<SetOptions ANSI_NULLS=“ON“ ANSI_NULL_DEFAULT=“ON“ ANSI_PADDING=“ON“ QUOTED_IDENTIFIER=“ON“ ENCRYPTED=“FALSE“ />
<CommandText>CREATE TABLE TEMPTABLEXYZ (ID INT IDENTITY PRIMARY KEY);
As you can see for each event we get an entry posted to the queue containing some xml that details the action carried out. This is fantastic – I thought – A way to automatically track DDL changes to a database and produce diff scripts between two points in time. That is until I added a new column to the table via the designer. Capturing the SQL change script reveals a fundamental flaw in the design of SQL Server. The change script creates a new table with the additional column, copies over the data from the original, drops the original and then renames the table to match the original name. The DDL script does not capture the data copy as expected, but importantly it does not capture the rename. The problem is that SQL Server stores the names of the objects as data in the system tables for a database. Renaming is therefore implemented as a data changing operation rather than what it logically is, a change to the definition of the data. Bang goes my little idea of automating this stuff.
As in all things the lesson to learn from this is that consistency is key. And unfortunately in a way Microsoft are.