Capture DDL Changes in SQL Server 2008

With the new system store procedure in SQL Server 2008, database administrators can now track changes to database tables with ease

Friday, October 03, 2008

Print

Comment

Email

Digg

Del.icio.us

Reddit

Twitter

Think of a mechanism that can automatically log all changes to your database tables; how valuable could those log values be to database administrators. With Microsoft SQL Server 2008, all this can be done with ease. The software comes with an in-built feature known as change data capture (CDC) that captures DDL and DML changes in a database table and stores them in a separate table. In this article, we show how easily one can activate this feature on a database table. The point to be noted here is that only a member of sysadmin server role can enable a database for CDC and once a database has been enabled, any member of the 'dbo' fixed database role can enable it on tables that need to be monitored within the database. Before implementing this feature, let's see how it works. First you need to enable CDC on a database and then the table inside that databasethat needs to be monitored. Once this has been done, there would be a change table created for storing changes. This table gets its data from a database transaction log file. One can create upto two change tables per table. As the CDC feature gets data from database transaction log, the SQL Server agent should be running. .

Implementing CDC
Start SQL Server Management Studio and connect to SQL Server 2008 instance. Once this has been done, open the query window. We would start with creating database for captured changes. We shall create 'ChangeDataCapture' database and before that we shall check if one already exists. This can be done using the following TSQL commands:

use master
go
if exists (select name from sys.databases where name=N'ChangeDataCapture')
drop database ChangeDataCapture
go
use master
go
create database ChangeDataCapture
go

The next step after creation of the database is to enable CDC. There are TSQL commands that can be used to enable CDC on 'ChangeDataCapture' database:

Use ChangeDataCapture
go
exec sys.sp_cdc_enable_db_change_data_capture
go

There is a mechanism in place to check if the database is enabled for CDC. Just type the following command in query window:

select [name] as DBName, is_cdc_enabled from sys.databases

If a database has been enabled for CDC, it will give 1 as the output otherwise it will give 0. One can check output in 'Results' window of SQL Server Management Studio. One can now find cdc schema, cdc user and other system objects in 'ChangeDataCapture' database. Now that our database is enabled for CDC, let's create a table inside 'ChangeDataCapture' database with the name 'PCQuest' and with two columns: one called 'pcquest' and the other, 'pcquestalt'.

Enable CDC on the 'PCQuest' table using the following TSQL commands:

use ChangeDataCapture
go
exec sp_cdc_enable_table_change_data_capture 'dbo','pcquest', @role_name = NULL, @supports_net_changes=1
go

Again check if CDC is enabled on 'PCQuest' table inside 'ChangeDataCapture' database:

select [name] as DBName, is_cdc_enabled from sys.databases

Till now, we have enabled CDC. Now, let's see how it works. First change 'PCQuest' table by adding one more column 'pcquestname' to it.

Alter table pcquest add pcquestname varchar(25)

The changes in 'PCQuest' table are reflected in 'cdc.dbo_pcquest_CT' and can be viewed by typing the following commands:

select OBJECT_NAME( source_Object_ID) AS [Table Name],
DDL_Command as [DDL Command],
DDL_LSN as [Log Sequence Number],
DDL_Time as [DateModified]
From CDC.ddl_history

The result of this command gives details of the changes that have occurred, like name of the table, the command used to alter table, log sequence number and date of modification. All this data is critical to database administrators and makes change tracking simple.

Page(s)   1