《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_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
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
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
'
修改后的记录
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
10 、启用灾备节点对应的分发代理；
由于之前中间节点采用 delete 的方式删除了数据，此处为了末端订阅恢复正常，手动 insert 原记录；
实际生产环境中，可以根据故障出现时暂停的事务，手动处理一个事务；后续事务正常应用到末端订阅
注意：
由于在将灾备节点转为订阅节点过程中，创建订阅存储过程的命令会记录到 msrepl_commands 中，因此，在跨过创建订阅的事务时，会将存储过程重置为初始状态；
另一方面，由于写库不停机，在将灾备节点转为订阅节点时至手动停止分发代理过程中，可能存在少量数据写入，因而在存储过程重置后，由于数据少量超前，会导致主键冲突（ insert ）的问题，而 update 可能丢失（ set 值前后一样的情况，不会出现脏数据，则不会记录到复制命令中）；
建议：在将灾备节点转为订阅节点时，代理计划部分改为“按需执行”
