SQL Server 启用或禁用 CDC 功能

1. 查看库表是否启动 CDC

   -- 查看数据库是否启用cdc
   SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
   
   -- 查看当前数据库表是否启用cdc
   SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;
   

2. 数据库启用和禁用 CDC

   -- 对当前数据库启用 CDC
   USE MyDB 
   GO
   EXECUTE sys.sp_cdc_enable_db;
   GO
   -- 对当前数据库禁用 CDC
   USE MyDB  
   GO  
   EXEC sys.sp_cdc_disable_db  
   GO  
   

3. 数据库表启用和禁用 CDC

   -- 启用
   USE MyDB  
   GO  
   EXEC sys.sp_cdc_enable_table  
   @source_schema = N'dbo',  
   @source_name   = N'MyTable',  
   @role_name     = NULL,  
   @supports_net_changes = 1  
   GO 
   -- 禁用
   USE MyDB  
   GO  
   EXEC sys.sp_cdc_disable_table  
   @source_schema = N'dbo',  
   @source_name   = N'MyTable',  
   @capture_instance = N'dbo_MyTable'  
   GO  
   

4. 查看表 CDC 功能是否启用

   SELECT  name ,
           is_tracked_by_cdc ,
           CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
                ELSE 'CDC功能启用'
           END 描述
   FROM    sys.tables;
   

注意:

1. 开启授权

   ALTER AUTHORIZATION ON DATABASE::[MyDB] TO [sa]
   

2. 需开启 SQL Server Agent 服务

问题及解决办法:
如无法执行启用或禁用表的 CDC，查看主机名的是否一致。

SELECT SERVERPROPERTY('ServerName'), srvname, datasource FROM master.dbo.sysservers;

如不一致,执行下面语句:

IF serverproperty('servername')<>@@servername    
  BEGIN  
  DECLARE  @server SYSNAME  
  SET   @server=@@servername      
  EXEC  sp_dropserver @server=@server    
  SET   @server=cast(serverproperty('servername') AS SYSNAME)   
  EXEC  sp_addserver @server=@server,@local='LOCAL'     
  END  
  ELSE  
    PRINT '实例名与主机名一致，无需修改！' ;

参考: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver15