SQL Server 的分散式資料複寫技術

 

作者：楊先民

本頁內容

前言
資料庫複寫（Replication）的介紹與使用時機
如何建置資料庫複寫
複寫代理程式 (Replication Agent) 的功能
複寫的種類與使用的時機
設計一個安全的複寫環境（Secure Replication）
結論

前言

經常有朋友希望能夠將公司內部的資料庫分散到其他台的 SQL Server 資料庫系統，原因可能是因為平常沒有備份資料庫的習慣，希望能夠利用資料庫分散的方式，把資料「備份」到另外一台機器中，以達到實質備份的目的。

在企業內部有非常多機會可能需要將資料分散，本期文章將為您探討 SQL Server 中資料庫的複寫 (Replication) 技術。

本期文章會討論以下的主題：

• 資料庫複寫（Replication）的介紹與使用時機

• 如何建置資料庫複寫

• 介紹複寫代理程式（Replication Agent）的功能

• 設計一個安全的複寫環境（Secure Replication）

 資料庫複寫（Replication）的介紹與使用時機

我們若不先提到 SQL Server 資料庫所提供的複寫技術，光就名詞上來定義複寫的動作，實際上複寫只是很單純的將 A 資料庫的內容複製一份到 B資料庫，換句話說，無論是 A 資料庫或是 B 資料庫，都會有一模一樣的資料。

得知了這樣的理論之後，我們想看看，其實在實務中，有很多中方式能夠達到上述的情境，例如：

(a) 定期利用 DTS 將資料複製到第二台機器中。

(b) 定期備份 A 的資料庫到 B 的資料庫。

(c) 用 SQL Server 所提供的複寫功能。

換句話講，資料庫複製一份的觀念，並不是只能利用 SQL Server 所提供的複寫功能才能達到，應該會有很多種方法，都能達到類似的相同功能。

但是，為什麼會需要用到 SQL Server 所提供的複寫功能呢？主要的原因是資料的自治 (Autonomy) 與延遲 (Latency) 上的考量。

SQL Server 所提供的複製機制，主要的考量點在於時間上的差異，也就是「同一時間，未必能看到兩台 SQL Server 有相同的資料」，但是卻能夠確保最後的資料是一致的。

由這樣的理論來看，複製複寫能夠應用的商業環境就有以下情形：

(a) 有多人需要存取或多台資料庫需要相同資料庫的環境：

1. 讓遠端使用者就地利之便取得資料，避免透過不穩定或慢速的網路存取資料。

2. 行動裝置的使用者 (例如 Pocket PC) 在離線狀態時也能使用資料庫，並且在重新與網路連接時再將異動的資料同步回主資料庫中。

(b) 需要提升效能的環境

1. 將需要大量讀取的資料放在一台機器，而需要大量寫入的資料放在另一台機器，讓使用者可以執行查詢與分析，不需要中斷原先資料庫的交易處理。

2. 根據不同業務單位所需要的資料不同，而進行資料的散佈，例如會計部門只需要會計的相關資訊，就不需要散佈人事相關會用到的資料。

(c) 可以當作待命伺服器

1. 一般的小企業沒有錢建立叢集伺服器，但又希望資料庫能夠有備援的機制，這時可以利用複寫達成這樣的功能。

 如何建置資料庫複寫

在建置 SQL Server 2000 的複寫之前，首先必需瞭解一些複寫的基本術語，我們只要將複寫當成是一般出版業的相關名詞即可，請先參照圖一的內容。

圖一：複寫之中三個關係的示意圖。

複寫在建置之前，必需先確定三種角色，分為是發行者 (Publisher)、散發者 (Distributor)、訂閱者 (Subscriber)。

發行者（Publisher）：

• 維護來源資料庫。

• 確保發行集資料可用於進行複寫作業。

• 維護該月台中關於發行集的所有資訊。

• 偵測有異動的發行集資料。

• 將有異動的資料傳送給散發者（可以是位於同一台或是不同的伺服器上）。

散發者（Distributor）：

• 包含有散佈系統資料庫（Distribution System Database）。

• 負責儲存歷程記錄資料及/或交易與中繼資料。

• 可支援多個發行者。

訂閱者（Subscriber）：

• 接收複寫資料（也就是發行集）的伺服器。

• 儲存資料副本。

在發行者-訂閱者（Publisher-Subscriber）的觀念中，資料是組織成為發行集（Publication）與發行項（Article）。

如果你以「書店買書」的觀念來看這些專有名詞的話，問題就簡單多了，首先發行者就是書店的老闆，他可以決定要賣什麼書給你 (發行項)，但書依規定是要放在一個專櫃中出售的 (發行集)，客人可以上門來買書，或是老闆定期將新的書用快遞 (散發者) 寄送到訂閱者的手中。

在定義完角色之後，接下來再定義要發行何種內容，就是所謂的發行項與發行集：

發行項 (Article)：

發行項可以是整個資料表、資料表的特定部分資料、預存程式、檢視表、預存程式、或是使用者自訂函數等等。

何謂發行集 (Publication)：

發行集是一個或是多個發行項 (Article) 的集合。也是訂閱資料的基本單位。舉例來說：你可以建立一個 Products 發行集，包含有資料表、預存程式以及與訂單有相關的資料。

複寫必須要以發行集為單位，當作訂閱的基本單位，不可以單獨僅訂閱特定的發行項。若要複寫的發行項有引用其他的資料庫物件，則也必須發行該物件所引用的物件。例如，如果發行特定一個檢視表（View），則必須將該檢視表所引用資料表，當作此發行集的一部份。

訂閱 (Subscription)：

訂閱是向複製資料庫的一項請求。可由「發行者」或「訂閱者」來發起此一程式。如果是由「發行者」發起的程式，稱為發送訂閱 (push)，如果是由「訂閱者」發起的程式，稱為提取訂閱 (pull)，有關發送訂閱與提取訂閱，如圖二：

圖二：發送訂閱以及提取訂閱。

如果把複寫當成是購買書籍的話，發送訂閱就像雜誌社定期送書到讀者家中，而提取訂閱則是讀者自己走到書店買書，選用的時機如下表一：

	發送訂閱 (push)	提取訂閱 (pull)
誰發起訂閱工作	發行者	訂閱者
安全性	較高 (必需由發行者設定)	較低 (允許匿名訂閱)
訂閱者機器需求	較低	較高
容納訂閱數量	較低	較高

表一：發送訂閱以及提取訂閱的比較。

 複寫代理程式 (Replication Agent) 的功能

SQL Server 的複寫機制，實際上是透過代理程式 (Agent) 來幫忙完成的，換句話說，在使用複寫功能時，必需要將 SQL Server Agent 服務啟動，才能順利進行複寫的工作。

SQL Server 所提供的複寫有三種類型，底層都有搭配的代理程式，如表二：

快照式	交易式	合併式
快照集代理程式	快照集代理程式	快照集代理程式
散發代理程式	散發代理程式
	記錄讀取代理程式	合併代理程式
	佇列助讀員代理程式

表二：各種複寫對應到代理程式。

快照集代理程式：

這個代理程式主要會向雙方的機器確認彼此的架構，並且傳遞發行者目前資料到訂閱者的機器中。

散發代理程式：

發行者透過散發代理程式定期將資料傳送到訂閱者的機器中。

記錄讀取代理程式：

這個代理程式守候在交易日誌 (log) 中，查詢是否有新增修改以及刪除的記錄。

佇列助讀員代理程式：

所有交易的動作都是循序的，為了確保 SQL Server 不會漏掉任何一筆交易記錄，得利用佇列助讀員確保最後資料一定會傳送到訂閱者資料庫。

合併代理程式：

用來處理合併式複寫的動作，以及衝突管理。

 複寫的種類與使用的時機

由上得知，SQL Server 內是利用代理程式定期做資料的複製複寫工作，而隨代理程式的不同，而有三種的複寫種類：

快照式 (Snapshot) 複寫

Snapshot，翻譯成快照，就好像照相一樣，將目前資料庫裡面當下的所有資料喀嚓照下來，然後原封不動的寫到另一台機器中。快照式複寫的使用時機是原本資料庫的內容不常更新時，而因為資料量的關係，會花較長的時間將資料傳送到訂閱者機器，而設定快照式複寫時，資料庫的欄位不需要設定主鍵 (Primary Key)。

交易式 (Transactional) 複寫

交易式複寫顧名思義是當發行者資料庫有任何交易動作 (例如新增、修改、刪除動作)，都會被散發到訂閱者資料庫中。當使用交易式複寫時，資料的初始快照集集會先傳送到訂閱者機器中，之後的交易記錄就會循序的傳送到訂閱者機器中。

選用交易式複寫的時機在於，資料複寫的延遲效能壓到最小，兩台機器資料一樣的時間能夠愈短，愈能做有效資料處理，但在設定交易式複寫時，要確定資料表上有設定主鍵。

合併式 (Merge) 複寫

合併式複寫，是能夠讓訂閱者與發行者，可以互相修改彼此的資料。就好像現在的手機都有通訊錄，能和桌上型電腦的 Outlook 同步是一樣的道理。當你修改手機上的資料，可以同步到桌上型的 Outlook，而修改桌上型 Outlook 資料，也可以同步到手機上。

在使用合併式複寫的時機在於，雙方的資料庫需要互相異動，並且訂閱者常會處於離線的網路環境下，例如業務帶著客戶資料外出，可能會修改一些資料，最後再同步回發行者資料庫中。

 設計一個安全的複寫環境（Secure Replication）

設計 SQL Server 的複寫工作之前，首先請先確認環境的建置，大多數無法順利建立複寫的原因，是因為安全性的設定不正確，而導致複寫無法順利設定完成。

在設定複寫環境之前，我們只要想一下複寫的動作，自然就會瞭解為何安全性是如此的重要。

如果複寫的環境設定不出來，不妨先簡化環境的設定。舉例來說，先把發行者、散發者與訂閱者三種角色都由一台 SQL Server 扮演，這樣就一定能先測試成功，再來再慢慢的增加難度，以期能達到發行者、散發者與訂閱者分別用一台機器來扮演。

你可以用下面的步驟來檢視一下你的權限是否有設定正確：

1. A 機器要傳送資料到 B 機器，尤其是要利用代理程式幫忙傳送，一定得設定好權限。所以 SQL Server Agent 的登入帳號的權限不能太小，如果 A 機器與 B 機器都沒有加入網域的話，那麼你所設定的登入帳號必需要能完全存取 C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA 目錄的權限，如圖三與圖四。

   圖三：到控制台的服務，檢查一下登入帳號。

   圖四：設定 REPLDATA 的權限，確定登入帳號能完全控制。

2. 如果 A 機器與 B 機器有加入到網域，則 A 機器的登入帳號就用網域帳號進行登入，同樣的也必需要設定 REPLDATA 目錄的安全性。

3. 將你要設定的散發者與訂閱者機器，利用 Enterprise Manager 註冊進來。

接下來就可以利用精靈來設定複寫了。

複寫的設定的步驟如下：

1. 建立發行集。

2. 設定 push 或是 pull 發行集。

   因為這兩個動作所要做的步驟都是用精靈完成，而每個步驟所要做的事情都蠻重要的，所以就用一個步驟一個步驟介紹，分別是圖五到圖二十。

   圖五：利用工具 → 複寫建立與管理發行集。

   圖六：選擇想要散發的資料庫，然後按下建立發行集。

   精靈的好處在這裡，如果你並沒有完成一些工作，某些按鈕就沒有辦法按下。

   圖七：選擇散發者的機器，如果你事先已經將其他台的 SQL Server 先註冊進來的話，你可以選擇其他台 SQL Server 當作散發伺服器，如果沒有的話，你也可以用本機電腦作為散發者， SQL Server 會幫你在該台機器中建立一個 distribution 資料庫。

   圖八：預設會將所有資料庫的資料，利用快照集資夾進行傳送，所以要先確認一下這個資料夾是否 SQL Server Agent 具備有存取的權限，換句話來說，資料之間的搬移複製動作，都是透過這個資料夾暫存相關的資訊。

   圖九：選擇要散發的資料庫，這裡是以 Northwind 資料庫為例。

   圖十：你可以選擇 SQL Server 複寫的對象是何種類型的資料庫，可以是 SQL Server 2000、SQL Server 7.0 或是非 SQL Server 的關連式資料庫，例如你可以選擇是 Oracle，或是 Access 資料庫。(不過若是選擇異質資料庫，則只能用 push 的方式將資料發送到異質資料庫，而不能使用 pull 的方式提取資料庫)。

   圖十一：選擇想要散發的資料表或是檢視表、預存程式，如果是交易式複寫，則資料表必需要有主索引鍵。

   圖十二：設定發行集的名稱，因為一個資料庫可能會有數個發行集。

   圖十三：精靈正在建立發行集的設定。

   圖十四：設定完發行集後，就可以設定資料集的訂閱工作，只要選擇發行集名稱，再按下「發送新的訂閱」，就會出現設定精靈。

   圖十五：從清單選出訂閱者的名稱，如果訂閱者名稱很多，則必需一個一個先將這些訂閱者利用 Enterprise Manager 註冊進來。

   圖十六：選擇要複寫到訂閱者機器上的哪一個資料庫。

   圖十七：選擇更新訂閱的頻率，如果是交易式複寫的話，可以選擇「連續」，不然可以使用排程，使得快照集資料定期傳送到訂閱者端。

   圖十八：如果訂閱者端沒有資料，則要選擇初始化結構描述與資料，以及啟動快照集代理程式以立即開始初始化處理，這樣訂閱者端資料庫才會初始資料。

   圖十九：精靈開始執行，將訂閱者端的資料初始化。

   圖二十：可以在複寫監視器中，查看發行集是否已經產生了快照集，並且將資料傳送到訂閱者的資料庫端。

   這時後，可以透過複寫監視器中，看到 test 資料庫已經將快照集準備好了，可以切換到 test 資料庫看一下裡面的內容，如圖二十一：

   圖二十一：在 test 資料庫中的 Employees 資料庫，目前已經將整個快照集的資料收集過來。

   圖二十一的快照集產生出來的速度，端看你的資料量多寡，以及與訂閱者端網路的頻寬決定，所以快照集代理程式這個動作，在實務上是最花費時間的動作。

   不管你是使用哪一種複寫方式，都會有快照集的產生，之後資料間的異動，就看複寫的特性了。

   本例是用交易式的複寫，所以我在發行者端 Employees資料庫做個異動之後，再檢查複寫監視器，即可看到如圖二十二的內容：

   圖二十二：交易複寫的改變。

   由圖二十二可以發現，只要發行者有任何異動，都會被記錄助讀員發現，進而將該筆交易資料送到訂閱者資料庫中，完成資料庫複寫的工作。

 結論

資料庫複寫機制，是一個相當實用的功能，它的運用範圍非常的廣泛，也適用在各種不同的環境之下，如果企業中的資料，因為不同原因而要分散成多個地方存放，可以考慮使用 SQL Server 2000 所提供的複寫機制完成你的需求。

 

PS【轉者加】：以下語句可以尋找 Local Publications 和 Local Subscriptions

　　　　--發行集
　　　　　　select publisher_db,publication,description from distribution.dbo.MSpublications
　　　　--訂閱集
　　　　　　select name,publication,subscriber_db,creation_date from dbo.MSdistribution_agents