如何處理SQL Server事務複製中的大事務操作

標籤：事務複製   distribution   transactional replication   

如何處理SQL Server事務複製中的大事務操作

事務複製的工作機制

事務複製是由 SQL Server 快照代理、記錄讀取器代理程式和分發代理實現的。快照代理準備快照檔案（其中包含了發行表和資料庫物件的架構和資料），然後將這些檔案儲存體在快照檔案夾中，並在散發者中的散發資料庫中記錄同步作業。

記錄讀取器代理程式監視為事務複製配置的每個資料庫的交易記錄，並將標記為要複製的事務從交易記錄複製到散發資料庫中，散發資料庫的作用相當於一個可靠的儲存-轉寄隊列。 分發代理將快照檔案夾中的初始快照集檔案和散發資料庫表中的事務複製到訂閱伺服器中。

在發行伺服器中所做的增量更改根據分發代理的計劃流向訂閱伺服器，分發代理可以連續運行以盡量減少延隔時間，也可以按預定的時間間隔運行。對於發送訂閱，分發代理在散發者上運行；對於提取訂閱，分發代理在訂閱伺服器上運行。該代理將事務從散發資料庫移動到訂閱伺服器中。 如果訂閱被標記為需要驗證，則分發代理還要檢查發行伺服器和訂閱伺服器中的資料是否匹配。

大事務同步延時處理方法

在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表）的資料。

大致邏輯是：Reader讀取subscription database的MSreplication_subscriptions表的transaction_timestamp列，獲得更新的上一次LSN編號，然後讀取散發資料庫中LSN大於這個編號的資料。 Writer將讀取到的資料寫入訂閱，並更新MSreplication_subscriptions表的transaction_timestamp列。然後Reader會繼續用新的LSN來讀取後續的資料，再傳遞給Writer，如此往複。

如果我們手工更新transaction_timestamp列,將這個值設定為當前正在執行的大事務的LSN，那麼distribution agent就會不讀取這個大事務，而是將其跳過了。

具體邏輯參見：

SQL Server複製系列3 – 預存程序sp_MSins_dboTableName_msrepl_ccs & sp_MSdel_dboTableName_msrepl_ccs的作用

SQL Server複製系列4 – Transactional replication中如何跳過一個事務

DBA的建議

為了最小化影響，建議使用複製的預存程序，將更新操作封裝為一個獨立事務，在訂閱伺服器上調用複製的預存程序，在本地執行批次更新。

在高並發的資料庫做歸檔後的刪除，為了避免業務影響，刪除操作會迴圈分批刪除，每批間等待一定時間。這裡，我們也可以使用控製表來控制大事務分批操作。將控制邏輯和複製的預存程序結合，增加批次並減少執行時間。這個過程也可以工作得和非複製的更新一樣好，幾乎不會用實際的UPDATE替換EXEC SP操作。

具體指令碼參見：

Large Updates on Replicated Tables

深入最佳化複製架構

對於多個發行者、多個發布、多個訂閱的情況，我們可以從架構上來最佳化和擴充。將每個發行者獨立一個distribution資料庫，放到獨立的伺服器上，減輕分發代理的壓力。對於訂閱，不需要即時要求的，用提取訂閱，盡量減少發送訂閱的數量。

對於訂閱資料庫，可以配置為大容量模式，最佳化大量操作的日誌寫入。建立輕量的訂閱資料庫，減少不必要的索引和觸發器。對於提取訂閱，修改拉取間隔。增加記錄備份的頻率。配置高效能的設定檔。

複製最佳化參見：

15 SQL Server replication tips in 15 minutes

本文出自 “SQL Server Deep Dive” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1905168

如何處理SQL Server事務複製中的大事務操作