在transactional replication, 經常會遇到數據同步延遲的情況。有時候這些延遲是由於在publication中執行了一個更新,例如update ta set col=? Where ?,這個更新包含巨大的數據量。在subscription端,這個更新會分解成多條命令(默認情況下每個數據行一個命令),應用到subscription上。 不得已的情況下,我們需要跳過這個大的事務,讓replication繼續運行下去。
現在介紹一下transactional replication的一些原理和具體的方法
當publication database的article發生更新時, 會產生相應的日志,Log reader會讀取這些日志信息,將他們寫入到Distribution 數據庫的msrepl_transactions和msrepl_commands中。
Msrepl_transactions中的每一條記錄都有一個唯一標識xact_seqno,xact_seqno對應日志中的LSN。 所以可以通過xact_seqno推斷出他們在publication database中的生成順序,編號大的生成時間就晚,編號小的生成時間就早。
Distributionagent包含兩個子進程,reader和writer。 Reader負責從Distribution 數據庫中讀取數據,Writer負責將reader讀取的數據寫入到訂閱數據庫.
reader是通過sp_MSget_repl_commands來讀取Distribution數據庫中(讀取Msrepl_transactions表和Msrepl_Commands表)的數據
下面是sp_MSget_repl_commands的參數定義
CREATE PROCEDURE sys.sp_MSget_repl_commands ( @agent_id int, @last_xact_seqno varbinary(16), @get_count tinyint = 0, -- 0 = no count, 1 = cmd and tran (legacy), 2 = cmd only @compatibility_level int = 7000000, @subdb_version int = 0, @read_query_size int = -1 )
這個存儲過程有6個參數,在Transactional replication 中,只會使用前4個(並且第三個參數和第四個參數的值是固定不變的.分別為0和10000000)。下面是一個例子:
execsp_MSget_repl_commands 46,0x0010630F000002A900EA00000000,0,10000000
@agent_id表示Distributionagentid,每個訂閱都會有一個單獨的Distributionagent來處理數據。 帶入@agent_id後,就可以找到訂閱對應的publication 和所有的article。
@last_xact_seqno 表示上一次傳遞到訂閱的LSN。
大致邏輯是:Reader讀取subscription database的MSreplication_subscriptions表的transaction_timestamp列,獲得更新的上一次LSN編號,然後讀取分發數據庫中LSN大於這個編號的數據。 Writer將讀取到的數據寫入訂閱,並更新MSreplication_subscriptions表的transaction_timestamp列。然後Reader會繼續用新的LSN來讀取後續的數據,再傳遞給Writer,如此往復。
如果我們手工更新transaction_timestamp列,將這個值設置為當前正在執行的大事務的LSN,那麼distribution agent就會不讀取這個大事務,而是將其跳過了。
下面以一個實例演示一下
環境如下
Publisher: SQL108W2K8R21
Distributor: SQL108W2K8R22
Subscriber: SQL108W2K8R23
圖中高亮的publication中包含3個aritcles,ta,tb,tc
其中ta包含18,218,200萬數據,然後我們進行了一下操作
在11:00進行了更新語句,
update ta set c=-11
後續陸續對表ta,tb,tc執行一些插入操作
insert tb values(0,0)
insert tc values(0,0)
之後我們啟動replication monitor ,發現有很大的延遲,distribution agent一直在傳遞a)操作產生的數據
在subscription database中執行下面的語句,得到當前最新記錄的事務編號
declare @publisher sysname declare @publicationDB sysname declare @publication sysname set @publisher='SQL108W2K8R22' set @publicationDB='pubdb' set @publication='pubdbtest2' select transaction_timestamp From MSreplication_subscriptions where publisher=@publisher and publisher_db=@publicationDB and publication=@publication
在我的環境中,事務編號為0x0000014900004E9A0004000000000000
返回到distribution database,執行下面的語句,得到緊跟在大事務後面的事務編號. 請將參數替換成您實際環境中的數據。(請注意,如果執行下列語句遇到性能問題,請將參數直接替換成值)
declare @publisher sysname declare @publicationDB sysname declare @publication sysname declare @transaction_timestamp [varbinary](16) set @publisher='SQL108W2K8R21' set @publicationDB='publicationdb2' set @publication='pubtest' set @transaction_timestamp= 0x0000014900004E9A0004000000000000 select top 1 xact_seqno from MSrepl_commands with (nolock) where xact_seqno>@transaction_timestamp and article_id in ( select article_id From MSarticles a inner join MSpublications p on a.publication_id=p.publication_id and a.publisher_id=p.publisher_id and a.publisher_db=p.publisher_db inner join sys.servers s on s.server_id=p.publisher_id where p.publication=@publication and p.publisher_db=@publicationDB and s.name=@publisher ) and publisher_database_id =( select id From MSpublisher_databases pd inner join MSpublications p on pd.publisher_id=p.publisher_id inner join sys.servers s on pd.publisher_id=s.server_id and pd.publisher_db=p.publisher_db where s.name=@publisher and p.publication=@publication and pd.publisher_db=@publicationDB ) Order by xact_seqno
在我的環境中,事務編號為0x0000018C000001000171
在subscription database中執行下面的語句,跳過大的事務。請將參數替換成您實際環境中的數據
declare @publisher sysname declare @publicationDB sysname declare @publication sysname declare @transaction_timestamp [varbinary](16) set @publisher='SQL108W2K8R22' set @publicationDB='pubdb' set @publication='pubdbtest2' set @transaction_timestamp= 0x0000018C000001000171 update MSreplication_subscriptions set transaction_timestamp=@transaction_timestamp where publisher=@publisher and publisher_db=@publicationDB and publication=@publication
執行完成後開啟distribution agent job即可。
接下來您就會發現,事務已經成功跳過,ta在訂閱端不會被更新,後續的更新會逐步傳遞到訂閱,延遲消失。