如何解決REPLICATION的IO太高的問題

最近發現Sql server在執行Transactional Replication同步資料時有時候會有效能相當低落的情況，於是我使用了活動檢視器檢查Sql server，結果發現IO一直居高不下：

 

查了一下網路，發現是『清除散發: distribution』的緣故(= =又是它)，因為我們只有兩台機器，所以有一台要負責一邊Replication與刪除MSrepl_commands造成IO增加，於是我把這個作業的排程從每10分鐘執行改為在中午12點鐘執行減少產生IO的頻率後效能低落的情況大大減少，不過順道一提在修改JOB執行的頻率時要考量到硬碟空間是否足夠，如果硬碟不夠千萬別這樣做。

阿肥的SQL SERVER replication troubleshooting第五集

訊息20015，層級16，狀態1，程序sp_MSreplremoveuncdir，行83

無法移除目錄'\\RYO\ReplData\unc\RYO$SQL2008R2_REPLICATIONDB_PUB\20120204141716\'。請檢查xp_cmdshell 的安全性內容並關閉其他可能在存取這個目錄的程序。

(1)    開啟xp_cmdshell

(2)    檢查存放snapshot的目錄權限要有full control，這裡要注意的是，權限的部分是要設定共用的部分而非安全性的部分。

參考

如何解決在 TRANSACTIONAL REPLICATION的DISTRIBUTION資料庫的MDF過大的問題

如何清除REPLICATION的DISTRIBUTION資料庫

最近發現DISTRIBUTION的MDF過大，後來發現是因為『清除散發: distribution』這個SQL JOB執行失敗的而造成無法刪除MSrepl_commands這個table的資料，而該JOB所產生的『訊息20015，層級16，狀態1，程序sp_MSreplremoveuncdir，行8』錯誤訊息解決方法請參考該網址，解決該問題後，重新執行該job後， MSrepl_commands這個table的資料就可以刪除了，以下是我解決問題DISTRIBUTION資料庫過大問題的步驟：

(1)    確認散發者的交易與紀錄保留時間

(2)    執行SELECT TOP 10 OBJECT_NAME(id) OBJECTNAME,reserved,rowcnt FROM SYSINDEXES ORDER BY 2 DESC;查看MSrepl_commands這個TABLE的容量，如果太大要執行sp_MSdistribution_cleanup使用清除MSrepl_commands這個TABLE，執行指令為：EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 48

執行結果：

(3)    將發行集的訂閱過期從永遠不會過期改為間隔12小時未同步就卸除。

另外若有多個發行集可以使用下列SQL指令來觀察間隔時間

SELECT DISTINCT

srv.srvname publication_server

, a.publisher_db

, p.publication publication_name

, p.retention

, ss.srvname subscription_server

, s.subscriber_db

FROM MSArticles a

JOIN MSpublications p ON a.publication_id = p.publication_id

JOIN MSsubscriptions s ON p.publication_id = s.publication_id

JOIN master..sysservers ss ON s.subscriber_id = ss.srvid

JOIN master..sysservers srv ON srv.srvid = p.publisher_id

JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id

AND da.subscriber_id = s.subscriber_id

ORDER BY p.retention

執行結果如下：

執行上述步驟後使用DBCC SHRINKDATABASE 來壓縮資料庫即可。


參考網址

阿肥的SQL SERVER replication troubleshooting第四集

SQL Server Agent 錯誤:

已拒絕執行作業 RYO\SQL2008R2-ReplicationDB-pub-RYO22008R2-ReplicationDB的要求 (來自使用者sa)，因為作業已經從 使用者 sa 的要求執行.

已將資料庫內容變更為’XXXX’

(1)     確認DB的狀態：sp_helpdb 'YourDataBase'

(2)     重新啟動SQL Server Agent。

阿肥的SQL SERVER replication troubleshooting第三集

該處理無法讀取檔案'C:\ReplData\unc\XX\20120204135258\XXX_tab_5.pre'，因為作業系統錯誤3。

系統找不到指定的路徑。

Google了一下看到這行話， please use the UNC mode to transactional publication or with updatable subscriptions ，於是我先把原本的資料夾設定為共享資料夾，並在publication的快照集路徑改為使用城UNC的方式就解決這個問題了。

點選自己的publication後按右鍵點選屬性

點選快照集，將原本的位置改成\\SERVER\RealData

阿肥的SQL SERVER replication troubleshooting第二集

資料表 'ooxx' 的 卸除 失敗。  (Microsoft.SqlServer.Smo)

無法 卸除 資料表 'ooxx'，因為它已用來複寫。 (Microsoft SQL Server, 錯誤: 3724)

1.       先把有REPLICATION 的物件(TABLE、VIEW)等取消掉，在本機發行集取消這些物件的複寫。

2.       Drop或truncate table。

阿肥的SQL SERVER replication troubleshooting第一集

SQL Server 無法連接到伺服器 'OLDSERVERNAME'。

SQL Server 複寫需要有實際的伺服器名稱才能連接到伺服器。不支援透過伺服器別名、IP 位址或任何其他替代名稱來進行連接。請指定實際的伺服器名稱，'NEWSERVERNAME'。 (Replication.Utilities)

(1)   使用SELECT @@SERVERNAME找出SERVERNAME

(2)   執行sp_dropserver 'OLDSERVERNAME\SQL2008R2'刪除舊名稱

(3)   執行sp_addserver 'NEWSERVERNAME\SQL2008R2','local'增加新名稱

(4)   重新啟動

SQL SERVER HA技術比較

一、情境：

1.       A機器(SQL 2008R2)單向同步B機器(SQL 2008R2)，機器數目2台。。

2.       在特定時間大量的DELETE與INSERT資料。

3.       資料延遲要小於5分鐘。

4.       建立資料副本

5.       資料庫為OLAP類型

二、SQL SERVER 2008 HA技術比較表

項目	replication	failover cluster	mirror	log shipping
用途	l   產生多組資料副本 l   資料正本做異動而在資料副本做查詢 l   同步異質性資料庫 l   正本與副本的資料可以彼此同步 l   資料延遲性小	l   SERVER的HOT STANDBY l   應用程式不必修改連線字串： 因為連接是共用的虛擬名稱 l   可以自動轉換主機	l   資料庫的HOT STANDBY l   可以自動轉換備援資料庫成主體資料庫(需要三台機器)。 l   配合snapshot可以有在備援機器讀取資料 l   二台機器就可以完成mirror l   應用程式不必修改連線字串： 加上Failover Partner=ServerB即可 l   資料延遲性小	l   產生多組資料副本 l   可以設定同步時間間隔，防止誤刪資料。 l   資料正本做異動而在資料副本做查詢
缺點	l   使用Merge類型的Replication 會有資料衝突的問題。 l   無自動切換 l   使用Transactional 類型時TABLE一定要有PK l   使用Snapshot 類型時，TABLE要有識別欄位	l   比較貴：硬碟上的花費是最貴的。 l   Share Disk掛掉後，整組一起掛。 l   管理麻煩。 l   不會有資料副本。	l   只限於使用者資料庫 l   若不使用自動轉換備援資料庫成主體資料庫，需要手動轉換。 l   鏡像資料庫若不配合snapshot，此時資料是不可以讀取鏡像資料庫的資料。 l   只能產生單一資料副本。	l   資料的延遲性較高。 l   將資料庫切換為簡單復原模式，會使記錄傳送停止運作 l   若將資料庫轉為可查詢狀態會有可能發生資料不是最新或使用者斷線的情況。 l   無自動切換

1.       經過比較後，會產生資料副本的有replication與mirror與log shipping，去掉failover cluster。

2.       除非配合mirror ，否則Snapshot只能在單一SERVER上執行所以不列入考量。

3.       因為我們的資料庫的復原模式為SIMPLE，所以不能使用log shipping，去掉log shipping。

4.       因為mirror不能讀取備援資料庫與必須手動切換成主體資料庫，去掉mirror。

由以上4點得知使用replication是最適合的。

三、Replication的比較表。

replication類型
Snapshot	l   DB資料量小 l   資料變更數量可觀但次數不頻繁時 l   可收受資料延遲較久 l   資料庫物件(TABLE或VIEW等)異動不大 l   TABLE要有識別欄位(identified) l   機器數目：2台
Transactional	l   需要最即時的資料 l   同步異動資料庫物件(TABLE或VIEW等) l   極大量的插入、更新和刪除。 l   雙向資料複製。 l   可處理異質資料庫，如 Oracle。 l   TABLE一定要有PK l   機器數目：2台。
Merge	l   可雙向同步資料。 l   可在離線時變更資料，上線時才同步資料。 l   可能會發生同步資料的衝突。 l   機器數目：2台

由Replication的比較表可知選擇Transactional Replication是最適合的。

SQL Server Database Mirror Part1:資料庫鏡像簡介

資料庫鏡像：

為增加資料庫可用性的軟體方案，在兩個不同的instance上，同步單一資料庫的資料。

資料庫鏡像的優點：

增加資料保護	資料庫多了副本，可以提供完整或近乎完整的資料備援性。
提高資料庫的可用性	High Availability:自動容錯移轉會迅速將資料庫的待命副本變成線上狀態，無資料遺失。 High Protection Mode:無自動容錯移轉，無資料遺失。 High Performance:資料庫管理員可選擇對資料庫的待命副本進行強制服務，但可能發生資料遺失。
提高資料庫在升級期間的可用性	輪流升級，將維護的等待停機的時間減到最少。

資料庫鏡像的角色：

Instance	功能
Principle Server	是主要資料庫，可以接受使用者的異動請求。
Mirror Server	是備援資料庫，是處於復原狀態，不斷接收來自 principle Server的Database異動資料並進行同步動作，不允許連線。但是可以建立 snapshot database 提供read-only的功能。
Witness Server	是 instance level的服務，主要提供自動錯誤偵測與 failover的功能。

資料庫鏡像分類

同步模式分類	SQL SERVER 2005 RTM分類	SQL SERVER 2005 SP1分類	Witness Server	自動Failover	SAFETY
Synchronous Database Mirroring	High Availability Mode	High Safety Mode with Automatic Failover	需要	Yes	FULL
	High Protection Mode	High Safety Mode without Automatic Failover	不需要	No	FULL
Asynchronous Database Mirroring	High Performance Mode	High Performance Mode	不需要	No	OFF

High Availability Mode：

當應用程式將資料寫入Principle Server時，此時Principle Server會傳送LOG檔到Mirror Server，Principle Server需等候Mirror Server將LOG寫入磁碟中的回應，Principle Server收到Mirror Server的回應後，Principle Server才會commit並告知應用程式，此模式會自動容錯移轉。

High Protection Mode:

同High Availability Mode，但不具自動容錯移轉的功能。

High performance mode:

當應用程式將資料寫入Principle Server時，此時Principle Server會傳送LOG檔到Mirror Server，Principle Server不需等候Mirror Server將LOG寫入磁碟中的回應。就可以告知應用程式transaction 已經commit，此模式不具自動容錯移轉的功能。。

注意事項：

不能在master、msdb、tempdb 或 model 資料庫建立鏡像。

只適用於使用完整復原模式的資料庫。

參考資料：資料庫鏡像作業模式