SQL Server: Three Common DDL Change Log Methods

Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? These are normal questions when more than one person are working on a same database.

Production environment is mostly kept secure for unauthorized access and few known persons are allowed to make changes BUT still you need to keep a track of these changes and if it’s a development database then it is also must to keep a complete log of each change.

Three major ways, we can keep track of these changes.

1.                 DDL Trigger & Event Notifications

2.                 Extended Events

3.                 Service Broker & Event Notifications

DDL Trigger method is most commonly used method, where we write a ddl (after) trigger on each database separately and using information from event notifications, we decide whether to rollback any DDL change or just dump change information to a table.

How to create a DDL Trigger?

Extended Events, is the most advance method, not only for DDL change tracking but it’s going to be next biggest tool for DBAs. SQL Server 2012, introduced three new events for DDL change tracking.

1.                 object_altered

2.                 object_created

3.                 object_deleted

Paul Randal script for extended event creation is good one to follow, but don’t forget to change events.

 Service Broker (with event notifications), is the best way I have ever found for DDL Change Tracking before SQL Server 2012. Though its initial steps are bit lengthy, that is why; most people avoid using this method.

Using service broker, you can dump all databases changes data to a single table on an instance, or you can transmit changes information as a message to other instance on internet (if need to create a single point of administration for multiple instances).

(What is Service Broker and what type of objects you need to create, can be found here and here)

Use following simple steps to create DDL Changes Log, for multiple databases on an instance.