>>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
@5 system tables gets created automatically.
2. Enable CDC on table by using
@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.
After enabling CDC on table one more addition tracking table
List of automatic jobs:
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.