基于SQL Server策略的管理-更改时评估模式

SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances article. 基于SQL Server策略的管理–有关在多个SQL Server实例上评估策略的文章。

Policy Based Management provides DBAs the possibility to declare a certain SQL Server system component (e.g. instances and objects) state and evaluate its compliance status with the declared state. There are different evaluation modes that can be used by a Policy Based Management declared policy. The modes differ by the policy evaluation execution type and the action that follows based on the evaluation result. There are four evaluation modes currently supported by the SQL Server Policy Based Management feature:

基于策略的管理使DBA可以声明某个SQL Server系统组件（例如，实例和对象）状态，并使用声明的状态评估其合规性状态。 基于策略的管理声明的策略可以使用不同的评估模式。 模式因策略评估执行类型和基于评估结果而采取的操作而异。 SQL Server基于策略的管理功能当前支持四种评估模式：

1. On demand evaluation mode that’s used per explicit administrator’s request. This is the only non-automated evaluation mode.按需评估模式。 这是唯一的非自动评估模式。
2. On schedule evaluation mode automatically checks policy violations on the scheduled time using SQL Server Agent jobs and logs violations as event notifications.按计划”评估模式使用SQL Server代理作业在计划的时间自动检查策略违例，并将违例记录为事件通知。
3. On change: prevent automated evaluation mode rolls back unwanted changes using DDL triggers to prevent policy violations.更改时：防止自动评估模式使用DDL触发器回滚不需要的更改，以防止违反策略。
4. On change: log only evaluation mode evaluates whether applied changes comply with the declared policy, but does not roll back the changes in case of a violation. It creates only event notifications instead.更改时：仅记录日志”评估模式评估所应用的更改是否符合声明的策略，但是在发生违规时不会回滚更改。 它仅创建事件通知。

The following diagram depicts the range of supported evaluation modes in SQL Server available facets referred by policies:

下图描述了策略引用SQL Server可用方面中支持的评估模式的范围：

Both automated and non-automated evaluation methods have their advantages, disadvantages and purposes. While automated evaluation modes are recommended in scenarios where unwanted user actions can violate declared policies, the non-automated is usually used by DBAs to verify a SQL Server instance and its objects status in search for omissions after regular and/or documented changes.

自动化和非自动化评估方法都有其优点，缺点和目的。 尽管在不需要的用户操作可能违反声明的策略的情况下，建议使用自动评估模式，但DBA通常使用非自动模式来验证SQL Server实例及其对象状态，以查找常规和/或有记录的更改后的遗漏。

SQL Server system performance can be affected, depending on the number of target objects being evaluated against the specific policy, if an automated policy evaluation mode is used. On the other hand, the automated evaluation modes are recommended as a method to ensure consistent policy compliance of SQL Server instances and their objects.

如果使用自动策略评估模式，则取决于根据特定策略评估的目标对象的数量，SQL Server系统性能可能会受到影响。 另一方面，建议使用自动评估模式作为确保SQL Server实例及其对象的策略一致性的一种方法。

All SQL Server editions, starting from SQL Server 2008 support the described policy evaluation modes, while the SQL Server Express editions support only the On demand evaluation mode.

从SQL Server 2008开始的所有SQL Server版本都支持所描述的策略评估模式，而SQL Server Express版本仅支持按需评估模式。

变更评估模式 (The On change evaluation mode)

In previous articles (see links below), we used the On demand and On Schedule policy evaluation modes to provide Policy Based Management feature examples. The On change evaluation mode is specific comparing to these two. First of all, it requires the enabled nested triggers feature on SQL Server. If the feature is not enabled, the policy evaluation will not provide valid results. In such case, evaluation will fail or report no violations at all. The nested triggers feature can be enabled via the Properties dialog of a SQL Server instance, on the Advanced page, by setting the Allow Triggers to Fire Others property to True.

在以前的文章（请参阅下面的链接）中，我们使用了按需策略和按计划策略评估模式来提供基于策略的管理功能示例。 与这两种相比， 变更评估模式是特定的。 首先，它需要在SQL Server上启用嵌套的触发器功能。 如果未启用该功能，则策略评估将不会提供有效的结果。 在这种情况下，评估将失败或根本不报告任何违规情况。 可以通过将SQL Server实例的“ 属性”对话框的“ 高级”页面上的“将触发器触发其他对象”属性设置为True来启用嵌套触发器功能。

There are two necessary policy requirements which declare whether a policy supports the On change evaluation mode:

有两个必要的策略要求，它们声明一个策略是否支持“按变更”评估模式：

1. On change mode (e.g. the Stored Procedure facet)更改时”模式（例如，存储过程构面）
2. Name value (e.g. the name of a stored procedure), and without server restrictions. The Name值 （例如，存储过程的名称）相关，并且不受服务器的限制。 Server restriction option determines whether the particular policy is restricted to particular SQL Server instances or not.服务器限制选项确定特定策略是否仅限于特定SQL Server实例。

To check supported evaluation modes by the SQL Server available facets and filter out only the ones that support On change: prevent mode, use the following query:

若要通过SQL Server可用方面检查受支持的评估模式，并仅过滤那些支持“ 在更改时：阻止模式”的方面 ，请使用以下查询：

 
SELECT *
  FROM msdb..syspolicy_management_facets
  WHERE execution_mode % 2 = 1;
    

This will provide the list of facets with the odd execution_mode value

这将提供具有奇数execution_mode值的构面列表

These are the facets that support the On change: prevent evaluation mode as the bit mask that defines the execution_mode value consists of:

这些是支持On更改：阻止评估模式的方面，因为定义执行模式值的位掩码包括：

4 – On schedule
2 – On change: log only
1 – On change: prevent

4 – 按计划
2 – 更改时：仅记录
1 – 更改时：防止

In the following example, we’ll show how to create a policy that evaluates names of stored procedures and prevents their (re)naming based on a declared rule (e.g. we want the names of all stored procedures to start with the ‘ACME_’ string).

在下面的示例中，我们将展示如何创建一个策略来评估存储过程的名称，并防止基于声明的规则对它们进行（重新）命名（例如，我们希望所有存储过程的名称都以“ ACME_”字符串开头） ）。

To define the policy, we need to create an appropriate condition first:

要定义策略，我们需要首先创建一个合适的条件：

1. Object Explorer, select the 对象资源管理器”中 ，在“ New Condition option in the context menu of the 条件”节点的上下文菜单中选择“ Conditions node.新建条件”选项。
2. Type in the name for the new condition (e.g. My_SP_Naming_Policy_Condition) in the Name text box.
3. 在名称文本框中输入新条件的名称 （例如My_SP_Naming_Policy_Condition ）。
4. Stored Procedure facet from the facets dropdown menu.存储过程 ”构面。

5. Set the condition rule using the Expression grid by specifying the following:

   通过指定以下内容，使用“ 表达式”网格设置条件规则：

   @Name LIKE ‘ACME_%’

   @Name喜欢'ACME_％'

   

6. Click OK to confirm the new policy creation

   单击“ 确定”确认新策略的创建

   After the condition is created, we can continue to declare the policy that will use the condition in order to evaluate and prevent unwanted renaming of stored procedures.

   创建条件后，我们可以继续声明将使用该条件的策略，以便评估并防止不必要的存储过程重命名。

7. To create the policy:

   创建策略：

8. Object Explorer, select the 对象资源管理器”中 ，选择“ New Policy context menu option on the 策略”节点上的“ Policies node.新建策略”上下文菜单选项。
9. My_SP_Naming_Policy.)My_SP_Naming_Policy。 ）
10. Enable the policy using the Enabled checkbox option.
11. 使用“已启用”复选框选项来启用策略。
12. Select the condition (My_SP_Naming_Policy_Condition) we previously declared using the Check condition dropdown.
13. 使用检查条件下拉菜单选择我们先前声明的条件（ My_SP_Naming_Policy_Condition ）。

14. Change the Evaluation Mode option to On change: prevent value.

    将评估模式选项更改为更改时：阻止值。

    

15. Against targets values to be set on every stored procedure and on every database as shown in the picture above. To change this, we will change the 违背目标”值，如上图所示。 要更改此设置，我们将“ Every Database target to a specific (e.g. 每个数据库”目标更改为特定的数据库（例如ACMEDB) database.ACMEDB ）。

16. Click the Every dropdown menu next to Database and select the New condition option. This will open the same dialog we used when creating the My_SP_Naming_Policy_Condition condition. Use it to declare a new condition (e.g. ACMEdb) by specifying the following:

    单击数据库旁边的每个下拉菜单，然后选择新建条件选项。 这将打开与创建My_SP_Naming_Policy_Condition条件时使用的对话框相同的对话框。 通过指定以下内容，使用它来声明新条件（例如ACMEdb ）：

    @Name = ‘ACMEDB’

    @Name ='ACMEDB'

    The appropriate Database facet will be already selected, since we are creating a database related condition. Click OK to confirm the ACMEdb condition creation and to return to the policy creation dialog. After the ACMEdb condition is created, the Against targets box of the policy creation dialog will be changed:

    由于我们正在创建与数据库相关的条件，因此已经选择了适当的数据库构面。 单击“ 确定”以确认ACMEdb条件创建并返回到策略创建对话框。 创建ACMEdb条件后，将更改“策略创建”对话框的“针对目标”框：

    

17. OK to complete the new policy (确定”完成新策略（ My_SP_Naming_Policy) creation.My_SP_Naming_Policy ）的创建。

After the process of declaring the policy is finished, both the policy and newly created conditions are available in Object Explorer under appropriate sub-nodes of the Policy Management node.

声明策略的过程完成之后，策略和新创建的条件都可以在对象浏览器中的“ 策略管理”节点的相应子节点下使用。

To test the policy, try to create (or rename) a stored procedure in a specified database. The following message will be shown:

要测试该策略，请尝试在指定的数据库中创建（或重命名）存储过程。 将显示以下消息：

Note that the policy we declared can be manually evaluated against the database and its existing stored procedures also. In case there are stored procedures that do not comply with the policy, these will be reported. So, the On change: prevent evaluation mode proves to be efficient both in preventing and identifying existing policy violations. Unfortunately, the number of facets referred by policies that support the On change: prevent evaluation mode is very limited by their number and by the necessary policy requirements as we described.

请注意，我们可以针对数据库及其现有存储过程手动评估我们声明的策略。 如果存在不符合该策略的存储过程，则将报告这些过程。 因此，“不断变化：预防”评估模式被证明在预防和识别现有政策违规方面都是有效的。 不幸的是，支持“不断变化：预防”评估模式的策略引用的构面的数量受到其数量和我们描述的必要策略要求的限制。

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