sql server管理和性能

23ee

基于策略的管理（PBM）
PBM在SQL Server 2008中已引入，它能够让DBA强制实施标准并在整个SQL Server环境中自动化健康检查活动。PBM功能提供了一个框架，对命名规范、安全、配置设置等实施组织内部的标准，并能提依据这些条件供定期的报表和警告。PBM能够实施的策略类型包括数据库级别的检查，如确保Auto Close和Auto Shrink是禁用的，强制对象命名规范，确保实例级别的配置选项，如Max Degree of Parallelism和Max Server Memory都正确地配置。
PBM有3个关键方面：
面（Facet）------用于检查的对象属性（如数据库、登录或服务器）。面是固定的，不能添加或改变。
条件（Condition）------评测true或false，包括验证设置或选项的逻辑，如确认AutoClose是false。
策略（Policy）------在一个目标上应用条件，确定策略模式，如评估或预防。
为了解释这些变量，你可以使用条件来创建策略，以此来确保数据和日志文件增长遵守最佳实践，如表16-1所示：

在定义每个策略时，可能要选择一个评估模式来确定策略的效果，表16-2概括了这些选项：

PBM准备开始
这里介绍PBM部署和运行的步骤，需要3个阶段：定义条件、创建策略和在本地机器上评估策略。下面的步骤建立一个条件和策略：

• 启动SSMS并选择管理=>策略管理
• 右击Conditions并选择New Condition
• 输入条件名称Auto Close Disabled
• 使用Facet的下拉列表，选择Database Performance
• 在表达式一栏，选择阑尾名称@AutoClose，验证等号操作符，并选择值False（如图16-1所示）。点击OK
  
• 右击Policy并选择Create New Policy
• 指定策略名称Database-AutoClose
• 使用 Check条件下拉列表，选择Auto Close Disabled条件
• 验证Against Targets选项，在每个数据库旁边显示一个检查
• 确保Evaluation Mode显示On demand，并且Server约束是None
• 点击OK。
  

现在展开策略文件夹，右击名为Database-AutoClose的策略，并选择Evaluate。报表将会显示实例上每个数据库一行的列表，希望每行都显示绿色检查以表明遵守策略。启用一个数据库上的Auto Close选项，再来评估策略，以确认功能准确无误。现在你应该看到一个红叉标记，表明没有遵守。在没有遵守策略的旁边是一个检查框，把该检查框标记为启用，然后点击对话框右下角的Apply按钮。点击Apply按钮会做两件事情：改变该数据库属性为Disable Auto Close，并重新评估策略显示一个遵守策略的数据库（绿色）。
这个例子展示了PBM如何高效地识别和解决一个环境中的配置问题。如果把策略排Job，它就能找到和接解决一个环境中任何欠佳的配置。
使用条件作为服务器约束
这里聚焦如何通过使用服务器约束选项来添加更多逻辑到策略里。为了增强前面所介绍的Auto Close策略的智能性，把这个策略应用到标准或企业版的SQL Server上或许是有用的。这会避免SQL Server Express实例上可能的虚假警告。下面的步骤将创建一个条件，并将其作为约束赋值给早前创建的策略：

• 右击Conditions并选择New Condition
• 输入名称SQL Server Standard或Enterprise Edition
• 使用Facet的下拉框，选择Server
• 在表达式一栏，选择栏位名称@EngineEdition，选择等号并从Value下拉框里选择Standard
• 点击Expression，使用OR操作符添加一个新语句
• 再次选择栏位@EngineEdition，使用等号，并从Value下拉框中选择EnterpriseOrDeveloper
• 点击OK
• 定位策略Database-Auto Close并选择属性
• 使用Server Restriction下拉框，选择命名为SQL Server Standard或Enterprise Edition的Condition
  

下次该策略在评估时，将会探测SQL Server版本，仅针对运行标准或企业版的服务器进行评估。为了平衡这些策略的值，有必要定义一个Central Management Server，导入策略并对环境进行评估。
企业策略评估
基于策略管理框架的架构组成了存放策略的中央管理服务器；服务器群组是服务器的逻辑容器；服务器注册是到目标数据库服务器的连接。一旦中央管理服务器定义好并且每个实例都注册，策略就能够很容易地对整个服务器群组进行评估，并产生报告来显示遵守状态。
第一步是配置一个中央管理服务器。该服务器通常用作存放策略，并主持Schedule进行策略评估。要配置一个中央管理服务器：

• 启动SSMS并选择View=>Registered Servers
• 展开数据库引擎，并在出现的扩展视图中右击Central Management Servers并选择Register Central Management Server。New Server Registration对话框将会如图16-2所示：
  
• 在“Server name:”一栏输入实例的名字，你可以选择性地取一个友好的Registered server name并给予描述。选择主机实例后点击Save。
  

现在中央管理服务器已创建，你想添加服务器进来。最好为环境（例如Production、Test、Development）中不同群组的服务器创建不同的服务器群组。要创建服务器群组，右击Central Management Server并选择New Server Group，输入Production，并点击OK。
通过右击服务器群组并选择New Server Registration，把每个生成实例注册进名为Production的组别中。实例必须能够直接与Central Management Server进行交流，因此你会想指定连接细节来连接New Server Registration对话框中的实例。
如果策略在实例而不是Central Management Server上创建，那么你需要转移它们，你可以使用一个简单的导出/导入来做到这点。要从源实例中导出策略，右击并选择导出，并指定一个名字和磁盘位置。每个策略导出都会生成一个XML文件。
要把策略导入到Central Management Server，右击服务器名称并选择Central Management Server Actions，然后从子菜单里选择Import Policies。导航到文件夹位置并选择策略文件进行导入。
要评估策略，右击Central management Server以评估所有注册的服务器，或右击组名评估组内的服务器，并从菜单里选择Evaluate Policies。选择源（指定Central Management Server）并通过左侧的Checkbox勾选策略来评估。点击右下角的Evaluate并验证输出。
自动化PBM检查最佳实践
定义并自动化健康检查策略有一些好处，特别是方案的可扩展性和复用方面。你可以排Job执行检查，任何偏离或疏忽都会立即强调出来。另外，只需很少精力就能把这些检查应用到整个增添新服务器或额外环境的资产(estate)中。
数据库--检查最后一次备份
该检查确保过去24H内成功完成一次完整备份，该条件中使用的方法可以很容易地应用到事务日志备份。
首先，创建一个新条件，名称为Last Good Backup。在Facet栏位，选择数据库维护。选择栏位@LastBackupDate并使用操作符>=。接下来，在Value栏位，点击省略号（...）启动Advanced Edit对话框。该对话框允许输入T-SQL语句。输入下面语句，点击OK，并保存条件：DateAdd(‘day’, -1, GetDate())。
接下来，新建一个策略，名称为Database-Recent Backup，并选择检查刚创建的条件Last Good Backup。初始时，把Evaluation Mode设置为On demand。所有其他设置应该保持为初始值。点击OK完成策略的创建。
数据库--数据和日志文件自动增长增值
该策略检查数据和日志文件的自动增长的增值，以确保它们都充分利用给定的数据库大小。要创建该策略，先创建如表16-3所示的4个条件。图16-3显示了中间数据检查条件的值。


在创建策略前，还需要4个条件用以确保每个策略都能应用到正确的数据库上，如图16-4。表16-4概括了所需的额外条件。


一旦这些条件创建好，接下来就是创建策略。需要4个策略，这些策略会连接检查条件到目标条件，并指定策略的评估模式。表16-5概括了所需的4个策略。图16-5显示了使用Small Data条件的一个实例对话框。


创建这8个条件和4个策略，可以自动化策略评估，以确保小数据库有KB增长的增值，有大于1GB数据或日志文件的数据库自动增长设为1GB或更大。
SQL Server--Max Degree of Parallelism
该策略的目标是确保超过4个CPU的任何服务器，都能有最佳的sp_configure选项Max Degree of Parallelism(MaxDoP)。实施这样的策略需要2个条件和1个策略。第一个条件用于检查验证MaxDoP设置是否正确。第二个条件用作服务器约束，确保策略仅能针对有超过4个CPU的服务器。最后，该策略会把检查条件和约束条件绑在一起来决定评估模式。
从创建检查条件开始，如图16-6。

接下来新建一个条件用于服务器限制。新建一个条件，名为Greater than 4 CPUs，使用facet Server。在Expression一栏，使用@Processors，并设置为>4。最后一步创建策略。使用名称SQL Server — MaxDoP，并选择前面创建的检查条件和服务器限制条件。该策略看起来应该像图16-7所示的样子。

通过PBM强制实施IT标准
PBM能够阻止开发者或管理员做违反阻止标准的变更。为了配置一个策略使用On Change：阻止评估模式，facet必须支持DDL变更。
对象命名规范
一个通常使用的规范是所有的存储过程加前缀'usp_'。这清晰地识别出用户创建的存储过程，因此区别于系统存储过程。要做到这点，创建一个条件，选择一个有意义的名字，并选择Stored Procedure面。在Expression栏，选择栏位@Name，使用LIKE操作符，并在Value栏位输入'usp_%'，如图16-8所示。

条件建好后，新建一个策略，并选择前面创建的检查条件。在Against targets一栏，选择Every Stored Procedure in Every database。如果要值得满意，改变Evaluation Mode为On change：阻止。这会引起错误，并回滚任何违反命名规范的CREATE PROCEDURE语句。图16-9显示了策略配置。

聚集索引
很多DBA和一些组织要求每张表都要有聚集索引。下面的步骤描述了如何通过PBM创建条件和策略来报告。
首先，新建一个条件，指定有意义的名字，选择名为Table的facet。选择栏位@HasClusterIndex，使用等号并选择值为True。
接下来，创建一个策略，并为目标选择每个数据库中的每张表。该条件使用的facet不支持强制执行条件，因此评估模式选项是On Demand或On Schedule。图16-10显示了策略配置。

企业策略管理框架（EPMF）
EPMF提供了中央化的符合报告用于PBM解决方案。当前尽在SQL Server 2008里自带。EPMF提供了一套报告，它们能够轻易识别出策略失败，按策略类别和过去的时间（over time）组织起来。解决方案很容易部署和配置，进一步利用PBM结构和知识。你可以下载EPMF并留意SQL Server更新：http://epmframework.codeplex.com/。
其它用作管理SQL Server的微软工具
这里介绍非SQL Server自带的工具：
System Center Advisor(SCA)
SCA是一个基于云的订阅服务，它提供配置复查及反馈。该工具目的是统一其它产品的功能，如Windows Server Update Service（WSUS）和Best Practice Analyzer（BPA），并弥补最佳实践指南和CSS（Microsoft Customer Service and Support）提供的建议之间的差距。因此，SCA为客户提供特定的、可行的推荐，它支持SQL Server 2008及以上的版本。你可以了解更多信息，从http://www.microsoft.com/en-in/server-cloud/system-center/advisor-overview.aspx。如何配置SQL Server 2012以允许System Center Advisor 监控，可以参考：http://support.microsoft.com/kb/2667175。
尽管SCA提供大量有用的信息，但它不是性能分析工具或连线监控工具，它没有实时的数据抓取或警报功能。这里概括一下编写本书时所能监控的环境：

• Windows Server 2008及以上：Active Directory、 Hyper-V Host、SQL Engine
• General operating system
• Exchange Server 2010
• SharePoint Server 2010
  

Topology
虽然SCA是一个基于云的服务，但是两个组件需要预置（on-premise）安装。虽然这个过程是流线型的，彼此互不干扰，但两个角色必须预置部署：Gateway和Agent。每个环境都需要网关，网关使用基于证书的授权来上传数据到Web服务。网关收集和上传凑够每个Agent捕获到的监视数据。网关必须在和被监视的服务器一样的安全区域（Active Directory domain 或 trusted domain）。另外，网关必须能访问Internet来完成数据上传到Web服务。每台SCA监控的服务器都需要安装Agent。如果服务器和网关所在的域不一样，那么也必须在服务器上安装网关和证书。另外，Agents必须能够和网关进行通信。数据采集过程大约每24H运行一次，一旦完成部署，你就可以在接下来的日子里确保服务器正确地显示在Web控制台。
Reporting
SCA的警报和管理是通过Web入口访问的，该入口可以在http://www.systemcenteradvisor.com/找到。Web控制台提供了一个简单易用的环境当前状态的概览，使用颜色来标示警告的严重性（critical、warning、without alert）并指明受影响的区域（SQL Server和Windows），如图16-11所示。概览页面还显示服务器状态，它显示了任何不报告的代理（nonreporting Agents）。

左侧导航区显示有5个额外的标签，其概括如表16-6所示：

除了使用Web控制台监控外，SCA还可以配置对新的SCA警告发送e-mail通知。
System Center Operations Manager（SCOM）
SCOM产品微软的企业级监控工具，属于Systems Center套件。SCOM提供了一个强大、灵活，并且高度可配置的平台用于搭建监控解决方案。然而，它需要大量工作。另外，SQL Server的管理包已经更新了（不是重写），贯穿SQL Server多个版本。同样，管理包常常使用遗留技术，而不会提供最佳的功能覆盖新的发布。结果你需要根据设计、部署、配置、调校和开发做出一项重要的投资。为了使用SCOM创建一个有意义的监视方案。
Design和Implementation
SCOM解决方案由大量关键组件构成，包括Agent（必须在要监控的每台Server上安装以便监控）。Gateway用于收集监控数据；Root Managerment Server（RMS）用于存放和聚集数据并生成警报；控制台是DBA和系统工程师管理环境的地方。图16-12显示了一个典型的SCOM部署场景。

Agent必须安装到需要监控的每台目标服务器上，和网关的通讯必须开启。如果目标服务器和网关不在同一安全区（比如不在同一域或工作组中），那么就必须使用证书来提供目标服务器和网关之间的授权。每台服务器都可以报告多达6个管理组别。
网关角色是安全边界和架构扩展点。假定SCOM平台设计为扩展监视数千台设备，如果所有设备都设置直接向这台主机报告，那么RMS或许就变成一个连接点。网关服务器提供一个外扩点用于监视基础结构。另外，在组织从多个地方操作或使用不同的安全区的场景下，网关服务器可用作安全边界及数据流到RMS的一个聚合点。Agents归位到给定的网关，PowerShell脚本可以用来提供failover Gateway，提供一个容错（fault-tolerant）的解决方案。
体系的顶层是RMS，它是配置和变更（新的代理及规则或监视器）的中心点。RMS服务器必须能够和网关服务器通讯，如果没有Active Directory信任，就必须配置证书授权。
Rules和Monitors
两种类型的检查rules及monitors均有SCOM执行。二者收集数据，理解它们之间的差异对于决定该使用哪种检查至关重要。
Monitor是一个接近实时的操作，是改变管控对象健康状态的威力方式。另外，一旦条件被解析，健康状态就会自动改变。一个例子是低磁盘空间，一旦释放空间，监视器就会自动解析。收集的数据不会存储。
Rule通常用于收集特定对象的数据（如用于存储性能基线的Avg Disk Transfer/sec）。规则对于创建报警也有用，它不会影响健康状态。这些报警必须手动解析。收集的数据存放在数据仓库。
Alerts
Alert不是e-mail或页面通知，而是一个事件，它能够被Monitor或Rule触发。Alerts显示在SCOM控制台，在Alerts标签下，它们按照默认的优先级排序。通知是通讯的一种方法，如e-mail、SMS或页面，由alert触发。标度（calibration）是调校alerts的过程，以确保恰当级别的敏感度。一个环境可以包含广大不同的数据库负载，Windows和SQL Server配置设置，以及优化，所以健康服务器的概念也是不同的。Alert标度按服务器重新定义阈值，以此来确保alerts都是有意义的。Alert调校采取覆盖的形式，对于特定服务器或群组（所有Windows 2008 逻辑地盘或SQL Server 2008数据库）的Rule或monitor，把阈值从标准改为客制化的值。当你创建覆盖时，保存微软提供的密封管理包是有用的。这在预封装、下载的管理包和任何组织或服务器特定的包之间提供了隔离。定义一个组织标准用于命名那些覆盖被保存的管理包，例如，你可以创建一个新的MP用于Windows Server 2008 R2客制化，并将其命名为Windows Server 2008 R2--Overrides。这清晰地为in-box何custom功能划清界限。
导入管理包
Windows和SQL Server 管理包（MPs）是Microsoft发布并进行版本控制、免费供公众消费的。下载最新版本并导入SCOM。管理包之间的任何依赖在导入的时候就指明了。MP下载包括一个Word档，它是一个指南，描述安装过程、规则和监视器，并包含任何最后一刻的突破性变化。导入导出功能也能用作备份和恢复方法，用于客制管理包，以防万一一个管理包需要回滚。
管理包创作
起源于监控过程的最伟大价值是创建健康检查，来识别应用平台的关键方面，并提供详细的数据采集。SCOM本身就是一个强大的平台，以管理包创作的形式开发客制化监控。