如何处理SQL Server事务复制中的大事务操作

如何处理SQL Server事务复制中的大事务操作

事务复制的工作机制

事务复制是由 SQL Server 快照代理、日志读取器代理和分发代理实现的。快照代理准备快照文件（其中包含了已发布表和数据库对象的架构和数据），然后将这些文件存储在快照文件夹中，并在分发服务器中的分发数据库中记录同步作业。

日志读取器代理监视为事务复制配置的每个数据库的事务日志，并将标记为要复制的事务从事务日志复制到分发数据库中，分发数据库的作用相当于一个可靠的存储-转发队列。 分发代理将快照文件夹中的初始快照文件和分发数据库表中的事务复制到订阅服务器中。

在发布服务器中所做的增量更改根据分发代理的计划流向订阅服务器，分发代理可以连续运行以尽量减少滞后时间，也可以按预定的时间间隔运行。对于推送订阅，分发代理在分发服务器上运行；对于请求订阅，分发代理在订阅服务器上运行。该代理将事务从分发数据库移动到订阅服务器中。 如果订阅被标记为需要验证，则分发代理还要检查发布服务器和订阅服务器中的数据是否匹配。

大事务同步延时处理方法

在transactional replication, 经常会遇到数据同步延迟的情况。有时候这些延迟是由于在publication中执行了一个更新，例如update ta set col=? Where ?，这个更新包含巨大的数据量。在subscription端，这个更新会分解成多条命令（默认情况下每个数据行一个命令）应用到subscription上。 不得已的情况下，我们需要跳过这个大的事务，让replication继续运行下去。

现在介绍一下transactional replication的一些原理和具体的方法：

当publication database的article发生更新时， 会产生相应的日志，Log reader会读取这些日志信息，将他们写入到Distribution 数据库的msrepl_transactions和msrepl_commands中。

Msrepl_transactions中的每一条记录都有一个唯一标识xact_seqno，xact_seqno对应日志中的LSN。 所以可以通过xact_seqno推断出他们在publication database中的生成顺序，编号大的生成时间就晚，编号小的生成时间就早。

Distributionagent包含两个子进程，reader和writer。 Reader负责从Distribution 数据库中读取数据，Writer负责将reader读取的数据写入到订阅数据库。

Reader是通过sp_MSget_repl_commands来读取Distribution数据库中（读取Msrepl_transactions表和Msrepl_Commands表）的数据。

大致逻辑是：Reader读取subscription database的MSreplication_subscriptions表的transaction_timestamp列，获得更新的上一次LSN编号，然后读取分发数据库中LSN大于这个编号的数据。 Writer将读取到的数据写入订阅，并更新MSreplication_subscriptions表的transaction_timestamp列。然后Reader会继续用新的LSN来读取后续的数据，再传递给Writer，如此往复。

如果我们手工更新transaction_timestamp列,将这个值设置为当前正在执行的大事务的LSN，那么distribution agent就会不读取这个大事务，而是将其跳过了。

具体逻辑参见：

SQL Server复制系列3 – 存储过程sp_MSins_dboTableName_msrepl_ccs & sp_MSdel_dboTableName_msrepl_ccs的作用

SQL Server复制系列4 – Transactional replication中如何跳过一个事务

DBA的建议

为了最小化影响，建议使用复制的存储过程，将更新操作封装为一个独立事务，在订阅服务器上调用复制的存储过程，在本地执行批量更新。

在高并发的数据库做归档后的删除，为了避免业务影响，删除操作会循环分批删除，每批间等待一定时间。这里，我们也可以使用控制表来控制大事务分批操作。将控制逻辑和复制的存储过程结合，增加批次并减少执行时间。这个过程也可以工作得和非复制的更新一样好，几乎不会用实际的UPDATE替换EXEC SP操作。

具体脚本参见：

Large Updates on Replicated Tables

深入优化复制架构

对于多个发布者、多个发布、多个订阅的情况，我们可以从架构上来优化和扩展。将每个发布者独立一个distribution数据库，放到独立的服务器上，减轻分发代理的压力。对于订阅，不需要实时要求的，用请求订阅，尽量减少推送订阅的数量。

对于订阅数据库，可以配置为大容量模式，优化批量操作的日志写入。创建轻量的订阅数据库，减少不必要的索引和触发器。对于请求订阅，修改拉取间隔。增加日志备份的频率。配置高性能的配置文件。

复制优化参见：

15 SQL Server replication tips in 15 minutes