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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介:

事务复制的工作机制


事务复制是由 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














本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1905168,如需转载请自行联系原作者



相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
4月前
|
SQL 数据库 数据安全/隐私保护
SQL Server数据库Owner导致事务复制log reader job无法启动的解决办法
【8月更文挑战第14天】解决SQL Server事务复制Log Reader作业因数据库所有者问题无法启动的方法:首先验证数据库所有者是否有效并具足够权限;若非,使用`ALTER AUTHORIZATION`更改为有效登录名。其次,确认Log Reader使用的登录名拥有读取事务日志所需的角色权限。还需检查复制配置是否准确无误,并验证Log Reader代理的连接信息及参数。重启SQL Server Agent服务或手动启动Log Reader作业亦可能解决问题。最后,审查SQL Server错误日志及Windows事件查看器以获取更多线索。
|
1月前
|
SQL Oracle 关系型数据库
[SQL]事务
本文介绍了事务处理的基本概念,包括事务的四大特性(原子性、一致性、隔离性、持久性)及生命周期。文章还详细解释了事务的保存点、四种事务隔离级别及其异常读现象,并提供了设置事务隔离级别的方法。最后,作者建议读者深入学习相关理论以更好地理解事务隔离级别。
48 0
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 存储 监控
SQLServer事务复制延迟优化之并行(多线程)复制
【9月更文挑战第12天】在SQL Server中,事务复制延迟会影响数据同步性。并行复制可通过多线程处理优化这一问题,提高复制效率。主要优化方法包括:配置分发代理参数、优化网络带宽、调整系统资源、优化数据库设计及定期监控维护。合理实施这些措施可提升数据同步的及时性和可靠性。
|
3月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
168 0
|
5月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
135 13
|
5月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 监控 供应链
|
5月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
73 6
|
5月前
|
SQL 分布式计算 MaxCompute
SQL开发问题之对于ODPS中的UNION操作,执行计划的问题如何解决
SQL开发问题之对于ODPS中的UNION操作,执行计划的问题如何解决

热门文章

最新文章