Change Data Capture in Sql Server
What is CDC?
Ans: SQL Server CDC (Change Data Capture) is
the process of capturing and recording changes made to the Microsoft SQL
Server database. CDC records INSERT, UPDATE, and DELETE operations
performed on a source table and then publishes this information to a
target table. Microsoft SQL Server CDC (Change Data Capture) is
asynchronous by default. This feature is present in SQL Server 2008.
Enable CDC (Change Data Capture) on Databasse: Enable Microsoft SQL Server CDC (Change Data Capture) for the entire database by executing the following query:
After enabling CDC at the database level, we can enable it to track and audit the DML changes on the database tables, by a member of db_owner fixed database role, using the sys.sp_cdc_enable_table system stored procedure, with the ability to enable it to track changes on list of columns specified by the @captured_column_list parameter, and create change tables on a separate filegroup specified by the @filegroup_name parameter,
Enable CDC (Change Data Capture) on a Table:
Check that CDC is enabled on that table, we will query the sys.tables DMV for all tables under the current database with CDC enabled
Once CDC is enabled on the table, a number of system tables will be created under the CDC schema of the database to store the CDC related information. These tables include the following
1. CDC.captured_columns table that contains the list of captured column
2. CDC.change_tables table that contains the list of tables that are enabled for capture
3. CDC.ddl_history table that records the history of all the DDL changes since capture data enabled
4. CDC.index_columns table that contains all the indexes that are associated with change table
5. CDC.lsn_time_mapping table that is used to map the LSN number with the time and finally one change table for each CDC enabled table that is used to capture the DML changes on the source table, as shown below:
And the SQL Agent jobs associated to CDC enabled table, the capture and cleanup jobs, will be created like below:
Disabling CDC
The Change Data Capture can be easily disabled on a specific table using the sys.sp_cdc_disable_table system stored procedure
Can disabled completely at the database level, without the need to disable it on CDC enabled tables one by one, using the sys.sp_cdc_disable_db system stored procedure
Auditing DML Changes
After enabling CDC on the database table, let us perform the below three database DML changes, INSERT, UPDATE then DELETE, on that table and check how these changes will be audited using CDC feature,
Check your Microsoft SQL Server CDC (Change Data Capture) table by executing the following query:
