SQL Change Data Capture{Comments Off on SQL Change Data Capture}


The article I read for this week is called Implementing SQL Server’s Change Data Capture by Gregory Larsen. He talks about a new feature called “Change Data Capture (CDC)” came with SQL Server 2008. CDC provides the ability to set up and manage database data auditing without requiring custom auditing procedures and triggers; it captures DML operations (Insert, Update, Delete statements) and makes the altered database available for later reporting. Users can use CDC to track the changes that in a table or tables. CDC is a process that is implemented within a database and allows users to identify the SQL tables, all changes will be tracked. Users can track changes to a single table, or multiple tables, and each change to a table is tracked in separate tables. These tables are stored directly in the database where change data capture is enabled. When users implement change data capture a process is automatically generated and scheduled to collect and manage the change data capture information. By default change data capture information is only kept for 3 days. Users can read the change data tracking tables directly to see what has changed, or use built-in functions to read the data. But, change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

As it’s introduced by Microsoft, CDC is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

The first thing is to enable CDC by using the SQL statement SELECT IS_CDC_ENABLED, NAME FROM SYS.DATABASES. Once Change Data Capture is enabled, each change to a table is tracked in separate table, one for each table being tracked. When the Change Data Capture feature is implemented to a table, change table is created with same set of columns similar to the source table along with some additional meta information columns to get the changes that are done to the source table. The information of changes made to the source table can be read by simple select statement.The Main purpose to implement CDC is for data security purposes. This feature can be implemented for all user created tables.

Reference

Larsen, Gregory: Implementing SQL Server’s Change Data Capture, Database Journal, July 2, 2012