逐步實作大型網站資料庫所需要的三層式複寫架構

在一個大型網站的設定裡,資料庫所要承載的壓力其實是非常的重的,因此我們往往需要複寫的機制來完成所謂的資料庫負載平衡,最近剛好有在研究這一塊領域,於是就把正確的逐一做法(Step By Step)放到上面來,讓有需要的人自行取用之,縮短大家研究資料庫複寫的時間。在這邊文章中,所進行的主機架構為三層,也就是「發行者」、「散發者」、「訂閱者」三台機器是分開的,而並非各位在網路上常見的雙層式架構「發行者(兼散發者)」、「訂閱者」,理論上來說三層式的效果來說才是最優秀的,因為發行者機器其實就是最累的主要資料庫,不應該再加諸任何功能於身才是。設定架構如下圖所示:

以下是三台機器的名稱以及IP資訊:

發行者 Publisher
SQL-A 192.168.1.1

散發者 Distributor
SQL-B 192.168.1.2

訂閱者 Subscriber
SQL-C 192.168.1.3

開始進行三層式複寫架構的設定

Step 01. 將三台機器的防火牆,都開啟1433 Port以利資料庫間進行溝通連線。

Step 02. 將三台機器都用系統管理員身分開啟記事本,來解決整個網域間沒有AD(Active Directory)的問題。如果你已經有了AD架構,那麼請忽略這些步驟。

Step 03. 用記事本去開啟「C:\Windows\System32\drivers\etc」下的「hosts」檔案,並進行主機名稱對映IP的編輯工作。其中:

  1. SQL-A 添加:SQL-B 192.168.1.2
  2. SQL-B 添加:SQL-A 192.168.1.1
  3. SQL-C 添加:SQL-A 192.168.1.1、SQL-B 192.168.1.2

Step 04. 因為資料交換的過程中,會用到網路分享協定(SMB、CIFS),因此請將三台伺服器中的網路卡(要選準備互相溝通的那一塊喔,不要勾錯塊網路卡了)開啟內容選單,並進行下列的設定

  1. SQL-A 勾選:Client for Microsoft Networks
  2. SQL-B 勾選:Client for Microsoft Networks、File and Printer Sharing for Microsoft Networks
  3. SQL-C 勾選:Client for Microsoft Networks

Step 05. 設定一組三台伺服器都是同樣的帳號密碼之伺服器帳號,這個帳號未來為套用到SQL Server Agent之執行身分,並且被用在網路分享協定之帳號密碼上。

  1. SQL-A 新增:使用者帳號 DbDeplication/密碼 三台都一樣就好
  2. SQL-B 新增:使用者帳號 DbDeplication/密碼 三台都一樣就好
  3. SQL-C 新增:使用者帳號 DbDeplication/密碼 三台都一樣就好

Step 06. 綁定三台機器的SQL Server Agent服務,都指定到使用DbDeplication執行,將服務設定為自動啟動,並請啟動它吧。

散發者設定

Step 07. 由於SQL-B是散發者,因此我們需要為它設定一個專門用來存放快照集的SMB、CIFS資料夾,命名為「DbRepl」,並將此資料夾指派本機的存取權限給DbDeplication。

Step 08. 當然,指派SMB、CIFS的存取權限也是不可缺少的。這一步驟設定完成,系統會自動幫你在防火牆開啟網路芳鄰會用到的相關通訊埠號,喜歡更精確設定的人可以自行去開關。

Step 09. 我們要先設定SQL-B就是散發者。請到SQL-B開啟MSSMS(Microsoft SQL Server Management Studio)>複寫>設定散發,運行設定散發精靈。

Step 10. 對,SQL-B本身就是散發者,決定就是你了皮卡丘。

Step 11. 這裡限定只能使用網路路徑(UNC Path),所以剛才如果沒有設定SMB、CIFS的人,這下就慘了。其實精靈也已經跟你講得很清楚了,它只能記下來,但根本不知道你設定是否正確,所以沒有設定的人可以趕快跳出去設定,一切都還來的及。

Step 12. 指定散發資料庫「distribution」與其儲存路徑,這邊用預設值就好。

Step 13. 這裡詢問的是,哪一個發行者可以使用這個散發資料庫?

Step 14. 把SQL-A加進來吧!

Step 15. 發行者連到散發者來進行複寫管理作業時,是需要使用到密碼的,由於我們是第一次使用,因此需要自訂一個新的密碼。

Step 16. 快完工了,設定散發。

Step 17. 確認所有的設定資訊。

Step 18. 看到綠色勾勾,真是令人愉快!

發行者設定

Step 19. 身為發行者的SQL-A,一進入後不是先去做發行集的設定,而是先去設定散發精靈,根系統講說,我是發行者,但是我的散發伺服器在另外一台。

Step 20. 從下圖中可以發現我們正在SQL-A,但是要指定SQL-B為我們的散發者,請點選「加入」按鈕。

Step 21. 當然是輸入SQL-B的SA帳號密碼了。

Step 22. 找到SQL-B了!

Step 23. 之前就有設定過,發行者連入進行複寫管理作業時,是需要使用到密碼的。

Step 24. 準備結束了。

Step 25. 看一下散發精靈的總結資訊。

Step 26. 又開心地看到綠色勾勾了。

Step 27. 設定發行集之前,讓我們先來確定一下要發送的資料庫資訊。

資料庫:SlashLook

資料表:Employee

資料表欄位:iAutoIndex(PK, Primary Key)、cName

Step 28. 進入發行集精靈嘍!

Step 29. 選擇我們要發行的資料庫。

Step 30. 這一次我們要進行的是交易式發行集。

Step 31. 全部的資料表與欄位都發行過去了。

Step 32. 若有需要進行資料篩選的人,可以在這邊篩掉一些機敏性資料。

Step 33. 立即建立快照集。

Step 34. 指定快照集的代理程式,當然是選SQL Server Agent這個好夥伴啦!請點選安全性設定。

Step 35. 選擇SQL Server Agent,發行者請輸入SQL-A的SA帳號密碼。

Step 36. 確認一下快照集代理程式的身分資訊。

Step 37. 精靈結束後建立發行集。

Step 38. 幫你的發行集取一個名稱吧!

Step 39. 看到綠色勾勾就是開心的時刻。

Step 40. 此刻,回去SQL-B(散發者)端,我們已經發現SQL-A已經將快照集推送過來了。

訂閱者設定

Step 41. 終於走到了訂閱的步驟了!

Step 42. 讓我們來找出發行者(SQL-A)吧!

Step 43. 輸入SQL-A的SA帳號密碼。

Step 44. 找到發行集啦!找到喇叭商標啦!

Step 45. 為求效能最佳化,當然是自己的訂閱自己拿!

Step 46. 別忘了訂閱者(SQL-C)的資料庫現在還是空的,當然是建立一個資料庫桶子以利進行複寫工作啊!建議名稱與原始發送過來的資料庫桶子名稱一致,日後會比較好管理。

Step 47. 輸入要複寫過來的資料庫桶子名稱。

Step 48. 下一步。

Step 49. 接下來是指定散發代理程式的安全性,也就是SQL Server Agent啦!請點選圖中的「…」進行設定。

Step 50. 當然是選SQL Server Agent來當我們的散發代理程式的處理者。此外,連接到散發者處,請輸入SQL-B的SA帳號密碼。

Step 51. 下一步。

Step 52. 為了確保資料的同步性,選擇連續執行。如果資料變異過大且資料數量很龐大,可以在這個地方選擇排程式運行。

Step 53. 立即初始化。

Step 54. 建立訂閱。

Step 55. 檢視訂閱精靈的總設定資訊,按完成後應該就可以看到令人開心的綠色勾勾啦!

接下來就是見證奇蹟的時刻了

Step 56. 先到訂閱者SQL-C,確定資料表已經有複寫過來啦!

Step 57. 我們到發行者SQL-A,把張三改名字,砍掉王五這筆資料,並新增陳六資料。

Step 58. 接著馬上到SQL-C重新整理,發現資料庫的異動都被即時複寫過來啦!如果你細心一點,應該可以發現「iAutoIndex: 3」不見了,這就是為何複寫一張資料表,該表需要有設定PK的原因。

以上就是這篇三層式複寫架構的一步步說明,希望可以節省掉你大量的錯誤測試、摸索的時間。

如果有一些人為了追求更高的安全性,在Step 35連入發行者、或Step 50連入散發者作業的時候,選擇了「藉由模擬處理帳戶」、或者是「非最高權限管理員SA」的資料庫使用者(成員),那麼極有可能會出現複寫作業失敗,這時候不妨請您回到SQL-A、SQL-B的MSSMS中,看一下「安全性」→「登入」→「新增登入」,看看是否有該名使用者被授權在這個資料庫中運行?例如:SQL-A發行者資料庫的某使用者,應該要可以使用SlashLook資料庫桶子才對,又例如:SQL-B散發者資料庫的某使用者,應該要可以使用distribution資料庫桶子才對。

StepByStep SQLServerReplication DatabaseReplication TransactionalReplication Publisher Distributor Subscriber