In the recent articles (see below) about SQL Server Policy Based Management, we have explained the terms used by the feature, how it can be used in different environments and for various scenarios. We have also described how its basic purpose, evaluating declared rules against specific SQL Server instances and their objects, can be extended on other aspects of SQL Server such as SQL Server Audit is.

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 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:

1. Expand the Management node in SQL Server Object Explorer and select the Manage Categories option from the Policy Management context menu.
2. The Manage Policy Categories dialog will show all currently available policy categories.

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

   

4. In addition, uncheck the 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.
5. Click 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. Select the Properties context menu option of a policy in Object Explorer.
2. Switch to the Description tab of the dialog and click New.
3. Using the Create new category dialog, type in the name of the new policy category.
4. Click 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.

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 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.

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.

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. Select the 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.

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.