SqlServer 事务复制（transaction replication）的复制位点信息

在逻辑复制中，正如MySQL的show slave status，或者postgresql的逻辑复制pg_stat_replication的sent_lsn，来观察复制进度的坐标位点，其复制进度坐标位置都存储在复制的源（source）端。

SqlServer的事务复制则有一些不一样，在发布端和订阅端分别有一个记录复制信息的系统表，1，在源端，有一个MSdistribution_history系统表存储了复制相关的信息，包括复制的事务号transaction sequence number，复制延迟，复制命令个数等等一些列复制相关的信息，另外有一个时间戳字段，这里含义不明。
2，在目标端，MSreplication_subscriptions，存储了源端的一些信息，其中有一个transaction_timestamp字段，该字段才是真正的复制坐标位点信息，也就意味着，如果订阅节点（从节点）断开后重连，会用这个字段作为起始位置重新开始复制。微软官方并没有明确说明该字段的作用，只是说Internal-use only.

如何证明上述复制坐标位点存在于订阅的目标端而不是发布的服务端？实物分发的数据尚未被清理之前（发布端的distribution库中），笔者尝试过，可以尝试从不同的备份分别恢复订阅库，订阅库总是可以以备份时刻的位点，精确地从分发库获取差异数据。

 

源端：分发数据库的分发历史表MSdistribution_history 

Column name	Data type	Description
agent_id	int	The ID of the Distribution Agent.
runstatus	int	The Running status: 1 = Start. 2 = Succeed. 3 = In progress. 4 = Idle. 5 = Retry. 6 = Fail.
start_time	datetime	The time to begin execution of the job.
time	datetime	The time the message is logged.
duration	int	The duration, in seconds, of the message session.
comments	nvarchar(4000)	The message text.
xact_seqno	varbinary(16)	The last processed transaction sequence number.
current_delivery_rate	float	The average number of commands delivered per second since the last history entry.
current_delivery_latency	int	The latency between the command entering the distribution database and being applied to the Subscriber since the last history entry. In milliseconds.
delivered_transactions	int	The total number of transactions delivered in the session.
delivered_commands	int	The total number of commands delivered in the session.
average_commands	int	The average number of commands delivered in the session.
delivery_rate	float	The average delivered commands per second.
delivery_latency	int	The latency between the command entering the distribution database and being applied to the Subscriber. In milliseconds.
total_delivered_commands	bigint	The total number of commands delivered since the subscription was created.
error_id	int	The ID of the error in the MSrepl_error system table.
updateable_row	bit	Set to 1 if the history row can be overwritten.
timestamp	timestamp	The timestamp column of this table.

 

目标端：订阅端数据库中的订阅表MSreplication_subscriptions

Column name	Data type	Description
publisher	sysname	The name of the Publisher.
publisher_db	sysname	The name of the Publisher database.
publication	sysname	The name of the publication.
independent_agent	bit	Indicates whether there is a stand-alone Distribution Agent for this publication.
subscription_type	int	The type of subscription: 0 = Push. 1 = Pull. 2 = Anonymous.
distribution_agent	sysname	The name of the Distribution Agent.
Time	smalldatetime	The time of the last update by Distribution Agent.
description	nvarchar(255)	The description of the subscription.
transaction_timestamp	varbinary(16)	Internal-use only.
update_mode	tinyint	The type of update.
agent_id	binary(16)	The ID of the agent.
subscription_guid	binary(16)	The global identifier for the version of the subscription on the publication.
subid	binary(16)	The global identifier for an anonymous subscription.
immediate_sync	bit	Indicates whether synchronization files are created or re-created each time the Snapshot Agent runs.