Automatically tracking database schema changes


Last modified: 2010-11-14

How can you quickly find out what changes have recently been made to your database schema?

Previous blogs have shown how to include version numbers with a database and how checksums can help ensure a schema is as expected.

But, what do you do if you find that the schema has been ‘unexpectedly’* changed?

This blog will look at how DDL triggers and logging can be used to track changes in your schema and help trouble shoot if there are issues.

* Someone has changed it outside of the standard deployment process.

Overview

  • DDL triggers can be used to automatically track schema changes.
  • Table is created in the schema to log these changes
  • This table can be queried to find recent changes.

Note
Thank-you to those who pointed out that MS SQL 2K8 enterprise edition and Oracle have schema auditing functionality built in – see LinkedIn discussion from a previous blog

The MS sample database, AdventureWorks, contains similar functionality to that outlined in this blog.

DDL triggers

DDL triggers were introduced in MS SQL 2K5 and can be used to track schema changes.

They fire primarily in response to TSQL statements that start with the keywords CREATE, ALTER, DROP and GRANT.

They give access to the EVENTDATA function which has a whole host of useful info – including the TSQL statement that was executed.

A script – 007_CreateTrigger_tr_DDL_Event.sql – is provided which includes an example trigger that will capture DDL events and logs them.

The following ‘snippets’ highlight and explain some of the key sections in this trigger:

CREATE TRIGGER tr_DDL_Event
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS

‘DDL_DATABASE_LEVEL_EVENTS’ capturing DDL events for this database.

SET @data = EVENTDATA()

SET @eventName = COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),'')
SET @eventMsg = 'SchemaName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', ObjectName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', TSQLCommand: ' + COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),'')
SET @eventMsg = @eventMsg + ', LoginName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),'')
SET @eventMsg = @eventMsg + ', UserName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),'')

‘SET @data = EVENTDATA()’ – capturing the DDL event data (of type XML).

XQueries are run to extract different elements eg ‘TSQL command’ and putting them into a variable @eventMsg.

EXEC dbo.p_EventLog_CreateRecord
@eventType='INFORMATION'
,@programName='tr_DDL_Event'
,@eventName=@eventName
,@eventMsg=@eventMsg

This data is then logged to a table dbo.EventLog (see details below) using a sproc dbo.p_EventLog_CreateRecord.

Note the parameter – ‘,@programName=’tr_DDL_Event’– this is used later as a query filter on dbo.Eventlog.

Storing the changes

By default I include a table – dbo.EventLog and associated sprocs – in all new databases.

This is used to store DDL event logs and for other general logging purposes.

This table can be created using a script similar to that shown below:

 CREATE TABLE dbo.EventLog
 (
	EventLogId int identity(1, 1) NOT NULL,
	EventType nvarchar(15) NOT NULL,
	ProgramName nvarchar(128) NOT NULL,
	CallingProcedure nvarchar(128) NOT NULL,
	EventMsg nvarchar(max) NOT NULL,
	ProgramSectionName nvarchar(128) NOT NULL,
	EventName nvarchar(128) NOT NULL,
	CreatedDate datetime NOT NULL,
	BatchId nvarchar(128) NOT NULL,
	CreatedBy nvarchar(128) NOT NULL
 )
 ALTER TABLE dbo.EventLog

ADD CONSTRAINT  pk_EventLog PRIMARY KEY CLUSTERED ( EventLogId ),

ADD CONSTRAINT ck_EventLog_EventType CHECK (EventType IN ('Success','Error','Information','Warning'))

How this works

If the following script – to create a sproc – is run on the database:

CREATE PROCEDURE dbo.p_TestSproc
AS
BEGIN
	PRINT 'Test sproc'
END
GO

Then this create event will be logged via the DDL trigger and stored in dbo.EventLog

The following query will return the schema changes that have just been made.

SELECT EventName, EventMsg, CreateDate, UserName
FROM dbo.EventLog
WHERE ProgramName='tr_DDL_Event'
ORDER BY EventLogId desc

The first row returned is as follows (note only showing first two columns):

“CREATE_PROCEDURE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: CREATE PROCEDURE dbo.p_TestSproc AS BEGIN PRINT ‘Test sproc’ END , LoginName: Login, UserName: User”

If a SQL user is then given exec permissions on this sproc – the following row is returned:

“GRANT_DATABASE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: GRANT EXECUTE ON [dbo].[p_TestSproc] TO [aspnet_Roles_BasicAccess], LoginName: Login, UserName: User”

If the sproc is dropped – the following:

“DROP_PROCEDURE”,
“SchemaName: dbo, ObjectName: p_TestSproc, TSQLCommand: DROP PROCEDURE dbo.p_TestSproc, LoginName: Login, UserName: User”

What do you do if your database has changed?

You want to refresh you dev and testing environments with the latest copy of the production database.

You’ve received a copy. Initial checks show that the the version number is as expected – but the checksums are not, indicating the schema has had some ‘unexpected’ changes.

What do you do?

Firstly, you probably want of find what unexpected changes have been made to the production system.

But, perhaps more importantly, there is a need to review the current release process and to improve on it so that there is less chance of unexpected changes occurring in the future.

Tracking database schema changes can provide the – what, when, and potentially who – to help in improving the process.

Finding out the differences and tracking down the changes.

To find the unexpected changes a tool such as RedGate SQL Compare can be used.

The production copy will need to be compared to a copy of the database that has the correct/expected schema*.

This process will outline the differences – and the unexpected changes made to the production database. These differences can then be correlated with entries in the dbo.EventLog table. This information can then be used to help in improving the deployment/change control process.

*A future blog will show how database unit testing can help ensure that you have a correct copy of the database schema.

Advantages of this approach

  • Help in trouble shooting. As outlined above, this can be used to track unexpected changes. But, it can also help if there are issues with official deployments as operational/support staff can quickly identify recent changes to a database as part of their overall trouble shooting procedures.
  • An aid in development. The log table can be used to quickly check what changes have been made and the order they have been applied – including security/permission changes.
  • Documentation. This is similar to the point above. DBAs often want to know what is going to be changed in a deployment. This can quickly provide this information.

Gotchas

  • The example DDL trigger provided does not log all the available event data – see EVENTDATA function. It could be easily extended to do so eg add something like the following to the script:
    SET @eventMsg = @eventMsg + convert(nvarchar(max),@data);
    
  • This is being used as an aid to the SDLC/database development. It is not intended to be part of a ‘formal’ security audit on a database.

Source code
All example scripts are provided in the database testing framework which can be downloaded from here.

What next

This blog has shown how schema changes can be tracked and how this can provide value especially when troubleshooting unexpected changes.

The next blog in this series will look how individual SQL objects can be labelled with build version numbers making it easier to:

  • Find out when a SQL object was last changed.
  • Correlate these changes with builds/deployments.

What do you think?

How do you track database changes?

Advertisements

One Response to Automatically tracking database schema changes

  1. Auburn Auto Insurance Quotes…

    […]Automatically tracking database schema changes « Michael Baylon's blog[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: