无需修改源库的MS SQL到Azure SQL ETL增量变更捕获方案咨询

核心思路 #

无需每日拉取源库全量数据，仅在源侧查询轻量的主键+行哈希值，与预先存储的基准哈希做对比定位变更行，仅同步有变更的行数据，全程不涉及源库的任何结构、权限修改，同时大幅降低数据传输、计算成本。

具体执行步骤 #

• 首次全量同步时，在Azure Data Lake中生成对应源表的基准哈希表，仅保留两个字段：源表主键、全字段哈希值。哈希值直接在源库查询时通过SQL原生函数生成，参考语法：

  SELECT 
    主键字段,
    HASHBYTES('SHA2_256', CONCAT_WS('|', ISNULL(字段1, '^NULL^'), ISNULL(字段2, '^NULL^'), ..., ISNULL(字段N, '^NULL^'))) AS row_hash
  FROM 源表
  

  若源库为SQL Server 2016以下版本，可将哈希算法替换为MD5。
• 每日同步任务启动后，先仅查询源库所有行的主键+哈希值存入Azure Data Lake的临时哈希区，该查询仅返回两个字段，数据量远小于全表，对源库压力极低。
• 通过ADF数据流关联对比临时哈希集与基准哈希表，得到三类变更数据：
  • 新增数据：主键仅出现在临时哈希集中的行
  • 更新数据：主键同时存在但哈希值不一致的行
  • 删除数据：主键仅出现在基准哈希表中的行（按需开启删除同步逻辑）
• 仅根据新增、更新的主键列表，去源库查询对应行的全量字段数据，后续的转换、加载至Azure SQL的流程保持原有逻辑不变。
• 用当日临时哈希集覆盖基准哈希表，供次日同步对比使用。

优化方案（适用于超大数据量表） #

若源表单表数据量超过千万级，可结合现有插入时间字段进一步降低压力：

• 预先确认业务更新周期，比如历史数据仅近30天会被修改，每日哈希对比时仅查询源库插入时间 >= 当日日期-30天的行参与对比，更早的无变更历史数据无需重复计算哈希。

注意事项 #

• 哈希计算时必须对所有字段的NULL值做统一占位符替换，避免因NULL值的计算特性导致哈希结果异常。
• 源表字段发生增删改调整时，需同步更新哈希计算的拼接字段列表，避免哈希对比失效。

方案优势 #

• 完全符合源库无修改要求：仅用到SQL Server原生函数做查询，无需开启CDC、创建触发器、新增字段，无任何源库侧变更。
• 成本大幅降低：日常同步仅需传输、处理占全表占比极低的变更数据，相比全量同步方案，存储、计算、传输成本可降低90%以上。
• 完全适配现有技术栈：所有流程均在MS SQL、ADF、Azure Data Lake、Azure SQL体系内实现，无需引入额外组件。

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