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

2009-02-09
資料庫中心是企業IT營運的重地,因此它的安全性、穩定性、可用性、彈性的部署與管理能力,肯定無法被忽略。全新的SQL Server 2008便針對以上這些嚴苛的要求提供了許多更易於控管的能力。
全新出爐的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程式碼至檔案、剪貼簿或是新增的查詢視窗中,方便修改。  

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

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


追蹤我們Featrue us

本站使用cookie及相關技術分析來改善使用者體驗。瞭解更多

我知道了!