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.
- 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.
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 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)', 'nvarchar(128)'),'') SET @eventMsg = 'SchemaName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)', 'nvarchar(128)'),'') SET @eventMsg = @eventMsg + ', ObjectName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(128)'),'') SET @eventMsg = @eventMsg + ', TSQLCommand: ' + COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(max)'),'') SET @eventMsg = @eventMsg + ', LoginName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/LoginName)', 'nvarchar(128)'),'') SET @eventMsg = @eventMsg + ', UserName: ' + COALESCE(@data.value('(/EVENT_INSTANCE/UserName)', '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):
“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:
“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:
“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.
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.
- 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.
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?