SQL Server基于策略的管理

SQL Server Policy-Based Management was introduced in SQL Server 2008, to make it easy for database administrators to define and enforce SQL Server best practices and company standards in the form of policies. This feature is available in both Enterprise and Standard SQL Server Editions.

SQL Server 2008中引入了基于SQL Server基于策略的管理，以使数据库管理员可以轻松地以策略的形式定义和实施SQL Server最佳实践和公司标准。 企业版和标准SQL Server版均提供此功能。

Policy-Based Management helps DBAs to be proactive, by providing them with a way to define the standards that control the SQL Server and database objects configuration. These configurations can be enforced on the database objects, database, SQL instance or multiple instances levels. The defined policies’ evaluation can be automated with no effort from the database administrator. If the DBA decides to fix the objects that don’t meet the configured policy, he can enforce this policy easily on all out-of-policy objects with a single button click, without going through all objects manually.

基于策略的管理通过为DBA提供定义定义控制SQL Server和数据库对象配置的标准的方式的方法，帮助他们积极主动。 这些配置可以在数据库对象，数据库，SQL实例或多个实例级别上强制执行。 定义的策略评估可以自动进行，无需数据库管理员的任何努力。 如果DBA决定修复不符合所配置策略的对象，则只需单击一下按钮，他就可以轻松地对所有超出策略范围的对象实施此策略，而无需手动检查所有对象。

Policy-Based Management consists of five main components; the pre-defined properties that describe the SQL object functionality, which will be used to evaluate and manage that object, such as the Database Options, called Facets. There are more than 74 facets in SQL Server, and each facet has one or more properties. Conditions are the facet’s property state that will be used to evaluate the managed objects, such as the Full value for the database Recovery Model property of the Database Options facet. The Targets are the type of objects that will be managed and evaluated. The set of conditions that will be used to evaluate the facets of the target objects called Policies. Evaluation Mode specifies how the policy will be evaluated by comparing the actual setting value with the value defined in the condition. If the actual value of the target meets the condition value, the policy evaluates to true. If the actual value of the target breaks the condition, the policy evaluates to false.

基于策略的管理包括五个主要部分； 描述SQL对象功能的预定义属性，这些属性将用于评估和管理该对象，例如称为Facets的数据库选项。 SQL Server中有超过74个方面，并且每个方面都有一个或多个属性。 条件是构面的属性状态，将用于评估托管对象，例如“数据库选项”构面的“数据库恢复模型”属性的“完整”值。 目标是将要管理和评估的对象的类型。 一组用于评估目标对象构面的条件，称为“ 策略”。 评估模式指定如何通过将实际设置值与条件中定义的值进行比较来评估策略。 如果目标的实际值满足条件值，则策略评估为true。 如果目标的实际值超出条件，则策略评估为false。

The evaluation mode can be On Demand, where the evaluation will be upon your request. Using On Schedule evaluation mode, you need to define a schedule that will control the evaluation process. The policy can be evaluated when a change that breaks the policy’s condition and make the policy to evaluate to false is occurred. In this case, the change can be prevented if you use On Change – Prevent evaluation mode, or just log that action without preventing it when using the On Change -Log Only evaluation mode.

评估模式可以是“按需” ，其中评估将根据您的要求进行。 使用按计划评估模式，您需要定义一个计划，该计划将控制评估过程。 当发生违反策略条件并使策略评估为false的更改时，可以评估该策略。 在这种情况下，如果您使用“按更改-阻止评估”模式，或者在使用“按更改-仅记录”评估模式时仅记录该操作而不阻止它，则可以防止更改 。

SQL Server Policy-Based Management is configured using SQL Server Management Studio. To go through it, expand the Management node from the Object Explorer to view the Policy Management. Expand the Policy Management node to find the Policy-Based Management main components as follows:

使用SQL Server Management Studio配置基于SQL Server策略的管理。 要遍历它，请从对象资源管理器中展开“ 管理”节点以查看“ 策略管理” 。 展开“策略管理”节点以找到“基于策略的管理”主要组件，如下所示：

As you can see from the previous image, there are no user pre-defined conditions or policies. It is the DBA’s job to check which policies should be defined and evaluated upon the company’s need. There are sample policies that are installed with the SQL Server installation and stored as XML files. You can check these sample policies and import the ones you are interested in into your SQL Server instance to be ready for the evaluation. If you installed the SQL Server in the C drive, the XML files can be found under the below path:

从上一张图像可以看到，没有用户预定义的条件或策略。 DBA的工作是检查应根据公司的需要定义和评估哪些策略。 有一些示例策略随SQL Server安装一起安装，并存储为XML文件。 您可以检查这些示例策略，然后将感兴趣的策略导入SQL Server实例中，以准备进行评估。 如果将SQL Server安装在C驱动器中，则可以在以下路径下找到XML文件：

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Policies\DatabaseEngine\1033

C：\ Program Files（x86）\ Microsoft SQL Server \ 120 \ Tools \ Policies \ DatabaseEngine \ 1033

To import these polices to your SQL Instance, right-click on the Policies node under the Policy Management and choose Import Policy as follows:

要将这些策略导入到您SQL实例，请右键单击“ 策略管理”下的“ 策略”节点，然后选择“ 导入 策略” ，如下所示：

In the Import window, click on the dots beside the File to import field to brows the policies XML files. Choose all policies you need to import and click OK. Now the imported polices are ready to be evaluated using the suitable evaluation mode.

在“ 导入”窗口中，单击“ 要导入的文件”字段旁边的点以浏览策略XML文件。 选择您需要导入的所有策略，然后单击“ 确定” 。 现在，可以使用适当的评估模式来评估导入的策略。

The first step in creating and configuring the policies is deciding which facet property you will evaluate and enforce. There are many facets defined in SQL Server, which can be displayed by expanding the Facets node from the Policy Management as below:

创建和配置策略的第一步是确定要评估和实施的构面属性。 SQL Server中定义了许多方面，可以通过从“ 策略管理 ”中展开“ 方面”节点来显示这些方面 ，如下所示：

To check any facet, right-click on the selected facet and choose Properties as below:

要检查任何构面，请右键单击所选构面，然后选择“ 属性” ，如下所示：

In the Facet Properties window, you can find all properties for the chosen facet with description for each property. This helps you in deciding the facet and its property that will be used in the policy and the condition value that will be used to compare with the actual value.

在“构面属性”窗口中，可以找到所选构面的所有属性以及每个属性的描述。 这有助于您确定将在策略中使用的构面及其属性以及将用于与实际值进行比较的条件值。

Let’s say that the RecoveryModel property of the Database Options facet is selected. The next step now is to create the condition that specifies the facet property value that will be compared with the actual evaluated value. To create a condition, right-click on Conditions node from the Policy Management and choose New Condition:

假设已选择“数据库选项”构面的RecoveryModel属性。 现在的下一步是创建一个条件，该条件指定将与实际评估值进行比较的构面属性值。 要创建条件，请在“ 策略管理”中右键单击“ 条件”节点，然后选择“ 新建条件” ：

In the General page of the Create New Condition window, write a meaningful name for the created condition in order to distinguish it when creating the policy. Choose the facet that your condition depends on from the drop down list, which is the Database Options facet in our case as follows:

在“ 创建新条件”窗口的“ 常规”页面中，为创建的条件写一个有意义的名称，以便在创建策略时加以区分。 从下拉列表中选择您的条件所依赖的方面，在本例中为数据库选项方面，如下所示：

To build the condition, choose the facet’s properties that you will include in the Expression area of the Create new Condition window below:

要构建条件，请选择要包含在以下“ 创建新条件”窗口的“ 表达式”区域中的构面的属性：

Click on the dots beside the Field box to view the Advanced Edit window below to choose the facet property that you will use in your condition. Choose the property and click OK.

单击“字段”框旁边的点，以查看下面的“ 高级编辑”窗口，以选择要在条件中使用的构面属性。 选择属性，然后单击“ 确定” 。

Once you choose the facet property that will be evaluated, you need to choose the property value that will be compared with the actual value in the evaluation process. This value can be selected from the Value drop down list in the Expression area, which is Full recovery model in our example, as follows:

选择要评估的构面属性后，需要选择将与评估过程中的实际值进行比较的属性值。 可以从“ 表达式”区域的“ 值”下拉列表中选择此值，在我们的示例中为“完全恢复”模型，如下所示：

You can choose more than one property and value when creating the condition with the ability to perform And / Or operations between these values. After you finish the condition creation, click OK.

创建条件时可以选择多个属性和值，并且可以在这些值之间执行“与/或”操作。 完成条件创建后，单击确定 。

To make sure that the condition is created successfully, expand the Conditions node from the Policy Management where you should find the created condition:

为了确保成功创建条件，请从“ 策略 管理”中展开“ 条件”节点，在其中应找到创建的条件：

Now we are ready to combine the policy management components together to create the policy. Right-click on the Policies node of the Policy Management and select New Policy:

现在，我们准备将策略管理组件组合在一起以创建策略。 右键单击“ 策略管理”的“ 策略”节点，然后选择“ 新建策略” ：

In the General page of Create New Policy window, write the name for your policy in the Name field. Browse the previously created condition in the Check Condition field as follows:

在“ 创建新策略”窗口的“ 常规”页面上，在“名称”字段中输入策略的名称。 在“检查条件”字段中浏览先前创建的条件，如下所示：

Now we need to specify the target that the condition will be checked against, which are all databases in our example here. You are requested also to specify the Evaluation Mode for the policy. As you can see, the On Change: Prevent option is not available in our example here, as it is used to prevent only the operations that can be rolled back. Choose the suitable evaluation mode and click OK.

现在，我们需要指定要检查条件的目标，这里是我们示例中的所有数据库。 还要求您指定策略的评估模式。 如您所见，“ 更改时：阻止”选项在我们的示例中不可用，因为它仅用于阻止可以回滚的操作。 选择合适的评估模式，然后单击“ 确定” 。

Expand the Policy node of the Policy Management to ensure that the policy is created:

展开“ 策略 管理”的“ 策略”节点，以确保创建了策略：

To summarize what is done till now, we have created a policy that check if the recovery model of all databases hosted in the current SQL instance is FULL or not. And this policy will be evaluated on demand.

总结到现在为止所做的工作，我们创建了一个策略来检查当前SQL实例中托管的所有数据库的恢复模型是否为FULL。 并且该政策将按需评估。

Let’s evaluate the created policy against the defined target manually, ass the selected evaluation mode for our policy is On Demand. To evaluate the policy, right-click on the created policy and select Evaluate as below:

让我们根据定义的目标手动评估创建的策略，因为我们的策略选择的评估模式是“按需”。 要评估策略，请右键单击创建的策略，然后选择评估 ，如下所示：

The Evaluate Policies dialog box will be displayed, showing the result of the policy check per each database as follows:

将显示“评估策略”对话框，显示每个数据库的策略检查结果，如下所示：

As you can see from the previous result, three databases matched the policy condition and three databases didn’t match the condition. If you click on the details View link beside each database, the result details view window will be displayed showing the difference between the actual value and the expected value by the policy as below:

从前面的结果中可以看到，三个数据库符合策略条件，三个数据库不符合条件。 如果单击每个数据库旁边的明细视图链接，将显示结果明细视图窗口，其中显示该策略的实际值和预期值之间的差异，如下所示：

A nice feature that you can take benefits from, in the policy evaluation dialog box, clicking on the Apply button will change the target’s facet property value that doesn’t match the policy’s expected value to match the policy condition. In our example, if we click Apply button in the previous result window, the recovery model of the three databases that didn’t match the policy condition will be changed to FULL. If you click Apply, the below warning message will be displayed to make sure that you are OK with that changes:

一个不错的功能，您可以从中受益，在策略评估对话框中，单击“ 应用”按钮将更改与策略的预期值不匹配的目标方面属性值，以匹配策略条件。 在我们的示例中，如果在上一个结果窗口中单击“应用”按钮，则不符合策略条件的三个数据库的恢复模型将更改为“完整”。 如果单击“应用”，将显示以下警告消息，以确保您可以进行更改：

If you confirm applying the change, the policy will be evaluated again after changing the databases recovery model to FULL, evaluating the policy to true for all targeted databases as follows:

如果您确认应用更改，则在将数据库恢复模型更改为FULL之后，将再次评估该策略，对所有目标数据库将该策略评估为true，如下所示：

Let’s take one of the databases that didn’t match the policy’s condition at the beginning due to Simple recovery model and check if its recovery model is changed to FULL. From the AdventureWorks2012 database’s Options tab of the Database Properties window, it is clear that the database Recovery model is changed to FULL as follows:

让我们采用由于简单恢复模型而在开始时与策略条件不匹配的数据库之一，并检查其恢复模型是否更改为FULL。 从“ 数据库属性”窗口的AdventureWorks2012数据库的“ 选项”选项卡中，很明显，数据库恢复模型已更改为“完全”，如下所示：

结论 (Conclusion)

SQL Server Policy-Based Management helps database administrators to be proactive, by automating policies that will save time and effort in checking and applying SQL Server baselines and standards. Policy-Based Management can be also applied on a group of servers if you have Central Management Servers group that you use to administrate a group of servers together in single action.

SQL Server基于策略的管理通过自动执行策略，从而节省了检查和应用SQL Server基准和标准的时间和精力，从而帮助数据库管理员积极主动。 如果您有*管理服务器组可用于在单个操作中一起管理一组服务器，则基于策略的管理也可以应用于一组服务器。

As a best practice, add the msdb system database to your backup job as it stores all the policies information. Also, using the On Change – prevent evaluation mode, you need to test it on a test environment first to make sure that it will not harm the server’s performance. You can also create alerts for the policies result, as it is logged to the SQL Server error logs with the 34050, 34051, 34052 and 34055 error codes.

最佳做法是将msdb系统数据库添加到您的备份作业中，因为它存储了所有策略信息。 另外，使用“ On Change –防止评估”模式，您需要首先在测试环境中对其进行测试，以确保它不会损害服务器的性能。 您还可以为策略结果创建警报，因为它会记录到带有34050、34051、34052和34055错误代码SQL Server错误日志中。

翻译自: https://www.sqlshack.com/sql-server-policy-based-management/