1. 查看庫表是否啟動(dòng) CDC

   -- 查看數(shù)據(jù)庫是否啟用cdc
   SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
   
   -- 查看當(dāng)前數(shù)據(jù)庫表是否啟用cdc
   SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;
   

2. 數(shù)據(jù)庫啟用和禁用 CDC

   -- 對當(dāng)前數(shù)據(jù)庫啟用 CDC
   USE MyDB 
   GO
   EXECUTE sys.sp_cdc_enable_db;
   GO
   -- 對當(dāng)前數(shù)據(jù)庫禁用 CDC
   USE MyDB  
   GO  
   EXEC sys.sp_cdc_disable_db  
   GO  
   

3. 數(shù)據(jù)庫表啟用和禁用 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. 開啟授權(quán)

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

2. 需開啟 SQL Server Agent 服務(wù)

問題及解決辦法:
如無法執(zhí)行啟用或禁用表的 CDC，查看主機(jī)名的是否一致。

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

如不一致,執(zhí)行下面語句:

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 '實(shí)例名與主機(jī)名一致，無需修改！' ;

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