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. |
浙公網安備 33010602011771號