Change Data Capture

This article introduces to a new feature in SQL Server 2008, Change Data Capture. Change Data Capture records insert, update and delete activity in your table and the details of these changes are available in a format that can be easily consumed. The database must be enabled to Capture Changes to it. A member of sysadmin fixed role can enable the change. Once that is done, members of db_owner can enable tables for which change needs to be monitored. When the first table in the database is enabled, a capture process gathers changed data from the transaction log and inserts the change information in the associated change table. Additional metadata about each transaction is inserted in a metadata table that allows the captured changes to be placed on a conventional timeline.

Step to Enable and Configure Change Data Capture:
1. Create a database SQL Server 2008
create database CDC_TEST
go
2. Enable CDC in the database created.
USE SQL2008CDC
GO
EXEC sp_cdc_enable_db
GO
You can verify if CDC is enabled using the query below for the database.
select [name] as DBName,is_cdc_enabled from sys.databases where db_name(database_id)=DB_NAME()
3. You will be a new schema CDC and few system tables created under the database. Change Data Capture requires exclusive use of cdc user and schema.
4. Create a table to enable CDC
USE CDC_TEST
GO
CREATE TABLE dbo.CDC_TEST(
ID int Primary Key NOT NULL,
Name nvarchar(100) NOT NULL,
Email nvarchar(100) NOT NULL)
5. SQL Server agent must be started for CDC.
6. Enable CD for the table where you need to capture the change.
EXEC sp_cdc_enable_table 'dbo', 'cdc_test',
@role_name = NULL, @supports_net_changes =1
You can verify if CDC is enabled using the query below for the table.
use SQL2008CDC
go
select [name], is_tracked_by_cdc from sys.tables
7. Once CDC is enabled for a table, you will find a _CT table created by itself.
8. Now we will verify how CDC works by running a insert into the table CDC_TEST.
use SQL2008CDC
go
INSERT INTO dbo.cdc_test
values (1, N'sugeshkumar', N'sugeshkr@gmail.com')
9. Now update the row that we have inserted in the previous step.
use SQL2008CDC
go
update dbo.cdc_test set name='sugesh' where id = 1
10. The before image and after image of the row can be found on the table that was created with the name _CT.
The column _$operation has the value for the DML operation done, 1 is for delete, 2 for insert, 3 for before image, 4 for after image

You have seen how to enable and configure CDC for a database and table. The list below gives you all available Stored Procedures, DMVs, Functions and table list that is used by Change Data Capture.

Stored Procedures
sys.sp_cdc_add_job (Transact-SQL)
sys.sp_cdc_generate_wrapper_function (Transact-SQL)
sys.sp_cdc_change_job (Transact-SQL)
sys.sp_cdc_get_captured_columns (Transact-SQL)
sys.sp_cdc_cleanup_change_table (Transact-SQL)
sys.sp_cdc_get_ddl_history (Transact-SQL)
sys.sp_cdc_disable_db (Transact-SQL)
sys.sp_cdc_help_change_data_capture (Transact-SQL)
sys.sp_cdc_disable_table (Transact-SQL)
sys.sp_cdc_help_jobs (Transact-SQL)
sys.sp_cdc_drop_job (Transact-SQL)
sys.sp_cdc_scan (Transact-SQL)
sys.sp_cdc_enable_db (Transact-SQL)
sys.sp_cdc_start_job (Transact-SQL)
sys.sp_cdc_enable_table (Transact-SQL)
sys.sp_cdc_stop_job (Transact-SQL)

Dynamic Management Views
sys.dm_cdc_log_scan_sessions (Transact-SQL)
sys.dm_cdc_errors (Transact-SQL)
sys.dm_repl_traninfo (Transact-SQL)

Tables
cdc._CT
Returns one row for each change made to a captured column in the associated source table.

cdc.captured_columns
Returns one row for each column tracked in a capture instance.

cdc.change_tables
Returns one row for each change table in the database.

cdc.ddl_history
Returns one row for each data definition language (DDL) change made to tables that are enabled for change data capture.

cdc.lsn_time_mapping
Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.

cdc.index_columns
Returns one row for each index column associated with a change table.

dbo.cdc_jobs (Transact-SQL)
Returns the configuration parameters for change data capture agent jobs

Functions
cdc.fn_cdc_get_all_changes_ (Transact-SQL)
sys.fn_cdc_has_column_changed (Transact-SQL)
cdc.fn_cdc_get_net_changes_ (Transact-SQL)
sys.fn_cdc_increment_lsn (Transact-SQL)
sys.fn_cdc_decrement_lsn (Transact-SQL)
sys.fn_cdc_is_bit_set (Transact-SQL)
sys.fn_cdc_get_column_ordinal (Transact-SQL)
sys.fn_cdc_map_lsn_to_time (Transact-SQL)
sys.fn_cdc_get_max_lsn (Transact-SQL)
sys.fn_cdc_map_time_to_lsn (Transact-SQL)
sys.fn_cdc_get_min_lsn (Transact-SQL)