1、开启具体数据库实例的CDC

USE yourdatabase;
GO
EXEC sys.sp_cdc_enable_db;

查看数据库是否开启cdc配置
select *,is_cdc_enabled from sys.databases

2、开启表级别 CDC

use my_database
GO
exec sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name='my_tables',
@role_name=NULL,
@filegroup_name=NULL
GO

查看表是否开启cdc

select name,is_tracked_by_cdc from sys.tables where name in 
(
'tables1',
'tables2'
)

3、需要注意的先决条件

3.1查看是否开启sqlserver 代理

SELECT * FROM sys.dm_server_services

未开启代理无法使用CDC捕获功能，需要开启

3.2 设置事务隔离机制为READ_COMMITTED_SNAPSHOT

设置数据库为 READ COMMITTED SNAPSHOT

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
GO

设置数据库为 ALLOW_SNAPSHOT_ISOLATION

ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

检查数据库的隔离级别状态

SELECT
    name AS DatabaseName,
    is_read_committed_snapshot_on,
    CASE
        WHEN snapshot_isolation_state = 0 THEN 'OFF'
        WHEN snapshot_isolation_state = 1 THEN 'ON'
        WHEN snapshot_isolation_state = 2 THEN 'IN_TRANSITION_TO_ON'
        WHEN snapshot_isolation_state = 3 THEN 'IN_TRANSITION_TO_OFF'
        ELSE 'UNKNOWN'
    END AS SnapshotIsolationStateDesc
FROM
    sys.databases

也可以用下面的sql

SELECT
    name AS DatabaseName,
    is_read_committed_snapshot_on,
    snapshot_isolation_state,
    snapshot_isolation_state_desc
FROM
    sys.databases
WHERE
    name = 'my_database';  -- 替换为你的数据库名称