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. .
Direct Hit! |
Applies To: Database admins USP: Capturing DDL changes using stored procedures Primary Link: SQL Server 2008 Keywords: www.microsoft.com |
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
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
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 ,
DDL_Command as
DDL_LSN as
DDL_Time as
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.