通過SQL Server 2008資料庫複寫實現資料庫同步備份

標籤：style   blog   http   使用   os   io   strong   檔案   

SQL Server 2008資料庫複寫是通過發布/訂閱的機制進行多台伺服器之間的資料同步，我們把它用於資料庫的同步備份。這裡的同步備份指的是備份伺服器與主伺服器進行即時資料同步，正常情況下只使用主要資料庫伺服器，備份伺服器只在主伺服器出現故障時投入使用。它是一種優於檔案備份的Database Backup解決方案。

在選擇資料庫同步備份解決方案時，我們評估了兩種方式：SQL Server 2008的資料庫鏡像和SQL Server 2008資料庫複寫。資料庫鏡像的優點是系統能自動探索主伺服器故障，並且自動切換至鏡像伺服器。但缺點是配置複雜，鏡像資料庫中的資料不可見（在SQL Server Management Studio中，只能看到鏡像資料庫處於鏡像狀態，無法進行任何資料庫操作，最簡單的查詢也不行。想眼見為實，看看鏡像資料庫中的資料是否正確都不行。只有將鏡像資料庫切換主要資料庫才可見）。如果你要使用資料庫鏡像，強烈推薦killkill寫的SQL Server 2005 鏡像構建手冊，我們就是按照這篇文章完成了資料庫鏡像部署測試。

最終，我們選擇了SQL Server 2008資料庫複寫。

下面通過一個樣本和大家一起學習一下如何部署SQL Server 2008資料庫複寫。

測試環境：Windows Server 2008 R2 + SQL Server 2008 R2（英文版），兩台伺服器，一台主要資料庫伺服器CNBlogsDB1，一台備份資料庫伺服器CNBlogsDB2。

複製原理：我們採用的是基於快照的事務複製。主要資料庫伺服器產生快照，備份程式庫伺服器讀取並載入該快照，然後不停地從主要資料庫伺服器複製交易記錄。見：

圖片來自SQL Server聯機叢書

安裝與配置步驟：

一、在兩台伺服器上安裝好SQL Server 2008 R2，主要安裝的組件：Database Engine(含SQL Server Replication)，Management Tools。

二、主要資料庫伺服器（發行伺服器）的配置：

1. 在主要資料庫伺服器CNBlogsDB1建立樣本資料庫CNBlogsDemo（注意Recovery mode要使用預設值Full，只有這個模式才能進行事務複製），然後建立一張測試表，比如：CNBlogsTest。

2. 設定存放快照的檔案夾：

建立發布之前，先設定一下存放快照的檔案夾，建立發布後會在該檔案夾產生快照檔案，訂閱伺服器需要在初始化時載入該快照檔案。

選擇Replication》Local Publications》屬性，在出現的視窗中選擇Publishers，如：

點擊紅框處的按鈕，出現設定視窗：

在Default Snapshot Folder中設定快照檔案存放路徑。

3. 在主要資料庫伺服器建立發布：

在Replication》Local Publications中選擇New Publication，出現一個嚮導。先選擇要發布的資料庫CNBlogsDemo，然後選擇發布類型Transational publication，如：

點擊Next，出現錯誤：

原來所有要複製的表都需要有主鍵，剛才建CNBlogsTest表時，沒有建主鍵。建一下主鍵，並重新啟動嚮導就可以了。

接著選擇要複製的對象：

點Next，Next，進入Snapshot Agent視窗，選擇Create a snapshot immediately and keep the snapshot available to initialize subscriptions，見：

Next，進入Agent Security：

選擇Security Settings，進行相應的帳戶設定：

一個是設定運行Snapshot Agent的Windows帳戶，我們這裡選擇與SQL Server Agent同樣的帳戶。

一個是設定串連發行伺服器的SQL帳戶，我們這裡就用主要資料庫伺服器的sa帳戶。

繼續：OK，Next，Next，為這個發布起個名字：

 

點擊Finish，就開始正式建立發布，建立成功就會出現如下視窗：

這時查看快照檔案夾，就會看到unc檔案夾，快照檔案就在這個檔案夾中。

這裡要考慮這樣一個問題，如何讓訂閱伺服器通過網路訪問這個快照檔案夾。

我們在這個問題上折騰了一些時間，本來想通過共用資料夾的方式，但又不想開啟匿名共用，折騰了半天，沒搞定訂閱伺服器訪問共用資料夾使用者驗證的問題。於是採用了FTP的方式，所以，下面介紹一下如何讓訂閱伺服器通過FTP訪問快照檔案。

4. 設定快照的FTP訪問

首先在主要資料庫伺服器上開通FTP服務，建立一個指向快照檔案夾的FTP網站，設定好可以遠端連線的FTP帳戶。然後在這台發行伺服器設定一下FTP用戶端配置。配置方法如下：

在Replication》Local Publications中選擇剛才建立的發布[CNBlogsDemo]:CNBlogsDemo_Publication，選擇屬性》FTP Snapshot，如：

 

選中Allow Subscribers to download snapshot files using FTP，並設定一下FTP用戶端串連參數，訂閱伺服器就是通過這裡的設定串連FTP伺服器的（註：Path from the FTP root folder的設定要和一樣，設定為：/ftp）。

點擊OK，這時會在快照檔案夾中建立一個ftp檔案夾，並在該檔案夾中產生快照檔案。

這樣，發行伺服器就配置好了，下面配置訂閱伺服器。

三、備份資料庫伺服器（訂閱伺服器）的配置：

進入訂閱伺服器CNBlogsDB2，建立與發行伺服器同名的資料庫CNBlogsDemo，使用完全復原模式。

在Replication》Local Subscriptions中選擇New Subscriptions，進入嚮導。

Next，進入選擇發行伺服器的視窗，選擇Find SQL Server Publisher，出現伺服器串連視窗：

這裡要注意的是Server Name中一定要填寫發行伺服器的電腦名稱，如果電腦名稱串連不上，要在hosts檔案中加一個IP位址解析。

成功串連發行伺服器之後，就可以看到剛才在主要資料庫伺服器上建立的發布：

Next，進入“分發代理工作位置”的選擇視窗：

 

 

我們這裡選擇pull subscriptions，把資料給拉過來，這樣主要資料庫伺服器的負擔會輕些。

Next，選擇訂閱伺服器上的資料庫，之前我們已經建好同名的資料庫，所以系統自己會找到。

Next，進入分發代理安全設定視窗：

點擊紅框內的按鈕，進入設定視窗：

設定如，Connect to the Distributor處設定的是發行伺服器的sa帳戶。

OK, Next, Next, Next:

Next, Finish, Success:

備份資料庫的訂閱就建好了！

現在來瞧一瞧訂閱伺服器CNBlogsDB2上的用於複製的資料庫CNBlogsDemo：

看！我們在發行伺服器上建立的表CNBlogsTest複製過來了。

現在我們去發行伺服器CNBlogsDB1上添加一條記錄：

再去訂閱伺服器CNBlogsDB2瞧一瞧：

資料立即同步過來了！搞定！

    

遇到的問題：

在測試過程中被兩個問題折騰了很長時間。

1）發行伺服器的Log Reader Agent不能啟動，錯誤資訊：

· The process could not execute ‘sp_replcmds‘ on ‘YCSERVER006‘. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) 
Get help: http://help/MSSQL_REPL20011 
· Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517) 
Get help: http://help/15517 
· The process could not execute ‘sp_replcmds‘ on ‘YCSERVER006‘. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) 
Get help: http://help/MSSQL_REPL22037

開始測試時，附加了一個現有資料庫進行複製遇到了這個問題，附加的是一下SQL Server 2005資料庫檔案，Owner為空白，改為sa問題就解決了，如：

2）第二個問題就是前面已經描述過的訂閱伺服器訪問發行伺服器上的快照檔案夾的問題，後來通過FTP的方式解決的。

對於SQL Server 2008資料庫複寫，目前我就學習了這些，期待園子裡有這方面經驗的朋友也來分享一下，在分享過程中你也會學到很多。

相關文章：

SQL Server 2008 資料庫同步的兩種方式 （發布、訂閱）