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 nameData typeDescriptionagent_idintThe ID of the Distribution Agent.runstatusintThe Running status:

1 = Start.

2 = Succeed.

3 = In progress.

4 = Idle.

5 = Retry.

6 = Fail.start_timedatetimeThe time to begin execution of the job.timedatetimeThe time the message is logged.durationintThe duration, in seconds, of the message session.commentsnvarchar(4000)The message text.xact_seqnovarbinary(16)The last processed transaction sequence number.current_delivery_ratefloatThe average number of commands delivered per second since the last history entry.current_delivery_latencyintThe latency between the command entering the distribution database and being applied to the Subscriber since the last history entry. In milliseconds.delivered_transactionsintThe total number of transactions delivered in the session.delivered_commandsintThe total number of commands delivered in the session.average_commandsintThe average number of commands delivered in the session.delivery_ratefloatThe average delivered commands per second.delivery_latencyintThe latency between the command entering the distribution database and being applied to the Subscriber. In milliseconds.total_delivered_commandsbigintThe total number of commands delivered since the subscription was created.error_idintThe ID of the error in the MSrepl_error system table.updateable_rowbitSet to 1 if the history row can be overwritten.timestamptimestampThe timestamp column of this table. 
目标端：订阅端数据库中的订阅表MSreplication_subscriptions
Column nameData typeDescriptionpublishersysnameThe name of the Publisher.publisher_dbsysnameThe name of the Publisher database.publicationsysnameThe name of the publication.independent_agentbitIndicates whether there is a stand-alone Distribution Agent for this publication.subscription_typeintThe type of subscription:

0 = Push.

1 = Pull.

2 = Anonymous.distribution_agentsysnameThe name of the Distribution Agent.TimesmalldatetimeThe time of the last update by Distribution Agent.descriptionnvarchar(255)The description of the subscription.transaction_timestampvarbinary(16)Internal-use only.update_modetinyintThe type of update.agent_idbinary(16)The ID of the agent.subscription_guidbinary(16)The global identifier for the version of the subscription on the publication.subidbinary(16)The global identifier for an anonymous subscription.immediate_syncbitIndicates whether synchronization files are created or re-created each time the Snapshot Agent runs. 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！