Replication的犄角旮旯（九）-- sp_setsubscriptionxactseqno，赋予订阅活力的工具

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

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

前言：有人总是拿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_seqno、subscription_seqno，使其生效；

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

测试环境：

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

转发节点：BJYW-XIAOLEI\SQL02 testDB_A库(SQL2008 R2)

末端订阅：BJYW-XIAOLEI\SQL02 testDB_B库(SQL2008 R2)

灾备节点：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 CLUSTERED
12 
13 (
14 
15 [id] ASC
16 
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 GO
22 
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、创建主写节点到灾备节点的不初始化订阅关系，并停止分发代理作业；同时创建灾备节点到末端订阅的不初始化复制链路；

SQL01的testDB_A库新建到灾备节点（testDB_C）的不初始化订阅

SQL01的testDB_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 distribution
10 GO
11 
12 SELECT SP.publisher_seqno,SP.subscription_seqno,* FROM [distribution].[dbo].[MSsubscriptions] SP JOIN dbo.MSpublications PB ON SP.publisher_id=PB.publisher_id
13 AND SP.publisher_db=PB.publisher_db AND SP.publication_id=PB.publication_id
14 WHERE PB.publisher_db='testDB_A' AND pb.publication='SQL01_A' AND sp.subscriber_db='testDB_C'
15 
16  
17 
18 UPDATE SP
19 SET SP.publisher_seqno= 0x0000004300000040000300000000 ,SP.subscription_seqno=0x0000004300000040000300000000
20 FROM [distribution].[dbo].[MSsubscriptions] SP JOIN dbo.MSpublications PB ON SP.publisher_id=PB.publisher_id
21 AND SP.publisher_db=PB.publisher_db AND SP.publication_id=PB.publication_id
22 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 END
10 
11   
12 
13 create TRIGGER [dbo].[tri_upd_test_a]
14    ON  [dbo].[test_a]
15    AFTER UPDATE
16 AS
17 BEGIN
18 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 DELETED
21 END

View Code

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

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

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

注意：

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

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

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

posted @ 2015-02-04 00:41 我是大菠萝 Views(1770) Comments(9) Edit 收藏举报

刷新页面返回顶部

我是大菠萝的博客园

Replication的犄角旮旯（九）-- sp_setsubscriptionxactseqno，赋予订阅活力的工具

公告