基于SQL Server策略的管理–类别和数据库订阅

SQL Server Policy Based Management provides several additional features which makes administering SQL Server easier. In this article, we are going to explain policy categories and database subscriptions.

SQL Server基于策略的管理提供了一些附加功能，这些功能使管理SQL Server更加容易。 在本文中，我们将解释策略类别和数据库订阅。

SQL Server基于策略的管理类别 (SQL Server Policy Based Management categories)

Each policy that’s created within Policy Based Management can belong to a certain policy category. However, a policy category is not mandatory for a policy.

在“基于策略的管理”中创建的每个策略可以属于某个策略类别。 但是，策略类别对于策略不是强制性的。

Policy categories can be crated in several ways. You can use the list of all available SQL Server policy categories which can be used both for modifying (updating and deleting) and creating categories:

可以通过几种方式创建策略类别。 您可以使用所有可用SQL Server策略类别的列表，这些类别可用于修改（更新和删除）和创建类别：

1. Expand the Management node in SQL Server Object Explorer and select the Manage Categories option from the Policy Management context menu.
2. 在SQL Server 对象资源 管理器中展开“ 管理”节点，然后从“ 策略管理”上下文菜单中选择“ 管理类别”选项。
3. Manage Policy Categories dialog will show all currently available policy categories.管理策略类别”对话框将显示所有当前可用的策略类别。

4. Click the empty row in the Categories grid and type in the name of a new policy category.

   单击类别网格中的空行，然后输入新策略类别的名称。

   

5. Mandate Database Subscriptions checkbox option for the category if you don’t want to promote the category (and the policies that belongs to it) as mandatory for all SQL Server databases.任务数据库订阅”复选框选项。
6. OK to save changes确定保存更改

Another method to create a new policy category is available during the policy creation process or while modifying existing policies. Either way, the same dialog is used:

在策略创建过程中或修改现有策略时，可以使用另一种创建新策略类别的方法。 无论哪种方式，都使用相同的对话框：

1. Properties context menu option of a policy in 对象资源管理器中选择策略的“ Object Explorer.属性”上下文菜单选项。
2. Description tab of the dialog and click 描述”选项卡，然后单击“ New.新建” 。
3. Create new category dialog, type in the name of the new policy category.创建新类别”对话框，键入新策略类别的名称。
4. OK to create the category.确定以创建类别。

The same dialog can be used to change the category of the policy via the Category dropdown.

可以通过类别下拉菜单使用相同的对话框来更改策略的类别 。

Note that both methods create a policy category that is mandatory for all databases by default. If needed, use the Manage Categories dialog to change this.

请注意，默认情况下，这两种方法都会创建对所有数据库都是必需的策略类别。 如果需要，请使用“ 管理类别”对话框进行更改。

Policy categories are used in several SQL Server features in order to simplify specific actions.

在几种SQL Server功能中使用了策略类别，以简化特定的操作。

The Object Explorer Details window provides the list of all available Policy Based Management policies and their categories if the Policies node is selected in Object Explorer

如果在对象资源管理器中选择了“ 策略”节点，则“ 对象资源管理器详细信息”窗口将提供所有可用的基于策略的管理策略及其类别的列表。

Using the Category column header, policies can be easily sorted in order to locate appropriate policies in the list. In addition, you can select desired policies and then execute one of the options available from the context menu, such as Evaluate.

使用类别列标题，可以轻松对策略进行排序，以便在列表中找到适当的策略。 另外，您可以选择所需的策略，然后执行上下文菜单中可用的选项之一，例如“ 评估” 。

Another place where policy categories can be used is the View Policy dialog. Click any object in Object Explorer that can be a policy target (database, table, etc.) and select the View submenu option of the Policies context menu option. The dialog will show the list of policies effective on the target object. The dialog shows relevant policies, their policy categories, and the information about the policies, such as the policy evaluation mode and the date and time the policy was last run.

可以使用策略类别的另一个地方是“ 查看策略”对话框。 单击对象资源管理器中任何可以作为策略目标的对象（数据库，表等），然后选择“ 策略”上下文菜单选项的“ 查看”子菜单选项。 该对话框将显示对目标对象有效的策略列表。 该对话框显示相关策略，其策略类别以及有关策略的信息，例如策略评估模式以及上次运行策略的日期和时间。

The most common method for evaluating policies is via the Evaluate context menu option of the Policies node in Object Explorer. This option shows the Evaluate Policies dialog that can be used to review all existing policies on a SQL Server instance and to manually check the ones you want to evaluate against declared target objects. The list includes the Category column making the process of overview and appropriate policies selection easier.

评估策略的最常用方法是通过对象资源管理器中“ 策略”节点的“ 评估上下文”菜单选项。 此选项显示“ 评估策略”对话框，该对话框可用于查看SQL Server实例上的所有现有策略，并手动检查要针对已声明的目标对象进行评估的策略。 该列表包括“ 类别”列，使概述过程和适当的策略选择更加容易。

SQL Server策略订阅功能 (SQL Server policy subscription feature)

The SQL Server policy subscription feature is applicable to SQL Server databases and their objects (e.g. tables, stored procedures, etc.) only. Policies which use SQL Server instances and their objects (excluding databases) cannot be used for the subscription feature.

SQL Server策略订阅功能仅适用于SQL Server数据库及其对象（例如表，存储过程等）。 使用SQL Server实例及其对象（不包括数据库）的策略不能用于预订功能。

Policy categories provides a possibility for databases to be subscribed to certain sets of policies. By subscribing a database to the specific policy categories, we declare that all of the policies in the policy categories apply to the particular database. Similarly, by unsubscribing the database from certain categories we declare that the policies from the policy categories don’t apply to the database.

策略类别为数据库预订某些策略集提供了可能性。 通过为数据库订阅特定的策略类别，我们声明策略类别中的所有策略都适用于特定的数据库。 同样，通过取消订阅某些类别的数据库，我们声明策略类别中的策略不适用于数据库。

The policy categories, as we previously described, can be set as mandatory or not for all SQL Server databases via the Manage Policy Categories dialog. Policy categories that are configured with the mandatory subscription are tied to all databases and databases cannot be unsubscribed from them. On the other hand, non-mandatory subscriptions are available for optional database subscriptions to the policies that belong to the category.

如前所述，可以通过“ 管理策略类别”对话框将所有SQL Server数据库的策略类别设置为强制性或非强制性。 使用强制订阅配置的策略类别绑定到所有数据库，并且不能从数据库中取消订阅这些数据库。 另一方面，非强制订阅可用于对属于该类别的策略的可选数据库订阅。

The fact a database is subscribed to the certain policy category does not mean that all category policies apply to the database and all objects in the database. As we explained in one of the previous Policy Based Management related article, policies can use custom defined filters for narrowing the scope of target objects, which can eliminate specific objects from the policy scope. However, if a database is not subscribed to a policy category the database and all database objects are explicitly excluded from the scope of the policies within the policy category.

数据库已订阅特定策略类别的事实并不意味着所有类别策略都适用于数据库以及数据库中的所有对象。 正如我们在先前基于策略的管理相关文章中所解释的那样，策略可以使用自定义定义的过滤器来缩小目标对象的范围，从而可以从策略范围中消除特定的对象。 但是，如果数据库未订阅策略类别，则该数据库和所有数据库对象将从策略类别内的策略范围中明确排除。

There are a number of scenarios where optional categories can be used. Usually, the policy administrator is not, nor should be, aware of existing databases. His scenario role is to declare policies and appropriate categories while the owner of a database can choose which policy categories apply and which don’t to the database. For example, a policy category can be appropriate for production but not for development databases. As there is no available target filter that can make the difference between the two database purpose types, there is no other proper way to tie the policy category to the appropriate databases only. Such policy categories should be non-mandatory and optionally available for subscription by appropriate databases.

在许多情况下都可以使用可选类别。 通常，策略管理员既不也不应该知道现有数据库。 他的方案角色是声明策略和适当的类别，而数据库所有者可以选择哪些策略类别适用于哪些策略类别，哪些不适用于数据库。 例如，策略类别可能适用于生产，但不适用于开发数据库。 由于没有可用的目标过滤器可以使两种数据库用途类型有所不同，因此没有其他适当的方法将策略类别仅绑定到适当的数据库。 此类策略类别应是非强制性的，并且可以选择由适当的数据库进行订阅。

To subscribe a database to the particular policy categories:

要将数据库订阅特定的策略类别：

1. Categories option from the Policies context menu of the database in 对象资源管理器中 ，从数据库的“策略”上下文菜单中选择“ Object Explorer. 类别”选项。

2. The Categories dialog will show all mandatory and optional policy categories. Using the checkboxes in the Subscription column, subscribe (or unsubscribe) the database from appropriate policy categories. Additionally, expand a category name to review policies that belong to the category.

   “ 类别”对话框将显示所有强制和可选策略类别。 使用“ 订阅”列中的复选框，从适当的策略类别中订阅（或取消订阅）数据库。 此外，展开类别名称以查看属于该类别的策略。

   

You cannot unsubscribe from mandatory categories as their checkboxes are disabled, while optional policy categories have enabled checkboxes in the Subscription column. Note that sysadmin or database owner role permissions are required to modify database policy subscriptions.

您不能取消强制性类别的订阅，因为它们的复选框已禁用，而可选策略类别已在“ 订阅”列中启用了复选框。 请注意，修改数据库策略订阅需要sysadmin或数据库所有者角色权限。

Policy categories are used for easier organization and overview of declared policies, and for declaring a policy targets scope by subscribing databases to certain categories. Setting a policy category as mandatory or optional depends on the scenario in which a subscribed database is used.

策略类别用于简化声明的策略的组织和概述，以及通过将数据库订阅某些类别来声明策略目标范围。 将策略类别设置为强制或可选取决于使用预订数据库的方案。

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