讓資料庫管理更直覺更容易　SQL Server 2008 DBA管理秘訣TOP5

全新出爐的SQL Server 2008在開發的管理上提供了許多的創新特色，筆者認為其中與DBA有關的五大特色最實用，不但簡單易學，而且能夠立即上手。這五大特色是原則集中控管（Policy-Based Management）、效能資料收集（Data Collector）、透通資料加密技術（Transparent Data Encryption）、備份壓縮（Backup Compression）以及資料壓縮（Data Compression）。接下來就一同來探討相關的使用秘訣。  

TOP1　SQL Server 2008原則集中控管技巧

Q：聽說SQL Server 2008新增了一個可以使用原則方式來集中管理所有SQL Server 2008執行個體、資料庫與其他物件各種設定的方法，可否詳細說明它的運作原理和操作方式？  

A：整個原則集中控管的流程是透過邏輯屬性（Facet）、條件的建立與設定、原則的建立以及管理目標的設定來完成。其中，管理目標的設定可以讓企業IT決定每一項原則所要套用的SQL Server 2008主機與執行個體。此外，建立好的原則也可以匯入到其他SQL Server 2008執行個體直接使用。  

在SQL Server 2008執行個體內的物件總管窗格中展開至「管理」→「原則管理」→「Facet」節點下，就會看到目前系統內建的70多種Facet項目，例如介面區組態、同義字、伺服器組態、伺服器設定、伺服器安全性、資料庫以及資料表等等。  

接著建立一個原則來測試看看，不過在此之前必須建立好所要套用的各種條件。在「條件」節點項目下按下滑鼠右鍵，然後點選快速選單中的【新增條件】，之後開啟如下頁圖所示的「建立新條件」頁面。在此先為這個新條件設定一個名稱，接著在「Facet」下拉選單中選取一個所要使用的邏輯組態，最後設定所要判斷偵測的Facet欄位與欄位值。

▲建立新條件

在本例中，筆者從Facet中選擇【資料庫維護】，然後選取「@RecoveryModel」以及等於Full的值，這表示要檢查的是，哪些資料庫的復原模式不是設定成「完成」。最後，按下〔確定〕按鈕完成設定。  

接著在「原則」節點上按下滑鼠右鍵，然後點選快速選單中的【新增原則】，隨後開啟一個新頁面，先輸入一個新原則名稱，然後在「檢查條件」下拉選單中選擇剛剛所建立的條件，並且選取所要使用的評估模式。而在「伺服器限制」設定中，則可以選取所要套用的伺服器條件設定。  

原則的評估模式可以區分為「視需要」、「按排程時間」、「變更時—避免」以及「變更時—僅限記錄」四種，不過，並不是每一種設定的檢查條件都有四種模式可以選擇，以範例中的資料庫復原模式條件來說，就只有「視需要」與「按排程時間」可選。以下說明這四種原則的使用時機。  

■視需要：讓系統管理員只設定好評估原則，等到需要查看原則評估結果時再隨時手動執行。
■按排程時間：藉由加入到SQL Server Agent的排程作業管理中，來定期執行此評估原則，而在此模式下會自動記錄原則違規的紀錄。
■變更時—避免：即時監控與透過結合的DDL觸發程序，來防止使用者建立、設定或修改違反原則的組態。
■變更時—僅限記錄：即時監控與針對違反原則的各項設定進行違規記錄，並且可以透過事件通知的方式告知系統管理員。  

建立好條件與新原則之後，展開至「原則管理」→「原則」節點上，針對該原則項目按下滑鼠右鍵，然後點選快速選單中的【評估】，接著將會開啟如下圖所示的「評估原則」頁面，可以看到目前管理的SQL Server 2008執行個體中，有哪些現有的資料庫不符合在原則中所設定的評估條件。點選「檢視」連結，則可以查看詳細資訊。

▲檢視原則評估結果

評估結果若不符合原則所定義的條件時，該怎麼辦呢？以資料庫的復原模式設定為例，在不符合規定的資料庫項目上按下滑鼠右鍵，並點選快速選單中的【內容】，然後切換到「選項」頁面內，即可發現並修改目前的「復原模式」設定了。  

TOP2　SQL Server 2008效能資料收集的使用方法  

Q：在針對SQL Server主機各項運作效能的監控上，若分析某些查詢式的歷史效能表現等等，以往都必須透過作業系統的效能監視器與SQL Server Profiler進行監看，這樣的管理方法不僅沒有效率，在面對公司內有眾多SQL Server需要一併監控與分析時更是難以管理，因此我想知道在全新的SQL Server 2008中是否提供更有效率的效能監控與分析？  

A：SQL Server 2008提供了一項效能資料收集（Data Collector）功能，除了Express與Express Advanced這兩個免費版本沒有內建之外，其他版本內都有。該項功能可以將多部SQL Server中的伺服器活動紀錄、磁碟使用量摘要、查詢統計資料紀錄相關資訊收集到指定的資料庫中進行分析，並且自動產生所需的三種中文分析報表。接下來就來看看這項功能的使用方法。  

首先開啟SQL Server Management Studio介面，然後進入「物件總管」窗格內，切換到「管理」→「資料收集」項目節點上並按下滑鼠右鍵，然後點選快速選單中的【設定管理資料倉儲】。接著將開啟「選取組態工作」頁面，由於本例是第一次執行此設定，因此選取「建立或升級管理資料倉儲」項目，然後按下〔下一步〕按鈕繼續。  

隨後進入「設定管理資料倉儲儲存體」頁面，如下圖所示先按下〔新增〕按鈕，新增一個用來存放效能收集資料的獨立資料庫（可任意命名），本例命名為「DataWarehouse」，然後按一下〔下一步〕按鈕。

▲設定管理資料倉儲儲存體

接下來，在「對應登入及使用者」頁面中，設定相關使用者對於管理資料倉儲的角色權限，包括讀取權限、寫入權限以及完整權限的設定。有一點必須注意的是，如果使用的是中文版，下方的「資料庫角色成員資格對象」窗格可能會緊縮在一起而無法操作，這是SQL Server 2008的Bug，必須等到新的修正程式出來之後才能夠解決，讀者可以暫時利用Tab與空白鍵按鍵來瞎選試試，隨後按下〔下一步〕按鈕繼續。最後在「完成精靈」頁面中檢視前面所做的全部設定，確認無誤之後按一下〔完成〕按鈕即可，若需要回頭修改，則按下〔上一步〕按鈕。  

建立好資料收集管理中的資料倉儲資料庫之後，緊接著如下圖所示重新執行「設定管理資料倉儲精靈」，在「選取組態工作」頁面中改選「設定資料收集」，然後按下〔下一步〕按鈕。

▲選取設定資料收集

接著在「設定管理資料倉儲儲存體」頁面中，分別選取之前所建立的資料倉儲資料庫的SQL Server執行個體與資料庫名稱，然後在本機任一路徑中先建立好一個用來存放快取檔案的空資料夾，並且在此頁面中的「快取目錄」內完成指定。設定好了之後，按下〔下一步〕按鈕。  

完成以上步驟的設定之後，接下來展開至「管理」→「資料收集」→「系統資料收集組」節點，將會看到系統自動建立好的三個資料收集的項目類別：「伺服器活動」、「查詢統計資料記錄」和「磁碟使用量記錄」，可以針對任一類別項目按下滑鼠右鍵，然後點選快速選單中的【屬性】來查看相關屬性設定。  

開啟「資料收集組屬性設定」頁面，便可以設定資料收集和上傳的方法，例如選取「無快取」然後設定收集和上傳資料的排程。此外，還可以設定這些分析資料在資料倉儲資料庫中的保存期限（預設值是14天）。由於前面的設定選取「無快取」，所以在接下來的「挑選作業排程」頁面中便可以選取所要進行資料收集與上傳的排程方式。  

自訂好資料收集與上傳的排程時間之後，展開「SQL Server Agent」→「作業」節點，便能夠看到前面建立好的每一個排程作業，為了讓資料的收集與上傳正常運作，務必確認SQL Server Agent的服務正持續執行中。  

設定好一段時間之後，只要資料收集與上傳的排程作業項目已經成功執行過，接下來就可以查看前面所提到的三種報表。查看的方法有兩種，第一種操作方法如下圖所示，先在指定為資料收集與上傳的資料庫項目上按下滑鼠右鍵，然後依序點選快速選單中的【報表】→【管理資料倉儲】→【管理資料倉儲概觀】。

▲開啟管理資料倉儲概觀

執行之後，將開啟「管理資料倉儲概觀」的HTML格式頁面，根據已執行過的排程作業，此處提供了三種不同的報告項目連結以供點選，先點選「查詢統計資料記錄」中的排程連結作為範例。  

隨後會出現一個「查詢統計資料記錄」報告頁面，其中顯示出不同查詢（Query）所佔用的各種系統資源分析圖表，如果想針對特定的查詢式來檢視更詳細的執行效能分析資訊，點選該查詢式的超連結即可。  

若進一步查看特定的查詢式執行的運作效能，可以得知每次執行時的平均CPU時間、每次執行時的平均持續時間、每次執行時的平均實體讀取次數，以及每次執行時的平均邏輯寫入次數等等。  

至於第二種查看資料收集報表的方法，可以如下頁圖所示展開至「管理」→「資料收集」項目節點上並按下滑鼠右鍵，然後依序點選快速選單中的【報表】→【管理資料倉儲】，即可檢視【伺服器活動記錄】、【磁碟使用量摘要」與【查詢統計資料記錄】。  

以點選【磁碟使用量摘要】項目為例，如果目前已經收集到資料，將會出現類似範例中的分析圖表，顯示目前所有資料庫資料檔與交易紀錄檔的大小、成長趨勢、平均成長大小等資訊。

▲磁碟使用量摘要

TOP3　使用SQL Server 2008透通資料加密技術保護資料庫安全

Q：我們公司有許多營運的重要應用系統資料庫，打算將它移轉到全新的SQL Server 2008上運作，請問SOL Server 2008是否有什麼安全功能，可以解決因資料庫遭竊而導致機密資料外洩的問題？  

A：根據以上的問題，可以得知讀者最需要的是一項與資料庫加密有關的解決方案。SQL Server 2008提供的全新透通資料加密（Transparent Data Encryption，TDE）技術，讓整合於它的前端應用程式毋須進行任何原始碼的修改，即可透過伺服器憑證加密機制來保護資料庫的安全。  

這項伺服器憑證並不需要藉由架設Windows Server所提供的CA憑證授權單位來核發，而是由SQL Server 2008系統本身來產生。這項技術可以有效避開可能因資料庫遺失或被竊所導致的商業風險。  

接下來，說明如何藉由SQL Server 2008所提供的幾個簡單步驟來防患未然。首先在SQL Server Management Studio管理介面中開啟一個新的查詢頁面，然後以下列程式碼範例建立SQL Server執行個體的主要金鑰和伺服器憑證。

CREATE MASTER KEY ENCRYPTION BY PASSWORD='輸入符合複雜度要求的密碼' CREATE CERTIFICATE 新憑證名稱 WITH SUBJECT='輸入憑證描述說明'

建立主要金鑰與加密用憑證設定之後，接著在所要進行加密的資料庫項目上按下滑鼠右鍵，然後點選快速選單中的【工作】→【管理資料庫加密】。  

開啟「管理資料庫加密」頁面後，設定加密演算法組態，可以選擇的加密演算法有AES 128或3DES。然後，在「使用伺服器憑證」下拉選單中選擇之前所建立的伺服器憑證名稱，最後勾選下方的「將資料庫加密設為開啟」選項。  

完成管理資料庫加密設定之後，建議再次開啟「管理資料庫加密」，然後切換到「屬性」頁面中，這裡會顯示目前資料庫加密的狀態、加密建立的日期時間、加密的演算法則、加密的類型以及加密的憑證名稱等等。  

接下來測試一下加密後的結果為何。先將這個加密過的資料卸載，然後將它複製到另一個SQL Server 2008執行個體的主機中，並嘗試以圖形介面的操作方式將它附加上來試試。如果沒有意外，將會出現如下圖所示的錯誤訊息。

▲無法附加已加密的資料庫

接下來，可以試著改用命令的方式來附加這個離線的資料庫檔案，以CREATE DATABASE命令語法指定所要附加的資料庫資料檔案（MDF）與交易記錄檔案（LDF）。執行之後，一樣會出現相同的錯誤訊息。  

為什麼執行加密資料庫的附加動作時會出現上述的錯誤訊息呢？因為在這個SQL Server 2008的執行個體中，沒有相對的主要金鑰（Master Key）與伺服器憑證。  

想要解決上述無法附加資料庫的問題，必須在這個SQL Server 2008的執行個體上，建立主要金鑰與匯入伺服器憑證以及相對應的私密金鑰。要特別注意的是，如果沒有連同私密金鑰一併匯入，將會出現金鑰已損壞的錯誤訊息。  

既然需要相對的伺服器憑證與私密金鑰，才能夠成功附加或還原加密的資料庫，那麼就必須如下圖所示在原來的SQL Server 2008執行個體查詢頁面中，下達「Backup Certificate 憑證名稱 TO FILE... With Private Key」語法，將憑證與私密金鑰匯出到指定的路徑中，並且在敘述中設定一串密碼來保護這個檔案。

▲匯出伺服器憑證與私密金鑰

成功匯出伺服器憑證與私密金鑰之後，便可以拿著這個檔案到另一部新的SQL Server 2008執行個體主機上。如下圖所示，先在查詢頁面中執行建立主要金鑰，接著下達CREATE CERTIFICATE語法，指定剛剛匯出的憑證檔案以及私密金鑰儲存路徑，並且輸入正確的保護密碼。完成以上的操作之後，再試試對於加密資料庫的附加動作，結果就會成功了。

▲建立主要金鑰與匯入伺服器憑證

TOP4　使用備份壓縮來提升備份效率降低空間需求  

Q：聽說全新的SQL Server 2008資料庫備份技術，善用了目前多核心CPU架構的特性，只須利用10%的處理資源便可以讓資料庫的備份時間比以往大幅縮減45%，而且備份壓縮後的檔案只有原來大小的25%左右。請問該如何實作這ㄧ項好用的功能呢？  

A：這項功能的確是SQL Server在資料庫備份管理上的一大突破，筆者實際測試後也發現，確實可以達到官方所提供的壓縮數據。使用此功能時，必須先啟用SQL Server 2008執行個體中的這項功能。

▲伺服器屬性設定

啟用的方法有兩種，第一種是透過SQL Management Studio圖形介面，在執行個體的項目節點上按下滑鼠右鍵，然後點選快速選單中的【屬性】，接著將會開啟如上頁圖所示的頁面，勾選「壓縮備份」選項即可。  

第二種方法則是利用下方的T-SQL語法，透過系統的預存程序sp_configure進行修改即可。當我們在SQL Server 2008的伺服器組態中啟用備份壓縮功能之後，便可以針對個別的資料庫備份計畫，決定是否採用備份壓縮的機制。請注意！備份壓縮的功能也支援儲存在備份磁碟或磁帶上。

/*以T-SQL語法啟用SQL Server 2008備份壓縮功能 USE master; GO EXEC sp_configure ‘backup compression default’, '1'; RECONFIGURE WITH OVERRIDE;

接著實際測試一下資料庫備份壓縮的能力。執行的方法也有兩種，第一種方式是在SQL Management Studio圖形介面中的特定資料庫項目上按下滑鼠右鍵，然後點選快速選單中的【工作】→【備份】。接著將開啟「備份資料庫」頁面，如下圖所示會多出「壓縮」區域中的設定功能，從「設定備份壓縮」下拉選單中可以選擇壓縮備份的設定，請選擇【使用預設伺服器設定】或【壓縮備份】。

▲資料庫備份壓縮設定

/*以T-SQL語法啟用AdventureWorks資料庫備份時的壓縮 機制 BACKUP DATABASE AdventureWorks TO DISK='Z:\ SQLServerBackups\AdvWorksData.bak' WITH FORMAT,COMPRESSION

如下圖所示其中的兩個資料庫的備份檔案，一個是採用一般傳統的方式備份得到的結果（Adventure_DB），另一個則是採用最新的資料庫備份壓縮技術所產生的結果（Adventure_DB_Compress），經壓縮後的備份檔只有原來的四分之一。

▲資料庫壓縮前後的大小比較

TOP5　使用SQL Server 2008的資料壓縮功能  

Q：根據瞭解最新的SQL Server 2008企業版本，除了提供備份壓縮功能來節省備份檔案的儲存空間之外，也提供了針對現有線上資料庫進行資料壓縮的功能，可以大幅節省線上資料庫的資料檔案大小，請問該如何正確使用這項功能呢？  

A：資料的壓縮功能，是SQL Server 2008企業版中提供的另一項新的壓縮特色，系統管理員可以透過命令或圖形操作介面，針對現有的資料庫資料或新建的資料表來設定壓縮機制的啟用。而對於現有線上的資料庫資料，還可以在資料壓縮執行之前先評估壓縮後可能造成的結果大小。接下來，先介紹命令方式的設定方法。  

先以「sp_estimate_data_compression_savings」預存程序的執行方式，查看針對指定資料表的壓縮評估，包括堆積、叢集索引、非叢集索引、索引檢視表以及資料表和索引資料分割。如果資料表、索引或資料分割已經壓縮，可以使用此預存程序來評估未壓縮的資料表、索引或資料分割的大小。關於它的使用語法範例如下所示，其中壓縮類型（data_compression）的設定值可以設定為NONE、ROW或PAGE，以決定資料的壓縮評估要採用資料列或是資料頁的方式。

sp_estimate_data_compression_savings   　[ @schema_name =] 'schema_name' 　, [ @object_name =] 'object_name'    　 , [@index_id =] index_id 　 , [@partition_number =] partition_number 　    , [@data_compression =] 'data_compression'

如果執行「sp_estimate_data_compression_savings」後的結果顯示，資料表將會成長，就表示許多資料列都是使用完整有效位數的資料類型，而且壓縮格式所需的小型負擔增加，會比壓縮的空間節省更大。在這種情況下請勿啟用壓縮功能。  

而針對準備新增的資料表，該如何一併啟用資料壓縮的功能呢？只要在以CREATE TABLE語法建立資料表的設定中加入「WITH(DATA_COMPRESSION=ROW)」即可，這表示同時針對此新資料表啟用資料列的壓縮功能。如果想針對現有的資料表或索引壓縮進行設定的變更，則可以透過ALTER TABLE與ALTER INDEX命令語法。舉例來說，如果想針對一個名為T1的現有資料表設定資料頁的壓縮，則輸入「ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE);」。  

如果想針對現有的線上資料庫資料進行評估與壓縮，並且不希望透過下達命令的方式來完成，也可以經由圖形介面所提供的精靈工具來設定。設定時，在所要壓縮的資料表項目上按下滑鼠右鍵，然後點選快速選單中的【儲存體】→【管理壓縮】。  

接下來會開啟如下圖所示的「選取壓縮類型」頁面，先從下拉選單中選擇壓縮的類型（None、Row或Page），然後按下〔計算〕按鈕即可得知可能的壓縮結果，如果評估之後確認要壓縮，則按下〔下一步〕按鈕。

▲選取壓縮類型

然後進入「選取輸出選項」頁面中，選擇執行「建立指令碼」、「立即執行」或「排程」。「建立指令碼」可以協助產生針對此資料壓縮的SQL程式碼至檔案、剪貼簿或是新增的查詢視窗中，方便修改。  

選擇「立即執行」，可以讓決定的壓縮類型立刻被套用。如果設定為「排程」，將設定執行排程的時間。在此為這個資料壓縮作業，設定一個定期執行壓縮的時間，或是設定一個單一次執行的時間點。  

往後若想得哪些資料表已經啟用壓縮時，只要在所要檢視的資料表項目上按下滑鼠右鍵，然後點選快速選單中的【屬性】。開啟「資料表屬性」頁面之後，切換到「儲存體」頁面，就可以在「壓縮」區域中看到目前資料壓縮所採用的壓縮類型。