Change Data Capture (CDC) in SQL Server tracks INSERT , UPDATE and DELETE activity on your tables by reading the transaction log asynchronously and writing the changes to dedicated history tables
Pre-requisites:
SQL Server Edition: CDC is available on Enterprise, Developer and Standard(2016 and later) and editions , not available on express edition.
SQL Server Agent: To Capture and Cleanup, once we enable CDC two jobs will create automatically.
1) cdc.TEST_capture
2) cdc.TEST_cleanup
Permissions: sysadmin or db_owner
Recovery Model: Full or Bulk logged
Disk Space: CDC changes table store a copy of every changed row. Keep additional storage.
Enable CDC on the Database:
USE TEST
EXEC sys.sp_cdc_enable_db;
Verify CDC enabled or not
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'TEST';

