快速逐步的實作SQL Server之主從複寫機制

要好好地設定一個Microsoft SQL Server的複寫機制,中間涉及到的資料庫及作業系統等級的權限以及設定、UNC或是FTP,沒有花上一小時是搞不定的。原因無它,安全而已。但是有時候在戰場上面不可能允許我們像學術派如此的輕鬆,結案時間可是不饒人的,因此我們今天要討論的是如何在某一寬鬆程度的假設下,快速完成資料庫之間的複寫機制。請先確定你的資料庫中,要從SQL-A複寫到SQL-B的資料表,具備了PK(Primary Key)的資料結構設計。例如下圖中位於SQL-A資料庫Employee資料表,其iAutoIndex欄位即為此表之PK。

主機及網路配置如下:

  1. 發行端(在此例亦作為散發端) 電腦名稱:SQL-A 192.168.1.1
  2. 訂閱端            電腦名稱:SQL-B 192.168.1.2

實作前的假設條件如下:

  1. 發行端(散發端)與訂閱端都「沒有、不需要」加入AD網域或架構(Active Directory)。
  2. 發行端(散發端)要發送的資料表,我確定都已經有PK(Primary Key)了,如上圖所示。
  3. 發行端(散發端)的作業系統,我擁有最高權限。(Administrator)
  4. 發行端(散發端)的資料庫,我擁有最高權限。(SA)
  5. 我不在乎發行端(散發端)的主機效能。
  6. 訂閱端的資料庫,我擁有最高權限。(SA)

開始設定兩台資料庫之間的交易式複寫機制

Step 01. 先把SQL-A以及SQL-B資料庫主機中的防火牆,開啟1433 Port,以利後續溝通。

Step 02. 在SQL-A端主機,以系統管理員身分打開記事本。

Step 03. 到「C:\Windows\System32\drivers\etc」下找到「hosts」檔案,並利用剛才打開的記事本編輯它。因為我們沒有AD,因此只好利用這個方式將SQL-B的IP 192.168.1.2加入到列表中。

Step 04. 在SQL-A端主機,打開SQL Server 2014組態管理員。

Step 05. 按下右鍵啟動SQL Server Agent。此外,如果可以的話,建議到服務裡面,設定自動啟動SQL Server Agent,而不是預設的手動啟動模式。

Step 06. 打開SQL-A端的MSSMS(Microsoft SQL Server Management Studio),在複寫>本機發行集上面按右鍵,點選新增發行集。

Step 07. 進入新增發行集精靈選單。

Step 08. 指定SQL-A本身亦為散發者。

Step 09. 精靈會警告你,系統將會自動啟動SQL Server Agent服務。

Step 10. 指定快照集資料夾「ReplData」,在這裡我們使用系統預設的路徑,不進行變更。

Step 11. 指定要建立發行的資料庫桶子「SlashLook」。

Step 12. 因為我們要進行的是單向的從SQL-A將資料表抄寫到SQL-B,因此我們選擇建立的是「交易式發行集」。

Step 13. 指定要抄寫哪一些SQL-A的資料表到SQL-B去。從下圖中我們也可以發現,你可以只選擇到某些欄位發送過去就好,如此一來可以保留一些機敏性資料不要發行過去。

Step 14. 你可以更進一步的加入一些SQL指令,來過濾出你不想要被複寫過去的機敏性資料。

Step 15. 選擇發行端立即建立快照集,讓等一下訂閱端可以方便的初始化。

Step 16. 「代理程式安全性」這部分需要好好的被設定,我們點選「安全性設定」。

Step 17. 「執行快照集代理程式」這裡,我們指定了「SQL-A\Administrator」帳號供給執行。至於「連接到發行者」這裡,當然就是給SQL-A資料庫的SA帳號密碼了。

Step 18. 按下確定退出到主設定精靈後,點選下一步。

Step 19. 在精靈結束後,建立發行集。

Step 20. 建立發行集名稱,在這裡我們輸入了「CompanyData」。要注意的是,一個發行集裡面允許有多個發行項目(資料表),日後都可以再增添修改,因此這裡的名稱你可以取成比較廣泛一點的定義。

Step 21. 接下來會顯示進度視窗,沒問題的話理論上都是綠色的勾勾才是。下圖中設定發散者處呈現的黃色驚嘆號,是在說明我們沒有跑到Windows的服務裡面,把SQL Server Agent設定成自動啟動。

Step 22. 回到SQL-A的MSSMS後,在本機發行集選單上面應該會出現一個有「CompanyData」字樣的發行集,請在上面右鍵>新增訂閱。

Step 23. 進入訂閱精靈畫面。

Step 24. 指定發行端為SQL-A,發行資料庫為「SlashLook」、發行集就是我們之前建立的「CompanyData」。

Step 25. 指定在散發者端執行所有的代理程式。當然啦,以大型網站來說,這樣的設定要特別注意效能上的問題。

Step 26. 接下來是設定訂閱者,點選「加入SQL Server訂閱者」。

Step 27. 這時候我們之前做的hosts檔案就派上用場啦!請大方的填入目標對象(訂閱者)為SQL-B,並輸入該資料庫的SA帳號密碼。

Step 28. 從SQL-A發行到SQL-B的資料庫後,要寫在哪個資料庫桶子中?請下拉選擇新增資料庫,我們將創造一個全新的資料庫桶子。

Step 29. 當然啦,建議與SQL-A端的資料庫桶子名稱一致,日後會比較好維護與回憶。

Step 30. 接下來是設定「散發代理程式安全性」,請點選圖中的「…」做進階設定。

Step 31. 散發代理程式之電腦帳戶,當然是設定成SQL-A\Administrator來進行運作。而連接到訂閱者的部分,當然是輸入SQL-B端的資料庫SA帳號密碼。

Step 32. 回到母設定畫面,一切備齊沒問題後點選下一步。

Step 33. 代理程式我們選擇「連續式執行」。如果你有效能上的考量的話,也可以在這邊改成是「排程式執行」。

Step 34. 當然是命令訂閱者馬上給我初始化。

Step 35. 精靈結束後建立訂閱。

Step 36. 結束精靈前檢視所有的設定屬性,請點選完成。

Step 37. 如果設定都沒問題的話,就可以開心的看到綠色勾勾。

Step 38. 回到SQL-A的MSSMS,在樹狀選單本機發行集中,應該可以看到建立的CompanyData發行集,其中有一台叫做SQL-B的資料庫,已經對SlashLook資料庫桶子進行訂閱了。請在這上面按右鍵>啟動複寫監視器,讓我們來看一下運作狀況。

Step 39. 狀況看起來一切都很好,呈現了綠色的勾勾。如果狀況不對的話,會給你紅色的叉叉,並載明一些錯誤原因。

Step 40. 讓我們回到SQL-B的MSSMS,看一下SQL-A資料庫的表格,是否有發行到SQL-B了,結果確定是有的。本機訂閱的部分亦出現來自於SQL-A的發行資訊。

最後,你可以試著自己進行一下SQL-A發行的資料表的CRUD,當你進行資料的變更後,去SQL-B重新整理,會發現資料馬上同步變更喔!熟練的話,以上的步驟全部跑完花不了你10分鐘的時間,是不是既簡單又快速呢?

StepByStep SQLServerReplication NoActiveDirectory MicrosoftSQLServerManagementStudio MSSMS SQLServerManagementStudio SSMS