Change data capture in sql server 2008 1

Another great feature has been added in sql server 2008 that is Change data capture(CDC). As name suggests, it keeps track on data change in the real production environment. It records all DML operations like INSERTs, UPDATEs and DELETEs to sql server tables. One can easily find out what modification has been done on the tables at what time and what data has been changed due to DML operation.

In sql server 2005, you have to use triggers like after insert, after update and after delete to keep a track on data change.  These triggers are introduced as new feature in sql server 2005 to help DBAs to answer auditing questions related to data change.

Sql server 2008 has improved the data tracking techniques by introducing Change data capture.

When you apply CDC, it creates auditing table having same column and table structure as source table in the database.

How to check whether Change data capture is already applied on database?

To find the status of the database whether it’s applied or not, you have to execute below query in master database.

Use master

Select name, is_cdc_enabled from  sys.databases

It will return database name and CDC status, if is_cdc_enabled is set to 1 then it’s already applied on the database. If it’s set to zero then you have to enable the Change data capture feature for database in sql server 2008.

 

Now Question is How to enable Change data capture on database level?

To enable the change data capture feature, you have to execute below query at database level.

Use testdb

Exec sys.sp_cdc_enable_db

After executing the above t-sql using sql server management studio, you will see schema has been created under “security -> schema” also some extra system tables has been created that are listed in testdb database under CDC schema.

 

cdc.captured_column : Name suggests, it stores list of capture column

cdc.change_tables: Stores list of tables that are participating in data change.

cdc.ddl_history: History of all DDL change since change data capture enabled.

cdc.index_columns: Indexes related to change table

cdc.lsn_time_mapping: stores LSN number and time of change

 

These tables will stores all metadata related to data change and internals which help to track the data more efficiently. Change data capture is table level activity so now you have to enable CDC for each table that you want to audit.

  How to enable Change Data Capture on one or more tables