SQL Server 2008 R2數據管理新利器剖析

　　【IT168專稿】我們在SQL Server 2008 R2數據管理新紀元一文中介紹了SQL Server 2008 R2的管理利器——SQL Server Utility。這一次我們將深入剖析這一管理工具。

　　SQL Server Utility的架構

　　從SQL Server Utility的架構圖中可以看出SQL Server Utility的基礎是Utility Control Point。DBA可以在企業內創建多個SQL Server Utility，一個SQL Server Utility可以用於管理多個SQL Server實例。一個SQL Server實例僅能創建一個UCP，每個SQL Server Utility都必須而且僅能依賴於一個UCP。

　　DBA使用SQL Server Utility的大致工作流程是：

　　• 在某個SQL Server實例上創建UCP，用於宿主UCP的實例上會創建一個UCP用於存儲各類信息的數據倉庫(sysutility_mdw)，同時該實例會創建三個作業用於處理改數據倉庫中的數據：

　　o sysutility_get_views_data_into_cache_tables

　　o sysutility_get_cache_tables_data_into_aggregate_tables_hourly

　　o sysutility_get_cache_tables_data_into_aggregate_tables_daily

　　• 將需要被託管的SQL Server實例添加到UCP中，被託管實例上會創建一個作業用於收集並上載該被託管實例的配置及性能信息並在該實例的msdb資料庫中創建若干表及代碼對象：

　　o sysutility_mi_collect_and_upload

　　o sysutility_mi_collect_performance

　　• 被託管實例上的兩個作業每個15分鐘運行一次並將收集到的數據上載到UCP數據倉庫 (sysutility_mdw)。

　　• DBA通過Utility Explorer查看各項信息。

　　SQL Server Utility作業剖析

　　sysutility_mi_collect_performance

　　該作業在被託管實例上每隔15秒就會運行一次，這個作業調用的是位於MSDB資料庫中的sp_sysutility_mi_collect_dac_execution_statistics_internal存儲過程，該存儲過程會收集被託管DAC的性能數據，並上載到被託管實例的msdb資料庫中。

　　sysutility_mi_collect_and_upload

　　該作業每隔15分鐘才運行一次，這個作業調用的是一段PowerShell腳本，主要用於收集處理器性能統計數據及邏輯卷的容量信息，同樣這個作業收集到的數據都是傳向被託管實例的msdb資料庫。

　　該作業同時還負責調用msdb資料庫中的存儲過程sp_sysutility_mi_upload，該存儲過程會進一步調用msdb資料庫中的另一個系統存儲過程sp_syscollector_run_collection_set，而這個存儲過程一看便知道屬於SQL Server 2008 Data Collection功能所使用的(關於這個我們後面會有詳細的介紹)。

　　sysutility_get_views_data_into_cache_tables

　　該作業每隔15分鐘運行一次，主要任務是將數據從相關的性能數據從sysutility_ucp_staging架構複製到sysutility_ucp_core架構下，然後調用一段PowerShell腳本對性能數據進行評估，最後計算評估結果。

　　sysutility_get_cache_tables_data_into_aggregate_tables_hourly

　　該作業每個1小時運行一次，主要任務是對sysutility_ucp_core架構下的處理器及存儲空間利用數據進行聚合併存儲聚合結果。

　　sysutility_get_cache_tables_data_into_aggregate_tables_daily

　　該作業每天運行一次，其主要任務同上面一個任務相似，也是執行聚合，不過這次聚合的粒度不是小時，而是天，另外這個任務在聚合完成後還會清除過期的緩存數據及健康度評估數據(包括前一天的詳細數據、小時粒度的聚合數據以及一月前的天粒度聚合數據)。

　　SQL Server Utility與Data Collection

　　經過進一步的探索，我們會發現SQL Server Utility其實依賴於SQL Server 2008的一項功能——Data Collection。Data Collection對於已經接觸SQL Server 2008的DBA來說應該不陌生，因為微軟已經在SQL Server 2008發布的時候大肆宣傳過。不過當時僅有三個Collection Set，Disk Usage、Query Statistics及Server Activity。

　　不過在SQL Server 2008 R2中我們會發現一個新的Collection Set——Utility Information。沒錯，某些讀者可能已經猜到這個新的Collection Set與我們正在介紹的SQL Server Utility有著千絲萬縷的關係。

　　打開這個Collection Set我們就可以清楚地看到其中定義了一項Generic T-SQL類型的收集項目，主要是調用msdb中的若干存儲過程和數據表，例如sp_sysutility_mi_get_dac_execution_statistics_internal存儲過程。

　　繼續翻到這個Data Collection Set的Upload設置頁我們甚至還能發現sysutility_mdw數據倉庫在整個流程中的位置(怪不得我一直沒有在作業和存儲過程定義中找到UCP是如何將數據載入這個最終的數據倉庫中)。

　　了解了這些，DBA應該大致了解SQL Server Utility的工作流程了吧。當然，對於新接觸SQL Server 2008的DBA來說就應該去看看有關Data Collection Set的介紹了，相信對於理解SQL Server Utility的工作原理是非常有幫助的。

 

　　SQL Server Utility配置

　　SQL Server Utility的需求

　　DBA們也許已經注意到了，創建UCP需要一個SQL Server實例，而UCP又可以用於管理多個SQL Server實例。我們暫將用於創建UCP的實例成為UCP宿主實例，而將UCP管理的實例成為託管實例。

　　SQL Server Utility對UCP宿主實例以及UCP託管實例都是有一些限制條件的，分別是：

　　• UCP宿主實例以及UCP託管實例的SQL Server的版本必須在10.5以上。

　　• UCP宿主實例以及UCP託管實例都必須是資料庫引擎實例。

　　• UCP宿主實例以及UCP託管實例必須工作在同一個域或具備雙向信任關係的域內。

　　• UCP宿主實例以及UCP託管實例的SQL Server服務帳號都必須擁有對活動目錄中用戶對象的讀權限。

　　• 在Windows Server 2003中，SQL Server Agent服務帳號必須是Performance Monitor User組成員。

　　以上這些限制除第一條有些苛刻，第二條有些遺憾外，其他尚屬合理。

　　第一條限制明顯會限制SQL Server Utility的應用，10.5的版本號就意味著SQL Server Utility將完全無法支持SQL Server 2008 R2之外的版本，甚至是發布沒有多久的SQL Server 2008。

　　SQL Server Utility存儲消耗

　　正如許多管理工具一樣，UCP將收集到的數據存儲在一個數據倉庫中，這就會帶來存儲的問題。為了避免存儲空間耗盡導致SQL Server Utility停止工作甚至影響同一存儲設備上其他資料庫的情況發生，DBA需要考慮一下SQL Server Utility存儲消耗的問題。

　　在通常情況下，每個託管實例每年會在數據倉庫sysutility_mdw中消耗將近2GB的數據空間，同時每個託管實例還會在msdb中消耗將近20MB的數據空間。託管實例上的資料庫數量、DBA設置的資源利用評估策略會對這個數據產生明顯的影響。因此建議DBA需要在SQL Server Utility上限後的一至兩周觀察一下實際的存儲消耗速率。

　　創建UCP

　　說了這麼多，我們簡單展示下創建UCP的過程。

　　首先需要在SQL Server Management Studio中找到Utility Explorer，在Utility Explorer的工具欄中找到一個名為「Create Utility Control Point」的按鈕。

　　點擊這個按鈕後，SQL Server Management Studio就會啟動創建UCP的嚮導

　　點擊「Next」略過介紹頁，接著就需要選擇用於宿主UCP的SQL Server實例了，同時還需要提供一個UCP的名字，輸入這兩項內容後又可以點擊「Next」了。

　　

　　接著就是選帳戶了，DBA有兩個選擇，使用一個Domain Account或者利用現有的SQL Server Agent服務帳戶，如果是Domain Account那麼SQL Server會自動創建一個SQL Server Agent代理帳戶。

　　接著SQL Server會檢查創建UCP的各項條件，如果沒有問題的話，繼續點擊「Next」兩次就可以結束整個配置嚮導了。

　　令筆者汗的一個小問題是，筆者尚未發現有如何圖形化工具在某個UCP宿主實例上刪除UCP的註冊，因此大家體驗這一過程的時候注意一下吧。

　　將一個SQL Server實例加入為託管實例的嚮導與創建UCP的嚮導步驟類似，只是啟動加入託管實例的嚮導需要在「Managed Instance」節點上右擊。

　　結束語

　　如前一篇介紹SQL Serve Utility的文章一樣，要想更好的體驗SQL Server Utility，還是趕緊下載一個評估版自己偷著樂吧。