Overview
Change Data Capture also known as CDC, and its helpful to track and capture the changes which are performed on SQL Server Database tables.
Change Data Capture tracks the INSERT, UPDATE, DELETE operations on database tables and then add this information to the mirrored table. This is very useful feature for data replication.
To enable the CDC, you must have sysadmin or db_owner role and make sure SQL Server Agent service is started.
Enable CDC
In order to start capturing the data changes, you need to enable CDC at database level first and then individual table level for all tables that need to be tracked.
1. To check if the change data capture(CDC) is enabled for your database run below query
SELECT name, is_cdc_enabled FROM sys.databases
2. If CDC is not enabled, You can enable CDC on database by executing stored procedure sys.sp_cdc_enable_db (Transact-SQL).
Use <database name>; exec sys.sp_cdc_enable_db
3. After the database has been enabled for CDC, we have to enable the CDC for individual source tables by using the stored procedure:
use <database-name> EXEC sys.sp_cdc_enable_table @source_schema = N'mySchema', @source_name = N'MyTable', @role_name = null, @supports_net_changes = 1
4. To determine whether a source table has already been enabled for change data capture, examine the is_tracked_by_cdc column in the sys.tables catalog view
select name, is_tracked_by_cdc from sys.tables; Once the CDC tracking is enabled for the table, check the system tables and you will find the metadata tables are being created for the same table with schema cdc and name ends with _CT. Please see the below example image:
5. By default, all the columns in the source table are identified as captured columns. If only a subset of columns needs to be tracked, use the @captured_column_list parameter to specify the subset of columns.
6. To verify the change data is getting captured, run the insert, update or delete query on the source table and you will find the changed data log in the created sys table (_CT):
Select * from [cdc].[mySchema_myTable_CT]
Example:
Select * from [cdc].[dbo_StudentMaster_CT]
7. To get source tables, their capture instance name, and captured columns list, run
EXEC sys.sp_cdc_help_change_data_capture