在逻辑复制中，正如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. |
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。