SQL Server CDC简单总结

库级别启用CDC：
USE AdventureWorks
GO
EXECUTE sys.sp_cdc_enable_db;

GO

查看库级别CDC是否启用：

SELECT  IS_CDC_ENABLED    ,CASE WHEN IS_CDC_ENABLED = 0        THEN 'CDC功能禁用'       ELSE 'CDC功能启用' END 描述 FROM   sys.databases WHERE  NAME = 'testhvr';


创建成功后，将自动添加CDC用户和CDC架构。

对表启用CDC：以HumanResources.Department为例

GO
EXEC sys.sp_cdc_enable_table@source_schema= 'HumanResources',
    @source_name = 'Department',@role_name = NULL

检查表HumanResources.Department是否开启CDC：
ELECT  name ,
        is_tracked_by_cdc ,
        CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
             ELSE 'CDC功能启用'
        END 描述
FROM    sys.tables

WHERE   OBJECT_ID= OBJECT_ID('HumanResources.Department')

--返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;
GO

--库级别禁用CDC
EXECUTE sys.sp_cdc_disable_db;
--表级别禁用CDC
USE testhvr;
GO
EXECUTE sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name = 'test',
    @capture_instance = 'all';