在transactional replication, 經(jīng)常會(huì)遇到數(shù)據(jù)同步延遲的情況。有時(shí)候這些延遲是由于在publication中執(zhí)行了一個(gè)更新，例如update ta set col=? Where ?，這個(gè)更新包含巨大的數(shù)據(jù)量。在subscription端，這個(gè)更新會(huì)分解成多條命令（默認(rèn)情況下每個(gè)數(shù)據(jù)行一個(gè)命令），應(yīng)用到subscription上。 不得已的情況下，我們需要跳過這個(gè)大的事務(wù)，讓replication繼續(xù)運(yùn)行下去。

現(xiàn)在介紹一下transactional replication的一些原理和具體的方法

當(dāng)publication database的article發(fā)生更新時(shí)， 會(huì)產(chǎn)生相應(yīng)的日志，Log reader會(huì)讀取這些日志信息，將他們寫入到Distribution 數(shù)據(jù)庫的msrepl_transactions和msrepl_commands中。 

Msrepl_transactions中的每一條記錄都有一個(gè)唯一標(biāo)識(shí)xact_seqno，xact_seqno對(duì)應(yīng)日志中的LSN。 所以可以通過xact_seqno推斷出他們?cè)趐ublication database中的生成順序，編號(hào)大的生成時(shí)間就晚，編號(hào)小的生成時(shí)間就早。

Distributionagent包含兩個(gè)子進(jìn)程，reader和writer。 Reader負(fù)責(zé)從Distribution 數(shù)據(jù)庫中讀取數(shù)據(jù)，Writer負(fù)責(zé)將reader讀取的數(shù)據(jù)寫入到訂閱數(shù)據(jù)庫.

reader是通過sp_MSget_repl_commands來讀取Distribution數(shù)據(jù)庫中（讀取Msrepl_transactions表和Msrepl_Commands表）的數(shù)據(jù)

下面是sp_MSget_repl_commands的參數(shù)定義

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 

) 

這個(gè)存儲(chǔ)過程有6個(gè)參數(shù)，在Transactional replication 中，只會(huì)使用前4個(gè)（并且第三個(gè)參數(shù)和第四個(gè)參數(shù)的值是固定不變的.分別為0和10000000）。下面是一個(gè)例子：

execsp_MSget_repl_commands 46,0x0010630F000002A900EA00000000,0,10000000

@agent_id表示Distributionagentid，每個(gè)訂閱都會(huì)有一個(gè)單獨(dú)的Distributionagent來處理數(shù)據(jù)。 帶入@agent_id后，就可以找到訂閱對(duì)應(yīng)的publication 和所有的article。

@last_xact_seqno 表示上一次傳遞到訂閱的LSN。

大致邏輯是：Reader讀取subscription database的MSreplication_subscriptions表的transaction_timestamp列，獲得更新的上一次LSN編號(hào)，然后讀取分發(fā)數(shù)據(jù)庫中LSN大于這個(gè)編號(hào)的數(shù)據(jù)。 Writer將讀取到的數(shù)據(jù)寫入訂閱，并更新MSreplication_subscriptions表的transaction_timestamp列。然后Reader會(huì)繼續(xù)用新的LSN來讀取后續(xù)的數(shù)據(jù)，再傳遞給Writer，如此往復(fù)。

如果我們手工更新transaction_timestamp列,將這個(gè)值設(shè)置為當(dāng)前正在執(zhí)行的大事務(wù)的LSN，那么distribution agent就會(huì)不讀取這個(gè)大事務(wù)，而是將其跳過了。

下面以一個(gè)實(shí)例演示一下

環(huán)境如下

Publisher: SQL108W2K8R21

Distributor: SQL108W2K8R22

Subscriber: SQL108W2K8R23

圖中高亮的publication中包含3個(gè)aritcles，ta,tb,tc

其中ta包含18,218,200萬數(shù)據(jù)，然后我們進(jìn)行了一下操作

在11：00進(jìn)行了更新語句，

update ta set c=-11

后續(xù)陸續(xù)對(duì)表ta,tb,tc執(zhí)行一些插入操作

insert tb values(0,0)

insert tc values(0,0)

之后我們啟動(dòng)replication monitor ，發(fā)現(xiàn)有很大的延遲，distribution agent一直在傳遞a）操作產(chǎn)生的數(shù)據(jù)

在subscription database中執(zhí)行下面的語句,得到當(dāng)前最新記錄的事務(wù)編號(hào)

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 

在我的環(huán)境中，事務(wù)編號(hào)為0x0000014900004E9A0004000000000000

返回到distribution database，執(zhí)行下面的語句，得到緊跟在大事務(wù)后面的事務(wù)編號(hào). 請(qǐng)將參數(shù)替換成您實(shí)際環(huán)境中的數(shù)據(jù)。（請(qǐng)注意，如果執(zhí)行下列語句遇到性能問題，請(qǐng)將參數(shù)直接替換成值）

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

在我的環(huán)境中，事務(wù)編號(hào)為0x0000018C000001000171

在subscription database中執(zhí)行下面的語句，跳過大的事務(wù)。請(qǐng)將參數(shù)替換成您實(shí)際環(huán)境中的數(shù)據(jù)

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 

執(zhí)行完成后開啟distribution agent job即可。

接下來您就會(huì)發(fā)現(xiàn)，事務(wù)已經(jīng)成功跳過，ta在訂閱端不會(huì)被更新，后續(xù)的更新會(huì)逐步傳遞到訂閱，延遲消失。

最新評(píng)論