16、SQL Server 複製及常見錯誤處理

標籤：

SQL Server 複製

複製是一組技術的組合，可以用此組合對資料和資料庫物件進行複製由一個資料庫移動到另一個資料庫。

複製的英文是Replication，重複的意思，而不是Copy。複製的核心功能是儲存轉寄，在來源資料庫中進行增、刪、改之後，重複這個動作到其他資料庫。

使用複製的原因

1、負載平衡：通過將資料複製到其他資料庫伺服器來減少當前伺服器的負載。

2、分區：將經常使用的資料和曆史資料進行隔離。

3、授權：將一部分資料提供給需要使用資料的人。

4、資料合併：每個地區都有各自的資料，將其資料進行合并。

5、容錯移轉：來源資料庫無法串連時，可串連備用資料庫。或來源資料庫保持連結情況下，用備用資料庫進行測試預存程序等。

複製的組成

複製的概念類似發行雜誌，發行商出版後，通過報刊亭等地方分發到訂閱雜誌的人手裡。在複製中，發行商，報刊亭，訂閱人 分別對應 發行伺服器，散發者，訂閱伺服器。

發行伺服器

包含需要被發布的來源資料庫。

散發者

包含散發資料庫，作用於儲存轉寄發行伺服器發過來的資料。一個散發者支援多個發行伺服器，類似報刊亭可以出售多個出版社的雜誌。散發者也可以和發行伺服器是同一個執行個體，出版商不通過報刊亭自己直接賣。

訂閱伺服器

包含發行伺服器所發布的資料副本，這個副本可以是一個資料庫，或者一個表，甚至是一個表的子集。

發布和文章

發布指的是發行就緒的文章的集合，這些文章包括表，預存程序，視圖和使用者自訂函數。

發行資料表時，還可以根據限定條件只發行資料表的子集。

訂閱

訂閱是相對發布的一個概念，訂閱定義了訂閱伺服器從哪個散發者接收發布。有兩類訂閱者式，推薦訂閱push和提取訂閱pull，在推薦訂閱情況下，當發行伺服器產生更新時，散發者直接更新訂閱內容，而提取訂閱需要訂閱伺服器定期查看散發者是否有可用更新，如果存在可用更新，則訂閱伺服器更新資料。

複製類型

SQL Server 共有3種複製類型，每一個發布只能有一種複製類型，分別為：快照式複寫，事務複製，合併式複寫。

快照式複寫

快照式複寫將發布的所有表做成一個鏡像（資料庫物件的快照），然後一次性複製到訂閱伺服器。由於發送的是整個資料集，所以中間發生的DML（增刪改）不會像其他複製那樣自動傳送到訂閱伺服器。

快照式複寫的特點是：

1、佔用網路寬頻，因為一次性傳輸整個鏡像，所以快照式複寫的內容不要太大。

2、適合更新不頻繁，但每次更新都比較大的資料。

3、訂閱伺服器是唯讀環境。

事務複製

事務複製也是由快照開始的，在第一次設定好事務複製後，發布的表、預存程序等將會被鏡像，之後每次對於發行伺服器所做的改動都會以日誌的方式傳送到訂閱伺服器。使發行伺服器和訂閱伺服器幾乎可以保持同步。

事務複製的特點是：

1、發行伺服器和訂閱伺服器內容基本可以同步。

2、發行伺服器，散發者，訂閱伺服器之間的網路連接要保持暢通。

3、訂閱伺服器也可以設定成提取訂閱，使訂閱伺服器可以不用一直和散發者保持連結。

4、資料量有持續的增長，更新頻繁。

合併式複寫

合併式複寫是允許發行伺服器更新資料庫，也允許訂閱伺服器更新資料。定期將這些更新進行合并，使發布的資料在所有的節點上保持一致。因此，有可能發行伺服器和訂閱伺服器更新了同樣的資料，當產生衝突時，並不是完全按照發行伺服器優先來處理。而是根據設定進行處理。

配置複製

首先在本地建立兩個執行個體，其中MSSQLSERVER預設執行個體作為發行伺服器和散發者，MYSQLSERVER作為訂閱伺服器。

重新安裝一次sqlserver，選擇新執行個體，不選擇預設執行個體即可。

 

1、在MSSQLSERVER執行個體中配置發行伺服器和散發者

將發行伺服器和散發者用作同一台

設定儲存快照檔案夾

需要對此目錄增加Everyone的讀寫權限，否則無法寫入會建立失敗。

配置散發資料庫

然後完成即可。

這樣發行伺服器和散發者就配置完成了。

 

2、建立一個發布

選擇要發布的資料庫

發布類型選擇事物發布

選擇需要複製的表

選擇立即建立並保持可用狀態

代理安全性選擇預設代理賬戶即可

點擊完成

發現發布成功了，但是有一個警告。這是因為代理服務預設是關閉的，需要啟動它。

啟動代理服務後，去查看快照代理程式狀態

啟動就可以了。

這樣發布就建立完畢了。

 

3、在MYSQLSERVER執行個體中建立訂閱。

選擇尋找SQL Server伺服器

串連MSSQLSERVER執行個體

選擇剛剛建立的發布

然後選擇發送訂閱，這樣每次發行伺服器存在更新就會自動推送到訂閱伺服器上。

在本地建立一個資料庫用於接收資料。

分發代理安全性，選擇類比進程賬戶，這裡有個問題一會說。

然後點擊完成即可

到此訂閱建立完成

 

然後查看同步狀態

出現這個錯誤時，在MSSQLSERVER執行個體中，找到代理->作業->該作業停止。

使用複製監視器

進程無法串連到訂閱伺服器，使用者NT SERVICE\SQLSERVERAGENT登陸失敗，這是因為MSSQLSERVER執行個體在嘗試用自己的SQLSERVER代理帳號進行串連，但是在MYSQLSERVER執行個體中，代理的帳號是不對的。所以要修改一下訂閱伺服器的串連方式。

在訂閱伺服器中本機訂閱是查看不到的，可以在發行伺服器中本地發布中對應發布下查看。

這裡修改下訂閱伺服器的串連方式，再次查看同步狀態。

已經開始進行傳遞事物了，查看複製監視器

查看訂閱伺服器該表數量

在發行伺服器中添加測試資料

重新查詢訂閱伺服器數量

可以發現，訂閱伺服器和發行伺服器幾乎是保持同步的。

 

16、SQL Server 複製及常見錯誤處理