[SQL SERVER][HA]資料庫複寫#建置交易式複寫
在建置交易式複寫前，先來了解交易式複寫內部是怎麼作業的，
如果往後遇到麻煩時，也可以比較快速排解問題。
這次建置交易式複寫拓撲如下:
很單純的單向資料同步，發行者至訂閱者。
運作方式
追蹤變更:
紀錄讀取器代理程式負責取得發行者的變更內容，交給散發者。散發代理程式負責將變更內容傳送給每個訂閱者。
紀錄讀取代理程式週期執行動作如下:
1. 連接散發者資料庫取得前次複寫週期最後的LSN(Log Sequence Number)
2. 連接至發行者資料庫的交易紀錄檔，找到最後的LSN並由下一筆LSN開始讀取交易紀錄檔最新的交易紀錄(commit)。
3. 將讀取的交易紀錄檔，依照順序寫入散發資料庫。
4. 寫入成功後，為本次讀取的交易紀錄檔寫入已複寫旗標在發行者交易紀錄檔中。
5. 在散發資料庫寫入歷史及錯誤資料。
散發代理程式週期執行動作如下:
1. 連接散發資料庫，取得即將傳送至訂閱者的交易紀錄。
2. 取淂訂閱者未認可的交易，並封裝交易為批次。
3. 連接至訂閱者並執行批次，將最後的LSN寫入至MSdistribution_history資料表
4. 在散發資料庫寫入歷史及錯誤資料。
交易選項(該次建置選用立即更新模式)
立即更新模式:
1.對於立即更新訂閱，「訂閱者」端的變更會傳播至「發行者」，
並使用「Microsoft 分散式交易協調器」(MS DTC) 來套用。
請確定已在「發行者」和「訂閱者」端安裝並設定了 MS DTC。
2.立即更新訂閱使用的觸發器要求連接到「發行者」以複寫變更。
如需設定此連接安全性的詳細資訊，請參閱＜更新訂閱的安全性考量＞。
3.如果發行集允許立即更新訂閱，且發行集中的發行項具有資料行篩選，
則無法篩選無預設值的不可為 Null 資料行。
使用該模式要是發行者不在線上，就表示更新分散式交易動作失敗，
而啟動此一分散式交易的觸發程序當然也算失敗，所以還是有其風險。
佇列更新模式:
1.包含在合併式發行集的資料表也無法發行為允許佇列更新訂閱的部分交易式發行集。
2.在使用佇立更新時，不建議更新主索引鍵資料行，這是因為主索引鍵是所有查詢的記錄定位器。
若衝突解決原則是設為「訂閱者優先」，則應在更新主索引鍵時多加注意。
若「發行者」與「訂閱者」的主索引鍵均更新，則結果將會是有著不同主索引鍵的兩資料列。
3.對於資料類型為 SQL_VARIANT 的資料行：當「訂閱者」端插入或更新資料後，
該資料會在從「訂閱者」複製到佇列之時，
由「佇列讀取器代理程式」以下列方式進行對應：
BIGINT、DECIMAL、NUMERIC、MONEY 和 SMALLMONEY 對應至 NUMERIC。
BINARY 和 VARBINARY 對應至 VARBINARY 資料。
由於佇列更新是非同步作業，故有可能會產生資料衝突，
但使用該模式的好處是可將離線時發生的所有交易一併傳送及更新(當發行者重新上線)。
交易式複寫架構
開始建置
--將SQL Server instance標示為散發者use masterexec sp_adddistributor @distributor = N'RICO-WIN2K8VM\SQL2K8', @password = N''GO--建立散發資料庫、Schemaexec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\sqldata', @log_folder = N'E:\sqlldf',@log_file_size = 500, @min_distretention = 0, @max_distretention = 48, @history_retention = 48, @security_mode = 1GO--建立資料表和快照資料夾屬性use [distribution]create table UIProperties(id int)GOEXEC sp_addextendedproperty N'SnapshotFolder', N'E:\ReplData', 'user', dbo, 'table', 'UIProperties'GO--建立發行者exec sp_adddistpublisher @publisher = N'RICO-WIN2K8VM\SQL2K8', @distribution_db = N'distribution',@security_mode = 1, @working_directory = N'E:\ReplData', @trusted = N'false', @thirdparty_flag = 0,@publisher_type = N'MSSQLSERVER'GO
新增發行集
選擇發行集類型
勾選立即建立快照並初始化訂閱
該帳戶在兩台server均存在，同時sqlserver和agent也要以該帳戶執行。
完成發行集
新增訂閱
選擇在散發者端執行所有代理程式
加入遠端訂閱者
設定相關安全性
設定代理程式排成
設定訂閱屬性
完成訂閱
發行者新增100筆資料
檢查遠端訂閱者資料是否同步
發行者刪除所有資料
確認遠端訂閱者資料是否同步
發行者再次新增100000筆資料
可以看到複寫監控顯示100000個命令。
確認遠端訂閱者資料是否同步
發行者更新特定資料(93101筆)
update dbo.bill_t set cancel_reason_1='test',cancel_date =getdate()where cancel_reason_1 is null and cancel_date is null
確認遠端訂閱者資料是否同步
資料果然即時同步。
參考
sp_adddistributor (Transact-SQL)
sp_adddistributiondb (Transact-SQL)
sp_updateextendedproperty (Transact-SQL)
Originally published at dotblogs.com.tw on December 11, 2010.
