非域環境基于發布/訂閱模式的SQL Server 數據庫同步

■ 唐山市教育局 孟令旸

編者按：筆者在查看單位在線教學平臺的數據庫時發現其數據庫系統采用的是SQL Server 2012。鑒于SQL Server 2012 自帶發布/訂閱功能，通過該功能就可以實現數據庫同步，因此，筆者決定采用發布/訂閱模式實現數據庫同步。本文將詳細介紹操作過程。

為了安全起見，筆者沒有直接為在線教學平臺的數據庫做同步，而是搭建了一個簡單的實驗環境：創建兩臺虛擬機，將兩臺虛擬機組成一個獨立的局域網。兩臺虛擬機上均部署SQL Server 2012 數據庫系統。筆者將一臺虛擬機用作發布服務器，另一臺虛擬機用作訂閱服務器。在發布服務器上創建了數據庫“student”，該數據庫只有一張表“cjb”。這是筆者虛擬出來的學生成績表，用來完成數據實驗。兩臺虛擬機主要實驗數據如表1 所示。

可能是非域環境，筆者進行的數據庫同步實驗并不順利，遇到了很多問題。由于SQL Server 給出的錯誤提示并不具體，很難根據錯誤提示找到故障原因。例如，創建同步和訂閱后，在復制監視器中總提示“未初始化的訂閱”，無論做多少次重新初始化操作，仍出現這個提示。又如，出現“對路徑C:Program FilesMicrosoft SQL ServerMSSQL 11.MSSQLSERVERMSSQLRepl Dataunc……的訪問被拒絕”錯誤，出現錯誤的這個文件路徑是快照文件的存放路徑，于是筆者為快照文件重新指定路徑，仍報這個錯誤。類似這樣的問題還有很多：“無法讀取文件xxxx.pre”“工作目錄錯誤”……。

當然，在創建發布或創建訂閱過程中，也不是所有問題都這樣，有一些問題是可以根據錯誤提示判斷出故障原因的。例如，當遇到“SQL Server 無法將SQL Server 代理配置為自動啟動”錯誤時，這個錯誤提示說明沒有啟動SQL Server代理服務。此時可以通過運行“services.msc”，出現“服務”窗口后，手動將SQL Server 代理服務配置為自動并啟動該服務即可。

表1 測試用虛擬機主要實驗數據一覽表

互聯網上有很多關于SQL Server 發布訂閱方面的教程，筆者參照這些教程進行了嘗試，也以失敗告終。于是筆者在參考這些教程的基礎上，對SQL Server 發布訂閱機制進行分析，通過不斷實驗，終于完成了數據庫同步實驗。

必要的準備工作

在域控制器下，出現SID重復、計算機名重復、端口等問題，在加入域的過程中就會提示這些問題的。在非域控制器下，要保證數據庫成功同步，建議先做好以下準備工作。

1.確保發布服務器和訂閱服務器數據庫之間能相互訪問

在確保發布服務器和訂閱服務器之間能正常通信的基礎上，要注意以下事項：第一，避免SID 重復；第二，避免計算機名重復；第三，開放必要端口。

2.確保訂閱服務器有權限讀寫發布服務器上的快照文件夾

（1）在發布服務器上，創建一個新文件夾。以后在創建發布時，指定該文件夾為快照文件夾。在本例中，創建的快照文件夾是“d:student epldata”。

（2）在發布服務器和訂閱服務器上，創建一個完全相同的Windows 系統用戶，并設置好密碼。將該用戶加入到“administrators”用戶組，即將該用戶設置為管理員權限。在本例中，添加的用戶是“synch”。

（3）將發布服務器上的快照文件夾“d:student epldata”設置為共享。設置共享時，將用戶“synch”添加到共享用戶列表中，通過“權限級別”下拉菜單將該用戶的訪問權限設置為“讀寫”權限。

（4）驗證共享文件設置是否成功?？梢栽谟嗛喎掌魃?，用“synch”用戶登錄系統，打開資源管理器后，用共享路徑訪問位于發布服務器上的快照文件夾，在該文件夾下創建文件夾和文件，測試該用戶是否有讀寫權限。在本例中，共享文件夾的路徑是“\143PSPFER23 epldata”。

3.將SQL Server 代理服務的登錄身份更改為指定用戶賬戶

（1）運行命令“services.msc”，打開服務窗口。

（2）打開服務窗口后，找到“SQL Server 代理”服務。

（3）雙擊“SQL Server代理”服務，打開服務屬性對話框窗口后，單擊“登錄”選項卡，勾選“此賬戶”，然后單擊右側的“瀏覽”按鈕，將SQL Server 代理的登錄身份更改為前面所創建的用戶賬戶“synch”，并輸入與該用戶賬戶對應的密碼。

（4）點擊“常規”選項卡，啟動“SQL Server 代理”服務，將服務啟動方式配置為“自動”。

4.發布和訂閱服務器之間相互進行注冊

（1）首先要保證在安裝SQL Server 之后沒有修改服務器的計算機名。如果安裝SQL Server 之后修改過服務器的計算機名，在注冊SQL Server 服務器時容易出現“SQL Server replication requires the actual server name to make a connection to the server”之類的錯誤。如果遇到此類錯誤，最簡單有效的方法就是卸載并重新安裝SQL Server。

（2）新建并配置SQL Server 登錄名

①用SQL Server Manage ment Studio 連接到數據庫服務器后，展開“（計算機名）”→“安全性”→“登錄名”，右擊“登錄名”，在右鍵菜單中選擇“新建登錄名”。

②出現“登錄名-新建”對話框窗口后，單擊“搜索”按鈕，將前面所創建的本地用戶“synch”配置為SQL Server 登錄名。

③單擊左側“服務器角色”，在右側“服務器角色”列表框中勾選“public”和“sysadmin”。給新建的登錄名synch 分配sysadmin 權限（SQL Server 超級管理員權限）。

（3）在發布服務器上備份需要發布的數據庫（備份過程略）。

在本例中，需要發布的數據庫是“student”是用來做同步實驗的數據庫，數據庫里面的學號、姓名、成績、班級等所有數據均有計算機隨機產生，如有雷同，純屬偶然。

（4）將備份文件復制到訂閱服務器上，并在訂閱服務器上還原備份的數據庫（數據庫還原過程略）。

（5）將新建的本地用戶賬戶配置為發布（訂閱）數據庫的擁有者。

①用SQL Server Manage ment Studio 連接到數據庫服務器后，展開“（計算機名）”→“數據庫”→“student”→“安全性”→“用戶”，右擊“用戶”，在右鍵菜單中選擇“新建用戶”。

②出現“數據庫用戶-新建”對話框后，在“用戶類型”下拉列表中選擇“帶登錄名的SQL 用戶”。在“用戶名”文本框中為新建用戶取名，單擊“登錄名”文本框右側的按鈕進一步指定登錄名。

③出現“選擇登錄名”對話框窗口后，單擊“瀏覽”按鈕，選擇前面新創建的登錄名“synch”。

④為發布數據庫選擇登錄名“synch”后，右擊該用戶，在右鍵菜單中選擇“屬性”。

⑤出現用戶屬性窗口后，單擊窗口左側的“成員身份”，在“角色成員”列表中勾選“db_owner”，即可將本地用戶賬戶“synch”配置為發布數據庫的擁有者。

同樣，在訂閱服務器上將本地用戶賬戶“synch”也配置為數據庫“student”的擁有者。

（6）將SQL Server 服務器配置為允許遠程連接。

用SQL Server Manageme nt Studio 連接到數據庫服務器后，右擊服務器名稱，在右鍵菜單中選擇“屬性”。出現服務器屬性對話框后，單擊左側的“連接”，在右側“遠程服務器連接”欄目中勾選“允許遠程連接到此服務器”。

（7）開啟SQL Server 的網絡協議TCP/IP 和管道命名協議

通過Windows 的開始菜單，打開SQL Server 配置管理器。然后依次單擊“SQL Server 配置管理器”→“SQL Server 網絡配置”→“MSSQL SERVER 的協議”，將右側的“Named Pipes”和“TCP/IP”兩個協議的協議狀態配置為“已啟用”。

（8）發布和訂閱服務器之間相互注冊

①重新啟動服務器操作系統，用“synch”用戶賬戶登錄操作系統。

②用SQL Server Manage ment Studio 連接到數據庫服務器后，依次單擊“視圖”菜單→“已注冊的服務器”。

③出現“已注冊的服務器”選項卡后，右擊“本地服務器組”，在右鍵菜單中選擇“新建服務器注冊”。

④出現“新建服務器注冊”對話框后，在“服務器名稱”文本框中輸入訂閱服務器的服務器名稱，身份驗證選擇“Windows 身份驗證”。當前登錄系統的用戶是“synch”，此時Windows 身份驗證的用戶賬戶是“synch”。此時單擊“測試”按鈕可以測試連接是否成功。單擊“保存”按鈕完成訂閱服務器的注冊。

按照同樣方法，在訂閱服務器上注冊發布服務器。

發布數據庫

采用發布/訂閱方式同步SQL 數據庫，數據庫需要通過分發服務器對外發布，因此，在發布數據庫之前，需要配置分發服務器，然后再創建發布。第一次創建發布時，可以按發布向導指引配置分發服務器。分發服務器是包含分發數據庫并為所有類型的復制存儲元數據和歷史記錄數據的服務器，除此之外，它還為事務復制存儲事務。分發服務器與發布服務器可以是同一臺服務器，也可以是不同的服務器。在本例中，是在創建第一個發布的時候，按照向導指引配置的分發服務器，也就是當前服務器既是發布服務器，也是分發服務器。

1.打開“SQL Server Ma nagement Studio”后，依次展開“對象資源管理器”樹型目錄“SQL Server 實例名”→“復制”→“本地發布”。右擊“本地發布”，在右鍵菜單選擇“新建發布”。

2.出現“新建發布向導”對話框窗口后，單擊“下一步”，按照向導指引新建發布；出現“分發服務器”頁面后，選擇“（服務器名稱）將充當自己的分發服務器，SQL Server 將創建分發數據庫和日志”；出現“快照文件夾”頁面后，指定將要存儲快照的根位置。在本例中，指定的快照位置是前面所創建的共享文件夾“\服務器名 epldata”；出現“發布數據庫”頁面后，選擇要同步的數據庫；出現“發布類型”頁面后，根據實際需求選擇發布類型。

SQL Server 支持的發布類型包括“快照發布”“事務發布”“對等發布”和“合并發布”四種發布類型。如果選擇快照發布，在同步數據庫時，它是按照數據和數據庫對象出現時的狀態來復制和分發的。

需要注意的是，因為已發布數據的變化不被增量地傳播到訂閱服務器，而是周期性的被一次復制。采用此數據同步方式，即便在發布服務器上更新了數據，訂閱服務器上的數據不會被立即更新，而是要根據同步周期，等下次數據更新。

如果想要實現實時同步，即發布服務器上的數據更新后，訂閱服務器上的數據立即更新，此時可以選擇事務發布。不過，如果選擇事務發布，所發布數據庫的每張表都必須有主鍵。采用快照發布方式同步數據時，對表結構沒有任何要求。

采用快照發布或事務發布同步數據時，所同步的數據都是單向的，都是從發布服務器到訂閱服務器。如果需要雙向同步數據，可以選擇合并發布。但創建合并發布時，所發布數據庫的表要求有rowguid 列，也就是每張表必須有全局唯一標識符。

3.出現“項目”頁面后，根據實際需要選擇項目發布的表和其它對象；出現“篩選表行”頁面后，根據需要將不需要同步的對象篩選出去；出現“快照代理”頁面，勾選“立即創建快照并使快照保持可用狀態，以初始化訂閱”。

4.出現“代理安全性”頁面后，單擊“安全設置”按鈕。出現“快照代理安全性”頁面后，勾選“在以下Windows賬戶下運行”，然后在“進程賬戶”文本框中輸入前面所創建SQL Server 登錄名，在“密碼”和“確認密碼”框輸入與該登錄名對應的密碼。在“連接到發布服務器”欄選擇“通過模擬進程賬戶”。

5.出現“向導操作”頁面后，勾選“在向導結束時”→“創建發布”。

6.出現“完成該向導”頁面后，在“發布名稱”頁面為新建的發布取一個名字。檢查在向導中所做出的選擇，如果沒有問題，單擊“完成”按鈕，創建發布。

7.出現“正在創建發布”頁面后，可以看到創建操作狀態及其結果。

訂閱數據庫

在發布服務器（源數據庫服務器）發布數據庫后，目標數據庫可以通過訂閱已發布的數據庫實現數據庫同步，下面是在訂閱服務器上訂閱數據庫的具體過程：

1.打開“SQL Server Ma nagement Studio”后，依次展開“對象資源管理器”樹型目錄“SQL Server 實例名”→“復制”→“本地訂閱”。右擊“本地訂閱”，在右鍵菜單選擇“新建訂閱”。

2.出現“新建訂閱向導”對話框窗口后，單擊“下一步”，按照向導指引新建訂閱。

3.出現“發布”頁面后，通過“發布服務器”下拉菜單中的“查找SQL Server 發布服務器…”，查找并連接到發布服務器。

4.連接到發布服務器后，可以查看到已發布的數據庫及其它對象（如存儲過程），選擇需要訂閱的發布對象。

5.出現“分發代理位置”頁面后，選擇“在其訂閱服務器上運行每個代理（請求訂閱）”。選擇請求訂閱可以降低分發服務器數據處理工作的開銷。請求訂閱還允許訂閱服務器上的用戶確定同步數據更改的時間。

6.出現“訂閱服務器”頁面后，選擇需要訂閱的數據庫。

7.出現“分發代理安全性”頁面后，單擊訂閱服務器右側的“……”按鈕。

8.出現“分發代理安全性”頁面后，勾選“在以下Windows 賬戶下運行”，然后在“進程賬戶”文本框中輸入前面所創建SQL Server登錄名，在“密碼”和“確認密碼”框輸入與該登錄名對應的密碼。

在“連接到發布服務器”欄選擇“通過模擬進程賬戶”。在“連接到訂閱服務器”欄選擇“通過模擬進程賬戶”。

9.出現“同步計劃”頁面后，在“代理計劃”下拉列表中選擇“連續運行”。

10.出現“初始化訂閱”頁面，在“初始化時間”下拉列表中選擇“立即”。用發布訂閱方式同步數據，必須使用發布數據和架構的快照對訂閱數據庫進行初始化。如果此步操作沒有勾選“立即”，將無法實現數據庫同步。如果創建訂閱時沒有選擇立即初始化，可以等訂閱創建完成后，右擊此訂閱，手動初始化。

11.出現“向導操作”頁面后，勾選“在向導結束時”→“創建訂閱”。

12.出現“完成該向導”頁面后，在檢查在向導中所做出的選擇，如果沒有問題，單擊“完成”按鈕，創建訂閱。

13.出現“正在創建訂閱”頁面后，可以看到創建操作狀態及其結果。

同步實驗驗證

1.在訂閱服務器查看數據庫同步狀態

①打開“SQL Server Ma nagement Studio”后，依次展開“對象資源管理器”樹型目錄“SQL Server 實例名”→“復制”→“本地訂閱”→訂閱對象。右擊訂閱對象，在右鍵菜單選擇“查看同步狀態”。

②出現“查看同步狀態”對話框窗口后，可以查看到訂閱和發布的數據庫，開始時間以及同步的狀態，此時可以單擊“停止”/“啟動”按鈕停止/啟動同步，可以單擊“監視”按鈕進一步監視數據庫同步。

③打開“復制監視器”窗口后，依次展開“復制監視器”→“我的發布服務器”→發布服務器名→發布對象，在右側“所有訂閱”選項卡下可以看到當前訂閱對象的狀態。如果成功同步數據庫，此處訂閱的狀態應該是“正在運行”。

2.在發布服務器上查看數據庫同步狀態

①打開“SQL Server Ma nagement Studio”后，依次展開“對象資源管理器”樹型目錄“SQL Server 實例名”→“復制”→“本地發布”→發布對象→訂閱對象。右擊訂閱對象，在右鍵菜單選擇“啟動復制監視器”。

②打開“復制監視器”窗口后，依次展開“復制監視器”→“我的發布服務器”→發布服務器名→發布對象，在右側“所有訂閱”選項卡下可以看到當前訂閱對象的狀態。如果成功同步數據庫，此處訂閱的狀態應該是“正在運行”。

③如果要重新初始化訂閱，可以右擊需要初始化的訂閱對象，在右鍵菜單中選擇“重新初始化訂閱”。

3.修改數據驗證數據庫同步效果

無論是在發布服務器上，還是在訂閱服務器上，所查看的同步狀態實際上是同步訂閱的工作狀態。如果在創建發布時，選擇的是事務發布，就可以通過修改發布數據庫的數據來驗證數據庫同步效果。

在發布服務器上打開所發布的數據庫，編輯其中一個表的數據。

手動更新源數據庫數據后，打開訂閱服務器上訂閱的數據庫，打開同一張表，查看該數據是否已更新。

例如，在本例中，該學生的姓名已更新為源數據庫的姓名，說明數據庫實時同步實驗取得成功。