设为首页 收藏本站
云服务器等爆品抢先购,低至4.2元/月
查看: 1643|回复: 0

[经验分享] 如何处理SQL Server事务复制中的大事务操作

[复制链接]
累计签到:1 天
连续签到:1 天
发表于 2017-3-13 14:25:49 | 显示全部楼层 |阅读模式
如何处理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




运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-351060-1-1.html 上篇帖子: SQL必知必会(第4版)(中文文字版)下载 下篇帖子: SQL Server数据库镜像基于可用性组故障转移 如何
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表