当前位置: 代码迷 >> Sql Server >> Replication的角落旮旯(九)- sp_setsubscriptionxactseqno,赋予订阅活力的工具
  详细解决方案

Replication的角落旮旯(九)- sp_setsubscriptionxactseqno,赋予订阅活力的工具

热度:112   发布时间:2016-04-24 09:25:01.0
Replication的犄角旮旯(九)-- sp_setsubscriptionxactseqno,赋予订阅活力的工具

《Replication的犄角旮旯》系列导读

Replication的犄角旮旯(一)--变更订阅端表名的应用场景

Replication的犄角旮旯(二)--寻找订阅端丢失的记录

Replication的犄角旮旯(三)--聊聊@bitmap

Replication的犄角旮旯(四)--关于事务复制的监控

Replication的犄角旮旯(五)--关于复制identity列

Replication的犄角旮旯(六)-- 一个DDL引发的血案(上)(如何近似估算DDL操作进度)

Replication的犄角旮旯(七)-- 一个DDL引发的血案(下)(聊聊logreader的延迟)

Replication的犄角旮旯(八)-- 订阅与发布异构的问题

Replication的犄角旮旯(九)-- sp_setsubscriptionxactseqno,赋予订阅活力的工具

 

 

---------------------------------------华丽丽的分割线--------------------------------------------

前言:有人总是拿Mysql的Master\Slave和SQL Server的replication比较,说Mysql的复制有多么强大、多么灵活。作为SQLServer的死忠,也曾被replication各种 的黑盒搞得体无完肤。不过还好,我们还是能从MS流露出来的各种存储过程中,发现蛛丝马迹,结合我们的头脑风暴,来一场真真正正的革命……

sp_setsubscriptionxactseqno,第一次了解是在拜读前任DBR的blog《在SQL Server 2005/2008事务复制中如何跳过一个事务》时,而近期在处理一个复制异常事件时,忽然灵光闪现,既然可以向后跳过某些事务,是否可以前滚到之前的某个时间点再继续复制呢?本文将通过实际测试,继续玩复制

 

闲话少叙,书归正传……

 

关于跳过某些复制事务,在此不再赘述,详见《在SQL Server 2005/2008事务复制中如何跳过一个事务》;这里只说如何追溯到之前某个时间点;

关于sp_setsubscriptionxactseqno这个存储过程,详见MSDN:https://msdn.microsoft.com/zh-cn/library/ms188764.aspx

具体用法如下:

sp_setsubscriptionxactseqno [ @publisher = ] 'publisher'         , [ @publisher_db = ] 'publisher_db'         , [ @publication = ] 'publication'         , [ @xact_seqno = ] xact_seqno

其中@xact_seqno这个参数,如果指定当前事务(起始点)之后的某个事务(截止点),就可以跳过两个时间点之间的事务;但如果需要跳到当前事务之前的某个事务时,除了sp_setsubscriptionxactseqno这个存储过程外,还需要一个系统表来配合才能实现——MSsubscriptions,位于分发库(默认为distribution)中;

MSsubscriptions记录了每个订阅与发布项目的关系,详见MSDN:https://msdn.microsoft.com/zh-cn/library/ms188368.aspx

其中publisher_seqno表示该订阅创建时在发布服务器上的事务序列号subscription_seqno为快照事务序列号(非初始化订阅则与publisher_seqno一致);

在事务复制中,订阅端应用事务时,需要检测当前事务是否小于这两个参数,如果当前事务号小于上述两个列的值,则逻辑上判为不成立(当前执行的事务早于创建订阅时的事务,理论上不成立)。

因此,要想回跳到之前某个时间点的事务,需要手动更新相应的记录,至少保证publisher_seqno和subscription_seqno与你要回跳的那个事务号一致;

至此,我们目的达成,但这又有什么意义呢?当前时间点下的数据为什么要回跳到之前的某个事务呢?这就要说到前几天我们处理的一个案例;

 

先说一下我们的复制环境,以下图为例:

根节点为写库,承载主写业务,为避免单点故障,增加一灾备节点进行保护;

转发节点作为根节点的订阅以及末端节点的发布,只进行复制命令的转发工作,双转发进行冗余,避免单链路故障影响末端订阅;

末端订阅承载各类读业务,双链路各取部分节点做负载均衡;

 

看似健壮的架构,避免的单点问题。但仍有个隐患,当任意一个转发节点故障时,尽管可以随时删除复制链路(末端订阅有负载均衡保护,随时可以删除节点),但由于复制的基础数据过大,故障回复时,无论是快照还是备份初始化,时间成本都很高。怎么破?

此前出现了"转发节点02"因增加存储空间导致开机后出现逻辑页错误,致使该条链路失效,难道真的需要重新初始化才能恢复?(需要重建3个节点,转发节点02、末端订阅03、04)

或许我们有更好的办法可以避免初始化的过程;

以下为本地测试的情况:

 

思路:启用灾备节点作为转发节点的基础数据

难点:

1、灾备节点数据如何恢复到故障时完整衔接中断的复制事务?

2、如果能保证事务完美衔接但数据超前,如何处理redo部分的冲突,并使其正常分发到下级订阅?

 

解决方法:

1、利用现有灾备节点,可以快速实现数据超前于故障时间点;

2、通过sp_setsubscriptionxactseqno可以重置灾备节点的订阅事务号,使其从较早的事务进行redo;但同时需要修改[distribution].[dbo].[MSsubscriptions]中的publisher_seqnosubscription_seqno,使其生效;

3、由于灾备节点数据超前于下级订阅节点数据,需考虑对灾备节点及下级订阅节点中,订阅表存储过程的修正,实现自动redo的过程;

 

测试环境:

    根节点:BJYW-XIAOLEI\SQL01  testDB_A(SQL2012)

 转发节点:BJYW-XIAOLEI\SQL02  testDB_A(SQL2008R2)

 末端订阅:BJYW-XIAOLEI\SQL02  testDB_B(SQL2008R2)

 灾备节点:BJYW-XIAOLEI\SQL01  testDB_C(SQL2012)

 

测试过程:

1、创建测试表及测试数据

 1 USE [testDB_A] 2  3 GO 4  5 CREATE TABLE [dbo].[test_a]( 6  7 [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 8  9 [context] [varchar](100) NULL,10 11 PRIMARY KEY CLUSTERED12 13 (14 15 [id] ASC16 17 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]18 19 ) ON [PRIMARY]20 21 GO22 23 INSERT INTO dbo.test_a( context )VALUES  ( '00001' )24 25 GO 10
View Code

2、创建复制链路

      BJYW-XIAOLEI\SQL01  testDB_A  -->  BJYW-XIAOLEI\SQL02   testDB_A  -->  BJYW-XIAOLEI\SQL02  testDB_B      

      灾备:BJYW-XIAOLEI\SQL01  testDB_C(SQL2012)使用同样的测试脚本及数据;

      复制关系如下:

 

3、模拟故障

       在转发节点(BJYW-XIAOLEI\SQL02   testDB_A)删除一条数据后,在根节点上更新该数据

       转发节点:DELETE testDB_A.dbo.test_a WHERE id=10

       根节点UPDATE testDB_A.dbo.test_a SET context='delete' WHERE id=10

 

      检查msrepl_errors,发现报错

 

4、主写和灾备节点再插入10条数据,用于模拟故障发生后,灾备节点数据超前于末端订阅节点

 
1 INSERT INTO dbo.test_a( context )VALUES  ( '00002' )2 GO 10
View Code

 

5、创建主写节点到灾备节点的不初始化订阅关系,并停止分发代理作业;同时创建灾备节点到末端订阅的不初始化复制链路;

      SQL01testDB_A库新建到灾备节点(testDB_C)的不初始化订阅

 

      SQL01testDB_C库新建到末端订阅(testDB_B)的不初始化订阅

 

6、修改灾备节点的初始订阅事务号为故障点的事务号

      找到最早出错的事务号(见第3步):0x0000004300000040000300000000

 

 1 USE testDB_C 2 GO 3  4 sp_setsubscriptionxactseqno  @publisher =  'BJYW-XIAOLEI\SQL01' 5         ,  @publisher_db =  'testdb_a' 6         ,  @publication =  'SQL01_A' 7         ,  @xact_seqno =  0x0000004300000040000300000000 8  9 USE distribution10 GO11 12 SELECT SP.publisher_seqno,SP.subscription_seqno,* FROM [distribution].[dbo].[MSsubscriptions] SP JOIN dbo.MSpublications PB ON SP.publisher_id=PB.publisher_id13 AND SP.publisher_db=PB.publisher_db AND SP.publication_id=PB.publication_id14 WHERE PB.publisher_db='testDB_A' AND pb.publication='SQL01_A' AND sp.subscriber_db='testDB_C'15 16  17 18 UPDATE SP19 SET SP.publisher_seqno= 0x0000004300000040000300000000 ,SP.subscription_seqno=0x000000430000004000030000000020 FROM [distribution].[dbo].[MSsubscriptions] SP JOIN dbo.MSpublications PB ON SP.publisher_id=PB.publisher_id21 AND SP.publisher_db=PB.publisher_db AND SP.publication_id=PB.publication_id22 WHERE PB.publisher_db='testDB_A' AND pb.publication='SQL01_A' AND sp.subscriber_db='testDB_C'
View Code

        修改后的记录

  7、修改灾备节点的订阅存储过程

      a)  insert:判断主键是否存在,如存在,需删除后再insert

      b)  update:对非主要键值,建议先set col=null,再set col='',最后再执行正确的update

      c)  delete :暂不处理,只记录主键信息,后续处理;

      Inster存储过程

 

       Update存储过程

 8、修改末端订阅的订阅存储过程

      a)  insert:判断主键是否存在,如存在,需删除后再insert

      b)  update:记录不存在主键的记录,后续从log表中补足;

      c)  delete :注释判断if @@rowcount=0的部分,不报错即可;

 

9、为方便监控,创建相应的trigger抓取实际操作情况;

 1 create TRIGGER [dbo].[tri_del_test_a] 2    ON  [dbo].[test_a] 3    AFTER DELETE 4 AS 5 BEGIN 6 SET NOCOUNT ON; 7 INSERT INTO monitor.dbo.trigger_monitor_byxl(tbname,t_type,t_VALUE,checktime) 8 SELECT 'test_a','delete','id= '+CAST(ID AS VARCHAR(10)),GETDATE() FROM DELETED 9 END10 11   12 13 create TRIGGER [dbo].[tri_upd_test_a]14    ON  [dbo].[test_a]15    AFTER UPDATE16 AS17 BEGIN18 SET NOCOUNT ON;19 INSERT INTO monitor.dbo.trigger_monitor_byxl(tbname,t_type,t_VALUE,checktime)20 SELECT 'test_a','update','id= '+CAST(ID AS VARCHAR(10))+'- context='+context,GETDATE() FROM DELETED21 END
View Code

 

10、启用灾备节点对应的分发代理;

        由于之前中间节点采用delete的方式删除了数据,此处为了末端订阅恢复正常,手动insert原记录;

        实际生产环境中,可以根据故障出现时暂停的事务,手动处理一个事务;后续事务正常应用到末端订阅

 

 注意:

由于在将灾备节点转为订阅节点过程中,创建订阅存储过程的命令会记录到msrepl_commands中,因此,在跨过创建订阅的事务时,会将存储过程重置为初始状态;

另一方面,由于写库不停机,在将灾备节点转为订阅节点时至手动停止分发代理过程中,可能存在少量数据写入,因而在存储过程重置后,由于数据少量超前,会导致主键冲突(insert)的问题,而update可能丢失(set值前后一样的情况,不会出现脏数据,则不会记录到复制命令中);

 

建议:在将灾备节点转为订阅节点时,代理计划部分改为“按需执行”

6楼Paddy_Duck
围观~
5楼博客园团队
您好,文中部分图片不能显示,麻烦上传一下图片。
4楼CareySon
学习围观,首席DBR:-)
3楼张骞
前排战队
2楼maxhema
学习围观
1楼Lvanhades666
学习围观
  相关解决方案