SQL Server Always On高可用集群下不遗漏记录的归档顺序读取方案咨询

方案1：CDC（变更数据捕获）+ LSN（日志序列号） #

这是目前该场景下最推荐的标准实现，适配所有增量同步、归档类需求：

• LSN是SQL Server事务日志的全局唯一有序标识，随事务提交单调递增，在AlwaysOn集群所有副本间完全一致，不存在回退、乱序、跳号问题，完全规避之前主键、RowVersion方案的缺陷
• CDC是SQL Server原生功能，开启后会自动记录表的所有插入、更新、删除操作，生成的变更表默认基于LSN排序，查询效率远高于在原表上基于RowVersion过滤
• 处理流程只需记录上次处理完成的最大LSN，下次查询直接读取大于该LSN的所有变更记录即可，无遗漏风险，额外性能开销通常低于5%

方案2：配置跨节点一致的SEQUENCE对象 #

如果不想开启CDC，可使用SEQUENCE替代自增主键，根据集群架构调整配置即可：

• 若为常规单主AlwaysOn集群：直接创建序列时指定NO CACHE参数，关闭序列预分配缓存，即可完全避免故障转移导致的跳号问题，生成的ID永远单调递增
• 若为多主分布式AG集群：根据可写副本数量设置序列步长，为每个节点分配独立的起始值，例如3个可写副本时，步长设为3，节点1起始值为1、节点2为2、节点3为3，即可保证所有节点生成的ID全局唯一、不会出现新ID小于旧ID的问题
• 序列支持直接作为表字段的默认值，改造成本远低于其他方案

方案3：插入时间戳+节点标识组合位点 #

如果业务逻辑允许，可在原表新增位点字段实现，改造最简单：

• 新增insert_time DATETIME2(7) DEFAULT GETUTCDATE()字段，同时新增node_id TINYINT DEFAULT <当前节点标识>字段，两个字段联合建索引
• 处理时以上次处理的最大insert_time + node_id作为位点，过滤新增记录，只要集群节点做了NTP时钟同步，不会出现乱序遗漏问题
• 该方案不需要调整原有主键逻辑，适配性最高

原有方案的问题说明 #

• 自增主键跳号确实是identity reservation机制导致，SQL Server默认会预分配一批自增值缓存在内存中，故障转移时未使用的缓存值会直接丢弃，产生跳号。常规单主AG场景下不会出现新主键小于旧主键的问题，该风险仅存在于多主分布式AG场景
• RowVersion方案性能差的核心原因是需要在原表上做范围过滤，除非为RowVersion建覆盖所有查询字段的索引，否则必然产生大量随机IO，远不如CDC增量读取的性能表现

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