Change Data Capture in Microsoft SQL Server

With the release of SQL Server 2008, Microsoft included Change Data Capture (CDC) functionality for the first time. This highly anticipated feature provides administrators with the ability to perform detailed auditing of database changes.

What is Change Data Capture?

Change Data Capture allows you to perform detailed tracking of changes made to your database. It records the details of INSERT, UPDATE and DELETE commands executed against the database in a relational format that makes it easy to parse the change records.

Why Would I Need Change Data Capture?

Change Data Capture is useful in any setting where you require the ability to look back at the changes made to a SQL Server database. Some examples include:

• Databases containing sensitive information where you need to record all changes
• Troubleshooting environments where you are attempting to determine why a database change occurred

What are the Drawbacks to Change Data Capture?

CDC is a resource-intensive process. Each time a change is made, the entries made in the change table consume processor resources. The cumulative impact of the change table on storage resources can also be very burdensome. You should not enable CDC unless you have a clearly defined need for its use.

How Do I Enable Change Data Capture?

Change Data Capture is an advanced SQL Server feature and is not available in all editions. This feature is limited to SQL Server Enterprise and SQL Server Developer. For more information on SQL Server 2008 editions, read Choosing the Correct SQL Server 2008 Edition For Your Needs.

To enable CDC, you must first enable it at the database level and then enable it for the specific table(s) you wish to audit. You enable CDC for a database with the following command (issued within the context of the target database):

EXEC sys.sp_cdc_enable_db;

To enable CDC for individual tables, use the sys.sp_cdc_enable_table stored procedure . Here's an example that would enable CDC on a table named accounts in the current database:

EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name= 'accounts';

How Do I Disable Change Data Capture?

You can disable CDC for a database with the following command (issued within the context of the target database):

EXEC sys.sp_cdc_disaable_db;

You can disable CDC at the table level with the following command:

EXEC sys.sp_cdc_disable_table @source_schema='dbo',@source_name= 'accounts';