SQL 2008五大管理秘诀

SQL Server 2008从开发到管理皆提供了琳琅满目的全新特色，以同样是一位身兼数据库管理师的笔者来说，对于其中与DBA有关的五大全新特色认为最为实用，并且也是简单易学以及马上就可以立即上手，这五大全新特色分别是策略集中控管（Policy-Based Management）、性能资料收集（Data Collector）、透明数据加密技术（Transparent Data Encryption）、备份压缩（Backup Compression）以及数据压缩（Data Compression）。接下来废话不多说，马上让我们一同看看相关的使用秘诀吧。

秘诀1 SQL Server 2008策略集中控管技巧

在SQL Server 2008中新增了一个可以使用策略方式，来集中管理所有SQL Server 2008实例(Instance)、数据库、其它对象各种设定的方法，下面就这一项功能详细说明它的运行原理以及操作方法。

整个策略的集中控管的流程是通过逻辑属性（Facet）、条件的建立与设定、策略的建立以及管理目标的设定来完成。其中管理目标的设定便可以让企业IT，来决定每一项策略所要套用的SQL Server 2008主机与实例，此外我们所建立好的策略也可以汇入到其它SQL Server 2008实例来直接使用。

在SQL Server 2008实例中，我们可以在对象总管的窗格中展开至[管理][策略管理][Facet]节点下，看到目前所有系统内建可以选择的70多种Facet项目，例如接口区组态、同义字、服务器组态、服务器设定、服务器安全性、数据库已收集数据表等等。

接下来就让我们来建立一个策略来测试看看吧，不过在此之前可别忘了必须建立好所要套用的各种条件。请在[条件]节点项目下按下鼠标右键点选[新增条件]，执行之后将会开启[建立新条件]的页面。在此您必须先为这新条件设定一个名称，然后在[Facet]下拉选单中选取一个要使用的逻辑组态，最后便需要设定所要判断侦测的Facet字段以及域值。

在这里笔者分别从Facet中先选择了[数据库维护]，然后在字段中选取了[@RecoveryModel]以及等于Full的值，这样表示我们要检查的是哪一些数据库的恢复模式不是设定成[完成]，点选[确定]完成设定。

接下来请在[策略]节点上按下鼠标右键点选[新增策略]，执行后将会开启如图所示的页面，在此您必须先输入一个新策略名称，然后在[检查条件]的下拉选单中选取刚刚我们所建立的条件，并且选取所要使用的评估模式。至于在[服务器限制]的设定中则是可以让我们选取所要套用的服务器条件设定。

有关于策略的评估模式可以区分为「视需要」、「按排程时间」、「变更时-避免」以及「变更时-仅限记录」四种，不过这并非每一种所设定的检查条件都可以有四种模式可以选择，以范例中的数据库恢复模式条件来说，便只会有「视需要」与「按排程时间」可以选择。以下说明这四种策略的使用时机。

视需要：让系统管理员只设定好评估策略，等到需要查看策略评估结果时再来随时手动执行。

按排程时间：藉由加入到SQL Server Agent的排程作业管理中，来定期执行此评估策略，而在此模式下会自动记录策略违规的记录。

变更时-避免：实时监控与通过结合的DDL触发程序来防止用户建立、设定或修改违反策略的组态。

变更时-仅限记录：实时监控与针对违反策略的各项设定进行违规记录，并且可通过事件通知的方式来让系统管理员知道。

在完成了条件与新策略的建立之后，请展开至[策略管理][策略]节点上，针对该策略项目按下鼠标右键点选择[评估]继续。紧接着将会开启如图1所示的[评估策略]页面，在此您将可以看到在目前所管理的SQL Server 2008实例中，有哪一些现有数据库是不符合我们在策略中所设定的评估条件的。对于它们的详细信息可以点选相对的[检视]连结来查看。

评估结果对于不符合策略所定义的条件时怎么办呢？在此以数据库的恢复模式设定来说，您便可以针对不符合规定的数据库项目上按下鼠标右键点选[内容]，然后切换到[选项]的页面中即可以看到与修改目前的[恢复模式]设定了。

图1 检视策略评估结果

秘诀2 SQL Server 2008性能数据收集的使用方法

关于针对SQL Server主机各项运行性能的监控，例如分析某一些查询式子的历史性能表现等等，以往我们都必须通过操作系统的性能监视器与SQL Server Profiler来进行监看，这样的管理方法不仅相当没有效率，当面对公司里有众多的SQL Server需要一并监控与分析时更是难以管理，在全新的SQL Server 2008中有更有效率的管理方法，来提供性能监控与分析的作法。

在SQL Server 2008中提供了一项性能数据收集（Data Collector）的功能。然而性能数据收集虽然是SQL Server 2008中所提供的这一项新功能，除 了Express与Express Advanced这两个免费版本没有提供之外，其它版本都是内建此功能的。通过的它使用可以帮我们将多部的SQL Server中的服务器活动记录、磁盘使用量摘要、查询统计数据记录相关信息收集到指定的数据库中来进行分析，并且自动产生所需要的三种中文分析报表。接下来就让我们来看看这一项功能的使用方法。

首先请开启SQL Server Management Studio接口，然后在[对象总管]的窗格中切换到[管理][数据收集]项目节点上，按下鼠标右键点选[设定管理数据仓储]继续。

接下来将会开启[选取组态工作]页面，由于我们是第一次执行此设定，因此请选取[建立或升级管理数据仓储]项目，点选[下一步]继续。

接着在[设定管理数据仓储内存]页面中，如图2所示在此我们必须先点选[新增]按钮，先来新增一个准备用来存放性能收集数据的独立数据库（可任意命名），因此笔者特别建立了一个名为DataWarehouse的数据库。点选[下一步]继续。

接下来在[对应登入及使用者]页面中，必须设定相关用户对于管理数据仓储的角色权限，分别有读取权限、写入权限以及完整权限的设定。在此如果您和笔者一样使用的是中文版，可能会发现下方的[数据库角色成员资格对象]的窗格紧缩在一起而无法操作，事实上这是目前SQL Server 2008的BUG，必须等到新的修正程序出来之后才能够解决，您可以暂时使用[Tab]按键与[Space]按键来瞎选试试看。点选[下一步]继续。最后在[完成向导]的页面中，可以检视到前面我们所作的所有设定，确认无误之后请点选[完成]即可，否则请点选[上一步]回头修改。

图2 设定管理数据仓储内存

图3 开启管理数据仓储概观

完成了数据收集管理中的数据仓储数据库的建立之后，紧接着必须再重新执行一次[设定管理数据仓储向导]，在[选取组态工作]的页面中请改由选取[设定数据收集]。点选[下一步]。在[设定管理数据仓储内存]页面中，如图2所示请分别选取我们在前面所建立数据仓储数据库的SQL Server实例与数据库名称，然后在本机任一路径中先建立好一个用来存放缓存文件的空文件夹，并且在此页面中的[快取目录]来完成指定即可。点选[下一步]继续。

在完成了以上步骤的设定之后，接下来我们便可以在展开至[管理][数据收集][系统数据收集组]的节点之后，看到系统帮我们建立好的三个数据收集的项目类别，分别是[服务器活动]、[查询统计数据记录]以及[磁盘使用量记录]，在此我们可以针对任一类别项目按下鼠标右键点选[属性]来查看相关属性设定。

开启[数据收集组属性设定]的页面，在此您便可以设定数据收集和上传的方法，例如您可以选取[无快取]然后设定收集和上传数据的排程。此外您还可以设定这一些分析数据在数据仓储数据库中的保存期限（默认值=14天）。在前面的设定中选取[无快取]，那么在[挑选作业排程]的页面中，您便可以选取所要进行资料收集与上传的排程方式。

当我们自定义了资料收集与上传的排程时间之后，我们可以在[SQL Server Agent][作业]项目节点展开之后，看到我们前面所建立好的每一个排程作业，为了让数据的收集与上传运行正常，请务必确认SQL Server Agent的服务持续在执行当中。

设定好一段时间之后，只要数据收集与上传的排程作业项目中已经成功执行过，那么接下来我们可以来查看前面所提到的三种报表，至于查看的方法有两种，我们先来看看第一种的操作方法。首先如图3所示，请在指定为数据收集与上传的数据库项目上按下鼠标右键点选位在[报表][管理资。料仓储]选单中的[管理数据仓储概观]继续。

执行之后将会开启 [管理数据仓储概观]的HTML格式页面，根据已执行过的排程作业，我们可以在此看到三种不同的报告项目连结可以来点选，我们可以先点选[查询统计数据记录]中的排程链接作为范例。

在此便会看到了[查询统计数据记录]的报告页面，我们可以看到不同查询（Query）所占用的各种系统资源分析图表，如果想针对特定的查询式子来检视更详细的执行性能分析信息，只要点选这个查询式子的超链接即可。

实际上，可以针对特定的查询式子执行的运行性能进行查看，便可以看到它执行时每次执行时的平均CPU时间、每次执行时的平均持续时间、每次执行时的平均实体读取次数、每次执行时的平均逻辑写入次数等等。

至于第二种查看数据收集报表的方法，可以展开至[管理][资料收集]项目节点上，按下鼠标右键点选[报表][管理数据仓储]选单中，即可分别看到有[服务器活动记录]、[磁盘使用量摘要]以及[查询统计数据记录]。在此笔者点选了[磁盘使用量摘要]项目时，如果目前已经有收集到资料，那么将会出现类似范例中的分析图表，来列出目前所有数据库数据文件与事务历史记录文件的大小、成长趋势、平均成长大小等信息。

秘诀3使用SQL Server 2008透明数据加密技术保护数据库安全

SQL Server 2008有很多新的的安全功能，可以避免因数据库遭窃而导致机密数据外泄的问题。

在SQL Server 2008中所提供的全新透明数据加密（TDE，Transparent Data Encryption）技术，让整合于它的前端应用程序无需进行任何原始码的修改，即可通过服务器证书加密机制来保护数据库的安全，而这项服务器证书并不需要藉由架设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实例查询页面中，以下达如图4所示的Backup Certificate凭证名称 TO FILE… With Private Key语法，来将凭证与私钥导出到指定的路径中，并且还必须在叙述中设定一串密码来保护这个档案的安全才可以。

一旦成功汇出服务器证书与私钥之后，最后我们便可以拿着这个档案到另一部新的SQL Server 2008实例主机上，如图5所示在查询页面中先执行建立主要密钥，接着再下达CREATE CERTIFICATE语法来指定刚刚导出的凭证档案以及私钥储存路径，并且输入正确的保护密码即可。完成了以上操作之后，您可以再试试看对于加密数据库的附加动作，别怀疑，一定会成功的！如图5所示。

图4 汇出服务器证书与私钥

图5 建立主要密钥与汇入服务器证书

秘诀4使用备份压缩-提升备份效率降低空间需求

全新的SQL Server 2008数据库备份技术，善用了如今多核心CPU架构特性，只需要利用10%的处理资源便可以让数据库的备份时间，相较于以往大幅缩减了45%，而且备份压缩下来的档案只要原来大小的25%左右。这一项功能是SQL Server在数据库备份管理上的一大突破，而在笔者实际测试的结果中，也发现确实可以达到这一项官方所提供的压缩资料。至于如何使用此功能呢？首先我们必须先将SQL Server 2008实例中的这一项功能启用。

关于启用的方法有两种，第一种方式是通过SQL Management Studio图形接口，针对实例的项目节点按下鼠标右键点选[属性]，执行后可以看到[压缩备份]的选项默认是尚未勾选的，请将它勾选即可。至于第二种方法则可以通过下方的T-SQL语法，通过系统的预存程序sp_configure来进行修改即可。一旦我们在SQL Server 2008的服务器组态中启用了备份压缩的功能，之后便可以针对各别的数据库备份计划中，选择是否要采用备份压缩的机制。

请注意！备份压缩的功能是同时支持储存在备份磁盘或磁带上

/*以T-SQL语法启用SQL Server 2008备份压缩功能

接下来我们可以实际来测试一下数据库备份压缩的能力。同样的您执行的方法有两种，第一种方式是通过SQL Management Studio图形接口中的特定数据库项目，按下鼠标右键点选[工作][备份]，接着将会开启[备份数据库]的页面，此刻您将可以如图6所示在页面中看到多出了[压缩]区域中的设定功能，从[设定备份压缩]下拉选单中可以选择压缩备份的设定，请选择[使用预设服务器设定]或[压缩备份]即可。

/*以T-SQL语法启用AdventureWorks数据库备份时的压缩机制

如图7所示以下两个数据库的备份文件，一个是采用一般传统的备份结果（Adventure_DB），另一个则是采用最新的数据库备份压缩技术所产生的结果（Adventure_DB_Compress），观察到了吗？经压缩后的备份档仅有约四分之一的大小。

图6 数据库备份压缩设定

图7 数据库压缩前后的大小比较

秘诀5 如何使用SQL Server 2008的数据压缩功能

在SQL Server 2008企业版本中，除了提供备份压缩功能来节省备份文件的储存空间之外，也提供了可以对于现有在线数据库进行数据压缩的功能，来大幅节省在线数据库的数据文件大小。这是SQL Server 2008企业版中提供的另一项新的压缩特色，而系统管理员可以通过命令或图形操作接口，来针对现有的数据库数据或新建的数据表设定压缩机制的启用，此外对于现有在线的数据库数据，我们还可以让数据压缩执行之前先评估压缩后可能造成的结果大小。接下来首先让我们来看看以命令方式的设定方法。

我们可以先以sp_estimate_data_compression_savings预存程序的执行方式，来查看针对指定数据表的压缩评估，这包括堆积、丛集索引、非丛集索引、索引检视表以及数据表和索引数据分割，如果数据表、索引或数据分割已经压缩，您可以使用此预存程序来评估未压缩之数据表、索引或数据分割的大小。关于它的使用语法范例如下所示，其中压缩类型（data_compression）的值可以选择设定为NONE、ROW或PAGE，来决定数据的压缩评估要采用数据列还是数据页的方式。

请注意！如果执行 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程序代码至档案、剪贴簿或是新增的查询窗口中来方便我们修改。

如果选择[立即执行]则可以让决定的压缩类型立刻被套用。至于如果是设定为排程，则将设定执行排程的时间。我们为这个数据压缩作业，设定一个定期执行压缩的时间，或是设定一个单一次执行的时间点。

至于我们往后如何得知哪一些数据表已经启用压缩了呢？很简单！只要在所要检视的数据表项目上按下鼠标右键点选[属性]，开启[数据表属性]页面之后，请切换到[内存]的页面，在此您将可以在[压缩]的区域中看到目前数据压缩所采用的压缩类型。

结论

SQL Server 2008以IT管理员的角度来看，虽然新增许多实用的管理功能，但是请务必记得这一些新功能（例如：数据压缩），大多只有在SQL Server 2008的企业版本中才有提供，因此对于准备部署或升级的企业用户必须特别留意，最好能够先仔细的看一下各版本的官方比较表。