2012年3月13日 星期二

SQL Server 分散式架構 - 點對點交易式複寫 + NLB

        在我之前的文章中 [SQL Server 負載平衡架構介紹(Load Balancing)],介紹了多種不同的負載平衡的方式,但其實在多種架構中,我個人比較喜歡的是透過複寫機制(Replication)來進行,複寫類型主要有下列三種,大家可以點選下列的說明進行參考,在這就不特別介紹。SQL Server的Load Balancing也是最多人問的一塊,所以在這邊介紹,而且之前的經驗上覺得得在專案的初期大家往往沒有一個好的架構開始進行,往往等到系統後期或上線後才進行調整,結果造成相當大的成本浪費,如設定的重新配置與程式碼的改寫,所以希望透過本章節可以讓大家多認識一個不錯的架構,再請大家多多參考。

複寫類型:

  • 快照式複寫考量
  • 交易式複寫考量
  • 合併式複寫考量

  • 在複寫的架構下,個人認為最佳的方式是可以多台同時進行讀寫,而且資料交換的時間又短(適交易量而定,但這是所有複寫類型中資料交換最快的一種),如下圖左邊的部份,但導入前需特別注意前端應用程式設計的部份,以免造成資料的衝突,最常見的是當資料表主鍵設計時,常常只利用流水號來進行,但是這樣的方式並不適合,容易造成資料寫入時的問題,尤其是當你表格為 Master/Detail 時,更容易有問題。在不改變架構的情況下,其實可以參考右邊的架構,也就是一台負責寫入,其他台的電腦負責讀取,這種情況下需要調整的程式就會非常的少,但是卻可以達到很大的效益,而且當你的交易越多或有效能上的問題時,就可以一直串連並分散交易,達到擴充的功能。

    在左邊的架構中前端的主機可以對A與B之間一台進行寫入的動作,之後資料庫端就會自動進行資料的同步,在本章中我們將介紹SQL Server 2005之後交易式複寫中擴充的一個新功能,點對點交易式複寫 (Peer-to-Peer Transactional Replication),讓A電腦與B電腦同時可以有讀寫的功能,另外透過NLB接收前端的需求,然後再平衡的分配給後端資料庫,進行達到 SQL Server - Load Balancing 的架構。




    配備說明:
    第一台
    電腦名稱:WIN-2008R2-1
    OS:Windows 2008R2 Enterprise
    DB:Windows 2008R2 Enterprise
    說明:第一個節點。

    第二台
    電腦名稱:WIN-2008R2-2
    OS:Windows 2008R2 Enterprise
    DB:Windows 2008R2 Enterprise
    說明:第二個節點

    設定流程:

    NLB (Network Load Balancing) 設定:
    關於NLB的設定,在請大家參考我之前的著作 [網路負載平衡 - Reporting Service 與 NLB 的結合],其中有介紹到NLB的部份,我就不在此特別說明,謝謝。

    點對點式複寫功能 (Peer-to-Peer Replication) 設定:
    1、設定第一台的發行集。
    1-1. 登入第一台電腦,並開啟SQL Server Management Studio
    1-2. 點選 [複寫] -> [本機發行集] -> [新增發行集]



    1-3. 選擇你要進行複寫的資料庫。

    1-4. 請選擇 [交易式發行集]。

    1-5. 選擇要進行複寫的資料表,可以全部選取。
    PS:這部份如果有效能上的考量時,你可以選擇特定的資料表進行複寫即可,由其在合併式複寫上,越多的表格更由於造成資料複寫上的延遲。

    1-6. 如果有需要特別設定篩選的資料時,請在此設定或請選擇下一步直接跳過即可。

    1-7. 由於我是透過手動同步各個節點的資料庫,所以在此處不設勾選。

    1-8. 設定複寫代理程式安全性,請記得最好使用網域帳號進行。

    1-9. 請勾選 [建立發行集],如果你想保留此次的設定,你可以勾選產生指令碼,日後直接套用即可。

    1-10. 請輸入發行集的名稱。


    1-11. 請再點選 [複寫] -> [本機發行集] -> [發行集名稱] -> [屬性]。

    1-12. 請將 [訂閱選項] -> [點對點複寫] -> [允許點對點訂閱] 設定成 [True],然後選確定。


    2、設定第二台主機的散發
    2-1. 登入第二台
    2-2. 點選 [複寫] -> [設定散發]


    2-3. 選擇第一項,也就是本身為散發者。

    2-4. 請設定第二台主機上的一個指定目錄進行分享,而且讓第一台也可以進行存取與寫入。

    2-5.  此步驟請依照預設值即可,如有需要再自行修改。

    2-6. 設定發行者與散發資料庫,並選擇下一步。

    2-7. 請勾選 [設定散發],如果你想保留此次的設定,你可以勾選產生指令碼,日後直接套用即可



    2-8 設定完成後,請將你設定複寫的資料庫進行完整備份,然後還原到第二台上,藉以確保第一台與第二台上的資料與Schema都相同。

    3、設定點對點式複寫功能 (Peer-to-Peer Replication)
    3-1. 登入第一台
    3-2. 選擇 [複寫] -> [本機發行集] -> [發生集名稱] -> [設定點對點拓撲]


    3-3. 選擇發行集。

    3-4. 在空白處點選滑鼠右鍵 -> [加入新的對等節點]。

    3-5. 登入你要加入的節點主機。


    3-6. 選擇加入的節點中的資料庫,請注意此處的識別碼必須都不相同。

    3-7. 加入後你就可以看而有一條雙箭頭的線將兩個節點連結。

    3-8. 設定記錄讀取器代理程式的安全性,這個部份也是請使用網域帳號進行設定。

    3-9. 如果每一個節點的安全性設定皆相同時,可以勾選下方的選項即可。

    3-10 跟上一個步驟相同。

    3-11. 由於在2-8的時候已經手動的初使化每一個資料庫了,所以請選擇第一個選項。



    3-12 最後在二台的 [本機發行集] 上你就可以都有相同的發行集名稱。


    關於複寫的進作情況,你可以透過 [複寫監視器] 進行確認,當然這個架構在使用上,需特別注意衝突的問題,通常都是前端程式設計的不小心所造成,而你可以從複寫監視器或SQL Server Error Logs中看出,但此架構並不會自動進行衝突的排除,所以必須手動排除,所以在系統上線前請多多測試與前端程式的部份,以免日後的衝突發生,造成資料的不正確。


    參考連結:
    SQL Server 的分散式資料複寫技術
    http://technet.microsoft.com/zh-tw/library/dd125513.aspx
    SQL Server Replication
    http://msdn.microsoft.com/en-us/library/ms151198.aspx
    Managing SQL Server 2005 Peer-to-Peer Replication
    http://technet.microsoft.com/en-us/magazine/2006.07.insidemsft.aspx
    Peer-to-Peer Transactional Replication
    http://technet.microsoft.com/en-us/library/ms151196.aspx
    How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio)
    http://technet.microsoft.com/en-us/library/ms152536.aspx
    使用複寫監視器監視複寫
    http://msdn.microsoft.com/zh-tw/library/ms151780.aspx


    關鍵字:Peer-to-Peer ReplicationNLBNetwork Load BalancingDistribution ArchitecturePeer-to-Peer Transactional Replication

    27 則留言:

    1. CaryHsu大請教您;
      NLB+點對點交易式複寫是說2台SQL Server做點對點交易式複寫,並在2台作業系統做了NLB,當AP端只需連入一個IP即進行分散處理。

      若不理會作業系統部份不做NLB,單純在二台Sql Server做訂閱復寫動作,AP端程在分為單讀資料庫、讀寫資料庫進行,這樣可行嗎?

      回覆刪除
      回覆
      1. 你的問題很好,其實也有許多的企業透過你說的方式進行,比如說報表作業等,這樣的確可以減少第一台機器的負載,也是一個很好的方法哦!!

        刪除
    2. caryhsu大您好:
      根據您這篇文章我進行試作,可是我發現一個問題,針對datatable複寫目前沒有發生問題,可是我發現在新增預存程序時並不會自動覆寫到另個一個資料庫,請問一下這是正常的嗎 ??
      煩請指教

      回覆刪除
      回覆
      1. 補充一下
        環境 SQL 2005 std , windows 2003 R2

        刪除
      2. 請問一下你在1-12的圖中,有一項 [複寫結構描述變更] 你是否有設定為true。

        刪除
    3. 您好,

      有個問題請教,當訂閱者超過5台以上,有時會出現"複寫代理程式發現錯誤,並設定在作業步驟重試間隔內重新啟動。如需詳細資訊,請參閱先前的作業步驟歷程記錄訊息或複寫監視器",請問會是什麼原因造成,例如系統資源不足,或SQL Server資源不足嗎?建議可以從哪個地方觀察?

      回覆刪除
      回覆
      1. 你是有觀察超過5台才有這種情況嗎?有特定的發生時間或頻率嗎?你也可以透過效能監視器觀察一下機器的效能情況,另外根據描述,你也需檢查一下Job的執行歷程看問題為何,這樣才會比較有方向。

        刪除
    4. 您好,

      之前沒有設定max server memory,大概2-3台訂閱者就會出現錯誤了,將max server memory調成16GB(系統有24GB)之後,前一陣子五台訂閱者ok,最近又出現此錯誤訊息了,但是時好時壞,到是沒有特定發生的頻率,目前從效能監視器看,發散者記憶體大約使用了50%

      回覆刪除
      回覆
      1. 你的版本是多少,之前如果有記憶體的問題,你可以考慮參考一下下列的更新,另外你遇到的錯誤代碼是多少,你要再提供多一點的資訊,我才比較容易協助你處理。

        Use Alerts for Replication Agent Events
        http://msdn.microsoft.com/en-us/library/ms151752.aspx
        FIX: High memory usage when you run Replication Snapshot Agent in SQL Server 2008 or in SQL Server 2008 R2
        http://support.microsoft.com/kb/2606301/en-us

        刪除
    5. 不好意思...想請問一下 目前使用的是SQL 2008R2版本 建立完交易式複寫後在設定那邊卻找不到點對點交易式開啟的按鈕,只有出現可"更新的訂閱,建立與同步處理,結構描述複寫,資料轉換"這四大項...請問是否要再別的地方設定呢??

      回覆刪除
      回覆
      1. 照著我的步驟設定是不會有問題,其本上設定可以透過T-SQL與GUI的方式進行,但不建議你透過語法的方式強制啟用,建議你可以透過 [點對點交易式複寫] 一文中的考量要素中的限制進行確認即可,有問題再一起討論吧。 ^^

        點對點交易式複寫
        http://technet.microsoft.com/zh-tw/library/ms151196(v=sql.90).aspx
        How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming)
        http://technet.microsoft.com/zh-tw/library/ms146914(v=sql.90).aspx
        如何:設定點對點交易式複寫 (SQL Server Management Studio)
        http://technet.microsoft.com/zh-tw/library/ms152536(v=sql.90).aspx

        刪除
    6. Cary前輩您好,剛好本公司也是用此技術,讀您這邊文章受益良多。
      本公司大概是一台做Publish,兩台做Subscriptions,但JOB不定期會跑出The job failed.The job was invoked by Schedule XXX (Replication agent schedule)的錯誤,有時Restart就會正常,有時又不正常,雖然訊息很淺顯易懂,但真的不知道問題怎麼發生的,不知道Cary前輩您有沒有遇過類似的問題,謝謝您!

      回覆刪除
      回覆
      1. 你好,錯訊訊息的部份你可以再看一下SQL Server的Error Log或是Event Log中是否有進一步的訊息,因為這樣要解決你的問題有點困難,或是你可以參考下列的文章啟用進階的Log進行確認,如果你有進一步的訊息,再Post上來一起討論吧。

        參考文章:
        How to enable replication agents for logging to output files in SQL Server
        http://support.microsoft.com/kb/312292

        刪除
    7. 您好~ 請教在NLB+交易式複寫的可更新訂閱中,欄位資料有流水序號產生時,切換到另一台寫入時序號不會依序的狀況,不知是否有解決方法?

      回覆刪除
      回覆
      1. 依照我的經驗,通常如果要將資料庫進行本章的架構,建議不要依靠流水序號,因為這樣在分散式的架構下,容易會遇到你的問題,解決方法建議還是透過自訂編號來解決,這樣才會是比較好的方案。

        刪除
    8. 您好~
      有個疑問可否幫忙解惑,在SQL備份備援的機制中,仰賴交易式複寫是不錯的方式!
      系統運作時,B-SQL主機僅能讀資料,無法寫資料進去,但我不解的是..當A-SQL主機異常中斷SQL服務時,B-SQL主機是如何承接A主機的角色(可寫可讀)呢?

      回覆刪除
      回覆
      1. 在A主機發生問題時,基本上由於是透過NLB進行控制,所以並不會影響應用程式的運作,但由於沒有完整(即時)的資料保護,所以在A主機發生問題時,可能會有資料Loss的情況發生,所以這部份需要再注意。

        整體上,如果真的有資料完全不能允許Loss的情況發生時,建議可以透過AlwaysOn是一個不錯的方式。

        上述的問題如有不清楚的地方,歡迎再來信討論。

        刪除
    9. 大大您好

      蔽司有使用此同步功能同時20台SERVER的需求,不曉得是否可行?

      回覆刪除
      回覆
      1. 由於你要同步的台數有點多,不太建議同時進行,你可以考慮透過不同的架構進行,如分切成階層式等,另外也要考量你的同步方式等因素,但實際運作上是可以達成的。

        刪除
    10. Cary大您好!

      想請教您,如果一個交易的內容是典型的「檢查帳戶餘額,足夠就扣款」,這種情況如果在「多台同時讀寫」架構下是不是應該會有問題?除了改用「一台寫入多台讀取」架構外,還有什麼建議的解法嗎?

      回覆刪除
      回覆
      1. 其本上如果我認為如果是使用多台同時讀寫時不太適合,畢竟關係到錢的部份,而且在程式面有許多要注意的部份,所以其實還是一台寫入多入讀取會比較適合。

        刪除
    11. CaryHsu您好:
      目前我的環境跨網段兩台SQL A與SQL B,目前是SQL A TABLE有資料,SQL B TABLE有資料,目標是設定每5分鍾的排程去進行複寫,當SQL A有新增資料時,會複寫SQL A原有資料加新增資料到SQL B;SQL B環境也同樣和SQL A一樣會複寫到SQL A。
      使用合併式發行集,在SQL A TABLE有資料(為發行集),SQL B TABLE(為訂閱者)不存在,是可以進行複寫成功的。
      但是如果是SQL A TABLE有資料(為發行集),SQL B TABLE有資料(為訂閱者),進行複寫卻會出現如下錯誤。
      嘗試執行的命令:
      {call sp_MSsetconflicttable (N'TABLE_LOG', N'MSmerge_conflict_TABLELOG_TABLE_LOG', N'OTS-DEV-COOP', N'VO', N'TABLELOG')}

      錯誤訊息:
      接近 'IDX' 之處的語法不正確。 (來源: MSSQLServer,錯誤號碼: 102)
      取得說明: http://help/102
      合併處理無法將快照集傳遞給訂閱者。如果使用 Web 同步處理,可能是合併處理無法建立或寫入訊息檔。執行疑難排解時,請使用詳細資訊記錄來重新啟動同步處理,並指定寫入的輸出檔。 (來源: MSSQL_REPL,錯誤號碼: MSSQL_REPL-2147201001)
      取得說明: http://help/MSSQL_REPL-2147201001 (來源: MSSQL_REPL,錯誤號碼: MSSQL_REPL-2147201001)

      若要使用點對點合併式複寫,似乎也是不符合我的需求,必須先將SQL A進行完整備份然後還原到SQL B去,
      請問依照MS SQL Server的複寫方式,有符合我的需求方式的複寫嗎?

      回覆刪除
    12. 你好~ 請問這個訊息該如何解決? 不知高手是否有遇過?

      ===================================

      SQL Server 無法建立訂閱者 'PMS-SQL-VN' 的訂閱。 (設定點對點拓撲精靈)

      ===================================

      執行 Transact-SQL 陳述式或批次時發生例外狀況。 (Microsoft.SqlServer.ConnectionInfo)

      ------------------------------
      程式位置:

      於 Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      於 Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
      於 Microsoft.SqlServer.Replication.ReplicationObject.CommonCreate()
      於 Microsoft.SqlServer.Replication.Subscription.Create()
      於 Microsoft.SqlServer.Management.UI.CreatePeerToPeerWizard.CreatePushSubscription(P2PProgressItem actionItem, Boolean& subscriptionAlreadyExists)
      於 Microsoft.SqlServer.Management.UI.CreatePeerToPeerWizard.CreateP2PSubscription(P2PProgressItem actionItem, Int32 currentProgressRow)

      ===================================

      轉換 expression 到資料類型 nvarchar 時發生算術溢位錯誤。
      已將資料庫內容變更為 'wuerp_cn'。
      作業 'SQL-CN-wuerp_cn-ptp_brandseason-PMS-SQL-VN-75' 已成功啟動。
      警告: distribution 代理程式作業已經由隱含方式建立,將於 SQL Server Agent 服務帳戶中執行。
      作業 'SQL-CN-wuerp_cn-ptp_brandseason-PMS-SQL-VN-75' 已成功啟動。 (.Net SqlClient Data Provider)

      ------------------------------
      如需說明,請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=8115&LinkId=20476

      ------------------------------
      伺服器名稱: SQL-CN
      錯誤號碼: 8115
      嚴重性: 16
      狀態: 2
      程序: sp_MSsetupnosyncsubscriptionwithlsn
      行號: 237


      ------------------------------
      程式位置:

      於 Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
      於 Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

      回覆刪除
      回覆
      1. 對了 忘了補充
        這是我在處理點對點交易式複寫時 發生的錯誤

        刪除
    13. 你好,請問一個問題,我有兩台SQL SERVER
      A=MSSQL 2000
      B=MSSQL 2008

      A發行 B向A訂閱

      目前訂閱都沒甚麼問題

      有甚麼方法可以下Query 方式,知道每次同步異動是那些 Table 的哪幾筆資料。

      由於A Server Table 是早期 MRP 系統,且前端系統會判斷結構是否有被異動,因此 A Server 的 Table 是不允許變更,且Table 都沒有異動時間的欄位,因此無法從 Table 得知。

      而 B Server 需取得異動筆數進而後續處理。

      回覆刪除
    14. Caryhsu - 學無止盡: Sql Server 分散式架構 - 點對點交易式複寫 + Nlb >>>>> Download Now

      >>>>> Download Full

      Caryhsu - 學無止盡: Sql Server 分散式架構 - 點對點交易式複寫 + Nlb >>>>> Download LINK

      >>>>> Download Now

      Caryhsu - 學無止盡: Sql Server 分散式架構 - 點對點交易式複寫 + Nlb >>>>> Download Full

      >>>>> Download LINK

      回覆刪除