Policy Categories
Policy categories have two purposes in SQL2K8: 1) organization of policies and 2) policy scope. In this posting we'll explore creating categories for each of these purposes.
Creating Categories
There are two ways to create a category: 1) the Manage Categories dialog and 2) The Policy Properties dialog.
To create a category from the Manage Categories dialog, connect Object Explorer to an instance of SQL2K8. Expand the Management node, right-click on the Policy Management and select Manage Categories.
This will launch the Manage Categories dialog. On this dialog you can create, update, and delete categories and change the subscription state for each category. I'll cover the subscription state later on.
To create a new category click in the Name field on the last row and type in the name for the category. This will create a new category which is mandatory for every database. Click OK to dismiss the dialog and you're done.
You can also create a new category from the Policy Properties dialog.
On the Description tab click New next to the Category field. This launches the new Create new category which accepts a category name. The category will be created as mandatory for all databases. You also use this dialog to assign a policy to a particular category. Use the category combo box to select a category.
Organizing Policies
There are a few key places where the use of categories simplifies certain actions.
Object Explorer Details
When the Policies folder is selected in Object Explorer, Object Explorer Details lists all of the policies on the system with a select set of properties, including Category.
The columns in Object Explorer Details can be sorted, just click on the column heading you wish to sort by.
Sorting on category can make it easier to perform certain operations.
Evaluate, Export, and Script are all valid operations on a set of policies. You could use ctrl-click to select multiple disjointed policies but I've found sorting to be quicker and less error prone.
View Policies Dialog
Right-click any object in Object Explorer and select Policies -> View to see the policies which apply (or can apply) to the selected object. The View Policies dialog is displayed.
Category is a property in the table which can be used to sort the policies. This makes it easy to see which policies in a particular category apply to the object or are in violation.
Evaluate Policies
The Evaluate Policies dialog allows you to manually evaluate one or more policies against a single object or against the computed target set (all objects the policies applies to).
This dialog includes the Category property which you can sort on. If you wish to run all of the policies in a particular category sorting them first prevents you from having to hunt and peck for each policy in a particular category.
In this section we looked at several places where using categories can expedite certain policy tasks. Now let's look at category subscriptions which controls the applicability of policies.
Delegation of Policy Applicability
Using categories databases can subscribe to certain sets of policies. Subscribe simply means that all of the policies in the category may apply or do not apply to the particular database. This is only applicable to policies which target databases or objects contained in a database. Policies which target the instance and instance objects (excluding database) are excluded from the subscription.
Categories can be configured for mandatory subscription, meaning all databases must subscribe to the category and cannot opt out, and non-mandatory subscriptions, meaning individual databases can opt-in/opt-out of policies in the category.
When a database subscribes to a category it's not implicit that all policies in the category apply to the database and all objects in the database. The policy may contain a target filter which eliminates certain objects from the scope of the policy. However, if a database does not subscribe to a category the database and all database objects in that databases are explicitly excluded from the scope of the policy.
There are certain situations where optional categories make sense. A set of policies may only apply to certain databases but the Policy Administrator doesn't know which databases the policies apply to. However the owner of the database does know and can choose which policy categories apply and which don't.
For example you may have a set of policies that only apply to reporting (read-only) databases, but only the database owner knows if their database is a reporting database (there is no database attribute to designate this type of database and therefore no way to construct a target filter). The Policy Administrator would create the policies for reporting databases in a category called "Reporting Database Policies". The category is configured to be optional. Each database owner can then choose to subscribe to the category or not.
Security Notice: You must be SA or DBO of the database to control policy category subscriptions.
This launches the Categories dialog.
Mandatory categories appear subscribed to and disabled - meaning they cannot be unsubscribed from. The "<Default>" category is always mandatory. Non-mandatory categories have the subscription checkbox enabled to allow the database owner to subscribe/unsubscribe as appropriate. When the category is expanded the list of policies in the category is displayed. To subscribe to the category select the checkbox under the Subscribed column and click OK.
Summary
Categories serve two purposes: 1) organization (group like policies together) and 2) policy scope. It's a best practice to categorize your policies by their purpose (such as industry compliance or security). Choosing to mark the categories as optional or mandatory depends on your particular situation.
Suggestion: If you have SQL2K and/or SQL2K5 servers in your topology and you want to create policies for these servers you can create policies on a SQL2K8 server. Create two non-mandatory categories, "SQL Server 2000" and "SQL Server 2005" and place each policy in the appropriate category. This will make it easier to track what the policies are for. If you automate the policies using PowerShell you can easily select the correct set of policies for each server version.
_____________
About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.
Comments
- Anonymous
July 07, 2008
PingBack from http://blog.a-foton.ru/2008/07/policy-categories/