SQL Server只读副本中变更数据捕获(CDC)的最佳实践问询

核心原则 #

不要直接在只读副本上启用CDC，这会破坏副本的只读状态，引发LSN序列不匹配、数据一致性等问题。以下是经过验证的最佳实践方案：

方案一：复用源库CDC数据同步至副本（推荐） #

• 源库已启用CDC，会自动生成捕获表、元数据表以及cdc.fn_cdc_get_net_changes_dbo_customers这类净变更函数。
• 通过ETL工具（如SSIS）或自定义脚本，定期从源库的CDC函数提取增量变更，同步到副本的对应分析表中。
• 优势：完全复用源库的LSN序列与CDC元数据，确保变更数据的一致性，避免重复或缺失。

方案二：基于Always On可用性组继承CDC配置（限特定架构） #

如果你的副本是Always On可用性组的辅助节点：

• 将辅助副本的数据库设置为可读模式（而非严格只读）。
• 主库启用CDC后，辅助库会自动同步所有CDC系统表（捕获表、元数据表）。
• 在辅助副本上启动SQL Server代理，手动创建CDC清理作业（源库的清理作业不会自动同步）。
• 此时可直接在辅助副本上使用CDC函数，因为LSN序列与源库完全一致，变更数据通过日志同步而来，并非副本本地捕获。

关于直接启用副本CDC的风险 #

• 操作不可行：只读副本不允许DDL操作，启用CDC的sys.sp_cdc_enable_db会直接报错，强行修改只读状态会破坏副本的同步关系。
• LSN不匹配问题：即便强行启用，副本的CDC会从启用时刻开始捕获本地同步来的变更，但本地LSN序列与源库完全独立，会导致：
  • 变更数据重复、缺失或顺序混乱；
  • 净变更函数无法正确计算增量，因为元数据与源库脱节。
• 因此忽略源库CDC元数据仅使用副本捕获内容的做法完全不合理，会彻底破坏增量加载的可靠性。

额外建议 #

• 针对ETL分析场景，建议在副本上创建独立的增量变更表，同步源库CDC数据后再进行分析，避免直接访问源库CDC表影响业务性能。
• 定期清理源库与副本的CDC历史数据，防止存储空间过度占用。

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