SQL Server中CDC与事务复制冲突及替代方案咨询

当SQL Server数据库已启用事务复制时，CDC无法创建独立的捕获作业，需复用复制的日志读取代理。若需保留复制同时实现类似CDC的变更捕获需求，可采用以下方案：

1. 复用事务复制的日志读取代理实现CDC功能 #

这是官方推荐的兼容方案，无需额外创建CDC捕获作业，直接让复制的日志读取代理同时处理复制和CDC的变更捕获：

• 确保事务复制已正常运行
• 执行T-SQL启用数据库级CDC：

  EXEC sys.sp_cdc_enable_db;
  

• 对目标表启用CDC（指定schema、表名，若无需权限控制可设@role_name = NULL）：

  EXEC sys.sp_cdc_enable_table 
      @source_schema = N'dbo', 
      @source_name = N'YourTableName', 
      @role_name = NULL;
  

• 此时日志读取代理会自动将捕获的变更同时推送给复制订阅和CDC的变更表，无需额外配置捕获作业。

2. 自定义变更捕获触发器 #

若需要完全自定义变更日志的结构和内容，可通过触发器实现变更捕获：

• 创建自定义变更日志表存储变更信息：

  CREATE TABLE dbo.ChangeLog (
      LogId INT IDENTITY(1,1) PRIMARY KEY,
      TableName NVARCHAR(128) NOT NULL,
      ChangeType NVARCHAR(10) NOT NULL,
      ChangeTime DATETIME2 NOT NULL DEFAULT GETDATE(),
      ChangedData NVARCHAR(MAX) NOT NULL
  );
  

• 为目标表创建触发器，将变更写入日志表：

  CREATE TRIGGER trg_YourTableName_ChangeCapture
  ON dbo.YourTableName
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      SET NOCOUNT ON;
  
      INSERT INTO dbo.ChangeLog(TableName, ChangeType, ChangedData)
      SELECT 
          'YourTableName',
          CASE 
              WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'UPDATE'
              WHEN EXISTS(SELECT * FROM inserted) THEN 'INSERT'
              ELSE 'DELETE' 
          END,
          (SELECT * FROM inserted FOR JSON AUTO) -- 用JSON存储变更数据，也可改用XML
      FROM inserted
      FULL OUTER JOIN deleted ON inserted.Id = deleted.Id;
  END;
  

注意：触发器会带来一定性能开销，高并发场景需谨慎测试。

3. 使用轻量级变更跟踪(Change Tracking) #

若仅需检测哪些行发生了变更，无需完整的历史变更数据，可使用SQL Server内置的变更跟踪功能：

• 启用数据库级变更跟踪：

  ALTER DATABASE YourDatabase 
  SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
  

• 对目标表启用变更跟踪（可选开启列变更跟踪）：

  ALTER TABLE dbo.YourTableName 
  ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
  

• 查询变更数据时，使用CHANGETABLE函数获取指定版本后的变更：

  DECLARE @last_sync_version BIGINT;
  -- 获取上次同步的版本（首次同步可设为0）
  SET @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION();
  
  SELECT *
  FROM CHANGETABLE(CHANGES dbo.YourTableName, @last_sync_version) AS CT
  JOIN dbo.YourTableName AS T ON CT.Id = T.Id;
  

内容的提问来源于stack exchange，提问作者roipgca