Capture DDL Changes in SQL Server 2008

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 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 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

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.