前段時間把公司的主數據庫切了，分成業務庫和報表庫，業務庫向報表庫進行實時的Replication。這個項目的上線提升了系統的性能和可維護性，現在把設計時的考量和所做的工作重新回顧一下，作為備忘。

　　項目起源

　　在日常的開發過程中，功能總是先于性能被考慮。只有當用戶抱怨系統性能時，我們才開始頭痛醫頭，腳痛醫腳地來解決這些性能問題。

　　公司的CRM和ERP系統叫作Olite，完全是我們組開發的。從無到有，功能不斷擴展，原先只有CRM模塊，后來加入了ERP模塊，Accounting功能和Report功能。近來出現的情況是當某些用戶跑一個大Report時，正在進行業務操作的用戶感覺系統響應非常慢。通過對系統的性能監視發現，在這些時刻，數據庫中產生了大量的鎖，同時服務器上出現了CPU和內存資源消耗的尖峰。

　　系統結構

　　性能問題源于系統的整體結構和發展過程。Olite系統的Application是基于.NET平臺的Web Form程序，數據庫為SQL Server 2005。其主體結構如下圖所示：

　　其Application端包括兩個網站：OliteBase和OliteReport，但連接的都是同一個數據庫。

　　Olite的Application端其實很薄，而把大量的業務邏輯包裝在存儲過程中，放在數據庫端來運行。

　　這種結構在起初性能很好，而且提供給用戶的Report是實時的業務數據。但隨著提供的業務模塊，特別是Report的增多（Report對應的存儲過程連接的表多，計算量大，輸出的結果集大），數據庫就成為了瓶頸。

　　首先，我們做了存儲過程的優化，通過創建Trace捕獲性能差的存儲過程，并對其進行優化。我們這么做了一段時間，但獲得的收效并不大。我們在優化以往存儲過程的同時，隨著系統新功能的上線，又有新的存儲過程進入需要優化的列表中。

　　其次，修改數據庫設計，其中包括修改表結構和優化索引。在系統局部重構表結構與關系對于性能的提升還是比較明顯的，但這樣的修改會造成Application端的大量修改，工作量大，風險大，所以不能大規模實施。對于索引優化又存在矛盾，業務模塊（OliteBase）要求數據庫中的索引不要太多，以支持高效的插入、修改和刪除，而報表模塊（OliteReport）則希望在數據庫中有更多的索引，以支持高效讀。

　　最后，我們還試圖提供晚一天的Report服務，來分流主數據庫的壓力。每天通過把前一天的備份數據庫恢復在另一臺服務器上，并在此服務器上提供OliteReport2站點，給用戶提供Report服務。但用戶并不喜歡使用OliteReport2，原因分析下來有3個方面：其一，有時用戶確實需要實時的Report。其二，OliteReport能存儲用戶的Report條件，而OliteReport2由于每天都會被刷新，無法保留這些條件。其三，用戶更習慣打開原來的Report鏈接。

　　項目需求

　　上述的各種優化方案都沒有根本性的解決系統的性能問題。在這種的背景下我們有了把報表數據庫與業務數據庫分離的想法。

　　此項目的需求：

　　1. 提高用戶對整個系統性能的感受，Report模塊不要影響到業務模塊的運行。

　　2. 用戶可以和原先一樣使用Report模塊，即不增加新的Report站點。

　　3. 用戶可以和原先一樣存儲填寫的Report條件，以供重復使用。

　　4. 盡可能提供最小延時的Report。

　　需求1是這個項目的主要目標，需求2、3、4是盡可能保證項目所帶來的改變對用戶是透明的。

　　方案選擇

　　對于原來的系統結構，其Application端已經是兩個獨立的站點OliteBase和OliteReport。所以只要把OliteBase和OliteReport的數據庫進行分離，在分離后的兩數據庫間進行數據的同步就行了。這里的關鍵在于如何進行數據庫間的同步。

微軟提供了很多種數據同步的選擇：1.集群；2.Log Shipping；3.Replication；4.Mirror；5.Integration Service。

　　微軟提供的這些方案中大部分都是用于做數據庫的高可用性的，而我們的項目是以高性能為目標的。為了滿足我們自己的需求，應選擇那種方案，并做哪些修改呢？

　　1.集群

　　這是第一個被我們否決的方案。配置SQL Server數據庫集群，對硬件有較多限制，而且配置相對其他方案復雜。我們的項目總共的服務器資源就兩臺，除原先主數據庫服務器外，另一臺是虛擬機。

　　2.Log Shipping

　　Log Shipping把主數據庫的日志傳送到從數據庫，并在從數據庫上進行回放來保證主、從數據庫間數據的一致，從數據庫為只讀。Log Shipping而且還有配置簡單的特點，開始時是我們的一個候選方案，但在進一步的實驗過程中發現了兩個問題。第一、Log Shipping可設置的時間間隔最小單位為分鐘。第二、當從數據庫進行日志回放時，連接此數據庫的連接需要被斷開。其中第二個問題是難以容忍的，這個方案也被淘汰了。

　　3.Replication

　　Replication的原理和Log Shipping有些相似，但其提供了更多的靈活性。Replication可以只多主數據庫的一些表、函數或存儲過程進行，甚至可以對某些符合條件的記錄進行。除此之外，其復制出來的數據庫可寫，而且復制的最小時間間隔可配置為concurrent（測試下來的時間延遲為秒級別），而且其配置也較為簡單。經過一些實驗，我們最后選擇了它。后面會對其原理和配置進一步討論。

　　4.Mirror

　　Mirror是SQL Server 2005提供的強大的高可用性方案。其鏡像數據庫不能直接讀取，這和我們的需求場景不符合，所以被否了。

　　5.Integration Service

　　Integration Service具有最大的靈活性，其可以為數據倉庫進行數據抽取，轉換和裝載。但使用Integration Service需要有大量的開發與測試工作，所以我們也沒選用。

　　Replication方案細分

　　Replication方案又可以分為Snapshot Replication, Transactional Replication, Peer-2-Peer Replication, Merge Replication。

　　Snapshot Replication：一般用于對于數據庫的一次性的完全復制。

　　Transactional Replication：用于主數據庫向從數據庫的單向復制。

　　Peer-2-Peer Replication：能進行二個或多個數據庫之間的互相復制，即從數據庫也能向主數據庫復制，這個功能很強大，但可能會引起沖突，需要特別關注保證各庫的數據完整性。

　　Merge Replication：可以把多個數據庫中的數據進行合并后，復制到目標數據庫。

　　對于我們的需求，我們選用了最單純的Transactional Replication。

　　Transactional Replication原理 

　　在Transactional Replication中有3個角色：Publisher（發布者）, Distributor（分發者）, Subscriber（訂閱者）。其邏輯圖如下：

　　在進行增量的Transactional Replication之前，Subscriber需要進行初始化，使其包含和Publisher一樣的表結構和初始數據。

　　Transactional Replication啟動之后，Distributor上的Log Reader Agent會將讀取Publisher的Log信息，并分揀出被標識為replication的INSERT, UPDATE, DELETE語句。此后復制這些Transaction到Distributor，并寫入distribution數據庫。最后Distribution Agent把Distributor上的Transaction運送到Subscriber進行重放。

　　注意：在圖中Distribution Agent運行在Distributor上，這是在push（推）模式下的情況。可以配置為pull（拉）模式，Distribution Agent將運行在Subscriber上。

更多關于Transactional Replication的原理可參考：

http://msdn.microsoft.com/en-us/library/ms151706(SQL.90).ASPx

　　項目中的配置與考量

　　在前文的系統結構小節，給出了原先的系統結構。我們希望通過這次的項目得到如下所示的系統結構：

　　OliteReport能連接到一個由主數據庫復制出的單獨數據庫上，這樣這兩個庫之間的鎖就被隔離了。同時主數據庫與從數據庫安排在兩臺服務器上（項目中我們把復制出的數據庫放在了一臺虛擬機上），那么CPU資源與內存資源的消耗也被隔離了。需要注意的是圖中OliteReport除了主要的讀操作外，還有少量的寫操作（這是因為用戶可以存儲Report條件）。我們把這些寫指回主數據庫，從數據庫在下一時刻的復制中得到這些數據。

　　在Transactional Replication中有三個邏輯角色，而項目中只有兩臺服務器。我們如何來安排這三個邏輯角色呢？

• 候選的方案有兩種：1.主數據庫上配置Publisher和Distributor，從數據庫上配置Subscriber；2.主數據庫上只配置Publisher，從數據庫上配置Distributor和Subscriber。矛盾的焦點是Distributor放哪里？需要指出的是Distributor對于Replication非常重要，這個角色承擔著從主數據庫抓取Transaction的工作，在Push模式下，它還需要負責把Transaction推送到個Subscriber。這些工作都會消耗所在服務器的CPU和內存資源。我們的項目希望盡可能保證業務模塊的性能，所以我們選用了方案2，把Distributor配置在從數據庫上。

　　我們是選用Push模式還是Pull模式呢？

• Push和Pull其實是針對Distributor傳送Transaction到Subscriber的方式而言的（這點我是很后面才認識到的，開始一直認為Push或Pull會影響Distributor抓取Publisher上的信息，其實不然）。對于Distributor和Subscriber在一臺服務器上，這兩種模式的效果基本一樣。我們選擇了Pull模式，即Distribution Agent運行在Subscriber端從Distributor拉Transaction數據。這是為了將來擴展考慮，如果以后再加一臺服務器來作為Subscriber時，Distributor不會增加太多的性能壓力。

　　另一個需要考慮的問題是復制些什么？

• Transactional Replication可以選擇復制哪些表、存儲過程或函數等內容。最簡單的是把整個數據庫中的所以元素都進行復制，但這會造成Replication服務所要監視的對象很多，同時網絡上傳輸的信息量也很大。項目中我們最后決定只復制所有的表，這樣做是出去性能的考慮。這樣做會對將來的release產生影響，需要注意，下文會進行討論。

　　還有一個需要考慮的是如何進行從數據庫的初始化？

• 在Transactional Replication開始之前，首先要對從數據庫進行初始化，使其獲得與主數據庫一致的表結構和初始數據。在配置Transactional Replication中會有一個選項來進行初始化（由Snapshot Agent完成）。但在我們的實驗中初始化耗費了幾個小時，所以我們沒有使用Transactional Replication默認的初始化方式，而是通過數據庫備份還原來完成初始化，要這樣做就需要改變配置的一些選項，下文還會涉及。

　　Transactional Replication有些什么前提條件？

• 數據庫的Compatibility level（兼容性等級）需要達到SQL Server 2005(90)（我們使用的是SQL Server 2005，當兼容性級別為80時，配置過程中會出現異常）。
• 數據庫的Recovery model（恢復模式）需要是Full（完整）。
• 所有需要Replicate的表必須具有主鍵。（這應該是理所當然的，但在這次配置中竟然發現一些非常“可恥”的東西）
• 存儲過程或其他腳本中，不能對進行Replicate的表進行truncate，需把相應存儲過程中的語句改為delete。這是因為Replication是基于對Log的抓取與解析，但truncate不產生Log。
• 如果Replicate的元素還包括存儲過程或函數，還會有其他一些前提條件，我們不在這里展開，可以查看msdn。

　　如何來配置Transactional Replication？

• 微軟提供了非常易用的圖形化界面可以進行Replication的配置。但圖形化配置的靈活性是有限的，有些配置選項在圖形化界面下無法完成。我的建議是先用圖形化配置Replication，并生成相應的script。此后根據需求修改script，并用script進行配置。在我們的項目中也是這么做的。
• 默認的情況下，Distributor服務器的D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/repldata會存放replicate數據。由于我們要支持Pull模式，需要共享這一文件夾，并給此文件夾設置一個具有Full Control權限的域賬戶。并把此域賬戶設置為Subscriber服務器上SQL Server Agent服務的運行賬戶，此服務同時需要被設為Automatic啟動方式。在sp_adddistpublisher的@working_directory參數設為此共享目錄的網絡路徑。
• 配置Publisher時，sp_addpublication的參數@sync_method = N'concurrent', @repl_freq = N'continuous'保證了Replicate能盡可能實時；@allow_initialize_from_backup = N’true’表示通過備份還原來進行從數據庫的初始化。
• 配置Subscriber時，sp_addsubscription的參數@sync_type = N'replication support only'表示從數據庫的初始化完全由外部來完成；@subscription_type = N'pull'表示使用拉模式。

　　后期維護

　　如何監視Replication的性能與異常？

• 微軟提供了Replication Monitor。這個工具還是比較好用的，可以查看到Publication和Subscription的狀態，還能查看到當前有多少Transaction等待傳送。
• Transactional Replication設置好后，Distributor上將自動生成相關的多個Alerts，如：Replication Warning: Subscription expiration (Threshold: expiration)，Replication Warning: Transactional replication latency (Threshold: latency)等。可以將這些Alerts與Database Mail進行綁定。當出現警告時，自動發出郵件。（此功能雖然在項目中配置了，但從未正常發出警告郵件，一直不知道為什么，如果有人知道的話可以聯系我）。

　　如何進行以后的Release？

• 原先數據庫的Release一般會分為三部分：1.表結構的變化（包括加/刪表，加/刪列）；2.配置數據的裝載（如添加新功能的配置數據）；3.刷函數與存儲過程腳本。
• 對于本項目中的Replication數據庫，在Release過程中需注意以下幾點：1.若新加的表需要進行Replication，除了在主數據庫創建表之外，還需配置此表進行Replicate，并進行初始化。2.若要刪除某處于Replication的表，需先取消此表的Replication，再在主、從庫中drop此表。3.若需要加/刪Replication表的列（此列不能為主鍵列）時，可以直接在主數據庫上執行腳本，變化會自動Replicate到從數據庫。4.配置數據的裝載也只需要在主數據庫完成。5.函數與存儲過程需要在主、從庫上都進行刷新。

　　總結與設想

　　此項目已經上線，基本達到了需求所提出的目標，但這只是開始，優化后的結構給將來系統的擴展提供了一個基礎。

• 通過實驗發現，在主/從數據庫上可以創建不同的索引而不互相干擾（這和Replication的配置相關）。這就可以根據主、從數據庫不同的使用模式，創建更優化的索引。我曾在國外某Blog上看到，利用SQL Server 2005的動態視圖，自動根據數據庫的使用模式來創建索引，就像自適應索引機一樣。這也是我將在OliteReport數據庫上做的事。
• 將來如果有了多個Subscriber數據庫，還能做OliteReport的數據庫Load Balance。當有Report請求時，系統首先查看各個Subscriber的CPU和Memory的Load，選擇Load較輕的Subscriber接受Report請求。
• 我們還能利用Replicate出的數據庫進行BI（商業智能）分析與挖掘，而不會影響到主數據庫的運行。

it知識庫：SQL Server性能調優——報表數據庫與業務數據庫分離，轉載需保留來源！

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。