CDC (CHANGE DATA CAPTURE)

 >>What is Change Data capture? [CDC]

1. Microsoft SQL Server 2008 has introduced a very exciting feature for logging DML changes.

2. Change data capture provides information about DML changes on a table and a database.

3.Change data capture records insert, update, and delete activity , that’s applied to a SQL Server table and makes a record available of what changed, where, and when, in simple relational 'change tables’.

4. Also stores historical data and COLUMN level changes in SQL Server by using CDC feature.

5. Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server

>>How it works>

1. The source of change data for change data capture is the SQL Server transaction log.

2. As inserts, updates, and deletes are applied to tracked source tables, entries that describe those changes are added to the log.

3. The log serves as input to the change data capture process. This reads the log and adds information about changes to the tracked table’s associated change table.

>>Permissions required to configure CDC:

EITHER DB_OWNER OR SYSADMIN permissions

>>CDC Configuration steps:

1. Enable CDC on database by using

EXEC sys.sp_cdc_enable_db

@5 system tables gets created automatically.

[cdc].[captured_columns]

[cdc].[change_tables]

[cdc].[ddl_history]

[cdc].[index_columns]

[cdc].[lsn_time_mapping]

[dbo].[systranschemas]

2. Enable CDC on table by using

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'MyTable',

@role_name = NULL

Note: Few CDC system table and 2 CDC jobs create automatically inside of the SQL Server databases

CDC Default Tables:

cdc.captured_columns: This table returns result for list of captured column.

cdc.change_tables: This table returns list of all the tables which are enabled for capture.

cdc.ddl_history: This table contains history of all the DDL changes since capture data enabled.

cdc.index_columns: This table contains indexes associated with change table.

cdc.lsn_time_mapping: This table maps LSN number (for which we will learn later) and time.

dbo.systranschemas:

After enabling CDC on table one more addition tracking table

Ex: CDC.DBO_STAB_CT

List of automatic jobs:

cdc.DBNAME_capture

cdc.DBNAME_cleanup

Findings:

Select * from CDC.DBO_STAB_CT

If the operation column shows value

1: Delete operation

2: Insert operation

3: Before update

4: After update

Along with this data gets captured into CDC defined table.

Note: Enable CDC only with confirmation from apps team or client... If you enable it consumes more hardware resource and additional storage is required.