SQL Server Policy-Based Management

Why does a property show up in > 1 facet? Why do some facets support CoC:Prevent, but others not?

sqlpbm — Fri, 13 Aug 2010 16:16:50 GMT

Last week, Shaun Stuart posted some questions on his blog about PBM "strangeness". He was wondering why some properties like "AutoClose" show up in more than one facet, and then why some of those facets support "check on change : prevent" but others don't.

I've reproduced my answer to Shawn, below ...

A property can appear in > 1 facet by design. Facets are based around usage patterns, and so a property like AutoShink may show up in > 1 facet. To a rough approximation, you can think of facets as like views.

The facet evaluation mode is the least restrictive (MIN) over all the facet properties. Suppose a facet F has three properties, P1, P2, and P3. Suppose that it is possible to rollback a transaction when you try to set P1 and P2, but the setter on P3 is non-transactional. Then, F cannot support the CheckOnChange:Prevent mode.

There are a few underlying reasons that facets have different evaluation mode capabilities. In the discussion below, “CoC” means “check on change” and “CoS” means “check on schedule.”

Some property setters do not map to DDL statements that can be rolled back in a trigger. Any facet containing such a property will not support CoC:Prevent.

Some property setters do not map to events supports by EVENT NOTIFICATION objects. Any facet containing such a property will not support CoC:Log (side note: use select * from sys.event_notification_event_types to see supported events).

To see a list of Facets along with their supported evaluation modes, see http://blogs.msdn.com/b/sqlpbm/archive/2008/05/24/facets.aspx

All this said, I can see why a customer like you would be confused. I have added these design change requests (DCRs) for the next release.

a) Show the supported facet evaluation modes in the “Object Explorer Details” page.
b) Show the supported facet evaluation modes in the “General” tab page of the “Facet Dialog”
c) Make the facets and their evaluation modes programmatically discoverable via SQLPS.
d) Add the table from the PBM blog page above and add it to the books on line.

Best regards,
Clifford Dibble
SQL PM

After renaming computer, "check on schedule" policies don't work. How to fix ...

sqlpbm — Mon, 02 Aug 2010 17:55:00 GMT

Hi,

If you rename your computer, then "check on schedule policies" won't work because the old machine name is embedded in the SQL Agent jobs.

To make this (and a lot of other stuff) work after a machine rename, execute the following procedure: "How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server"

Regards,
Clifford Dibble
SQL PM

Clarification around "Data and Log Files ..." and "Backup Files ..." policies

sqlpbm — Mon, 02 Aug 2010 17:45:16 GMT

Hi,

We ship these two Best Practices policies

Unfortunately, neither of those two policies can detect separate physical devices through Windows mount points. We will clarify the documentation on this and look to get this working across mount points in a future release.

Cliff Dibble
SQL PM

Clarification on the "Guest Permissions on User Databases" policy

sqlpbm — Mon, 02 Aug 2010 17:28:00 GMT

We ship a "Microsoft Best Practices: Security" policy called "Guest Permissions on User Databases". Unfortunately, the wording in the UI and BOL is not clear about whether this can/should apply to msdb.

Here is what is says today:

"You can do this by executing REVOKE CONNECT FROM GUEST" from within any database other than master or tempdb."

Here is what it should be (and will be in the next major release):

"You can do this by executing REVOKE CONNECT FROM GUEST" from within any database other than master, msdb, or tempdb."

For more information on the "guest account in msdb" issue, see Buck Woody's recent post titled "Don’t mess with the system databases in SQL Server, or Error: 916"

Regards,
Clifford Dibble
SQL PM

Generate the List of All Available Facets and Their Properties

sqlpbm — Mon, 20 Apr 2009 20:40:40 GMT

Our colleague Jens Suessmeyer in Germany has figured out how to generate the list of all facets and their properties. He has posted the list on his blog. Below is the code snippet from Jens:

foreach (FacetInfo info in PolicyStore.Facets)

{

     Console.WriteLine(info.Name);

     foreach (System.Reflection.PropertyInfo p in info.FacetProperties)

     {

          Console.WriteLine("\t" + p.Name);

     }

}

About the Author:

Hongfei Guo is a PM on the SQL Server Manageability team at Microsoft

Policy Evaluation Modes

sqlpbm — Tue, 14 Apr 2009 03:39:53 GMT

I hosted a PBM break out session in Microsoft MVP Summit 2009 in March. One feedback I got is that the rules for what evaluation modes a policy support are obtuse. I thought that would be a good topic to cover in the blog.

In PBM, users can specify the “Evaluation Mode” for a policy, as shown in the Policy Dialogue below:

The Evaluation Mode defines the combination of 1) when the policy will be evaluated and 2) what the system will do in case of policy violation. The following evaluation modes are supported:

Evaluation Mode	When to Check	Action upon Violation
On Demand	On demand only	Users can choose to configure the system to comply to the policy
On Schedule	Periodically according to the specified schedule	log
On Change – Log only	When there is a relevant (DDL) change to the DBMS	log
On Change – Prevent	When there is a relevant (DDL) change to the DBMS	Roll back the transaction

You may have noticed from the GUI that all policies support On Demand evaluation mode. Most policies support On Schedule evaluation mode. Among those, some policies support On Change – Log only. Again among those, some polices also support On Change – Prevent. As shown in the Venn diagram.

On Schedule

For 2008, we support On Schedule mode for all policies referring to facets on the Database Engine. There are two facets that are not in this category: ISurfaceAreaConfigurationForAnalysisServer is defined for the Analysis Services and ISurfaceAreaConfigurationForReportingServices is defined for the Reporting Services. We only support On Demand mode for policies defined on these two facets.

On Change

A policy supports On Change modes if and only if both of the following rules hold:

1) The facet referred to by the policy supports On Change modes

2) If there are any target-set-filter conditions (see the Policy Dialogue Figure) specified in this policy, they are all Naming conditions (conditions in the form of <@name, [Operator], [value]>) and there is no server restriction condition.

let’s look at the first rule first. The On Change evaluation modes are implemented using the DDL eventing mechanism of SQL Server. And the “roll back” action relies on transactional support of the corresponding DDL statement. The change of some facet properties does not trigger any event, for example, database size in the Database facet. And accordingly we don’t support On Change modes for such facets. For some facets, although there are always events for every facet property, there is no transactional support for the DDL statement. For such facets, On Change – Log Only mode is supported, but not On Change – Prevent. IDatabaseOption facet is such an example. You can use the following query to check out what evaluation modes a facet support:

select * from msdb..syspolicy_management_facets

The execution_mode column is a bit mask defined as follows:

4 (100): On Schedule

2 (10): On Change - Log Only

1(1): On change - Prevent

The following query checks what events are monitored for a facet supporting On Change modes:

select * from msdb..syspolicy_facet_events

Next let’s look at the second rule. In a policy you can use conditions to filter the set of objects the policy applies to. Theoretically, you can use any condition to filter out the targets. However, there is a tradeoff between flexibility and performance. The performance penalty in allowing arbitrary filter conditions becomes significant with On Change evaluation modes since the policy evaluation is done alongside the transaction. It is less an issue for On Schedule mode since users can choose to run the policy at off-peak time. For that reason, we made a tradeoff:

1) For the common use cases where the filter is a “name condition” on Database, that is: <@name, [Operator], [value]>, we allow the full set of applicable evaluation modes because there is an efficient way in calculating the target set.

2) For the use cases where arbitrary filter conditions are needed, we allow the flexibility but for performance reason we don’t allow On Change evaluation modes.

The syspolicy_conditions_internal.is_name_condition indicates if this condition is a “name condition” (when the value is not 0).

Is_name_condition has the following enum values (representing the operator in the filter condition):

0 : None

1: Equals

2: Like

3: NotEqual

4: NotLike

Summary

Not all policies support On Change evaluation modes. We only support On Change evaluation modes for a policy when the referred facet has DDL event coverage and the target filter conditions for the policy are simply Naming conditions if there are any.

About the Author:

Hongfei Guo is a PM on the SQL Server Manageability team at Microsoft

Defining Complex Server "Health" Policies in SQL2K8

sqlpbm — Fri, 12 Sep 2008 06:07:25 GMT

Bart Duncan (a dev on the manageability team and one of the masterminds behind the Management Data Warehouse and Data Collector feature in SQL Server 2008) has a blog posting where he walks through using PBM to author a complex server "health" policy.

Out-Of-Box Policies

sqlpbm — Tue, 15 Jul 2008 23:10:01 GMT

SQL Server 2008 includes several predefined policies. These are generally associated with best practices and overtime the rules from the Best Practices Analyzer will be implemented as policies for PBM. A number of these have already been implemented and are available to you in one of two forms:

First, they're installed as part of every Management Studio installation. There is no specific feature option to select in the setup GUI, just install Management Studio (Management Tools - Basic) and the policies will be installed. The policies are placed on your local machine in the form of XML files. These files can be imported into an instance of SQL Server 2008 or can be run directly from the XML files. The location of the files depends on where you chose to install the client tools. On a default install, you'll find them here: C:\Program Files\microsoft sql server\100\Tools\Policies

The second way to get the policies is to download them from the feature pack. Here's the link to the RC0 feature pack. If you scroll down the page you'll find an entry for Microsoft SQL Server 2008 Policies. You can download and run the MSI. You'll have an option to install the policies to any location you like.

It's a good idea to periodically check for updates to the policies. Overtime we'll add new policies, change existing ones as needed and remove out-dated policies. If you decide to modify one of these policies we recommend you move it to a different location to avoid it from being overwritten by an update.

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.

Policy Categories

sqlpbm — Mon, 07 Jul 2008 22:06:18 GMT

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.

Wide Format

sqlpbm — Sun, 06 Jul 2008 20:24:50 GMT

You may notice I've changed the blog format from narrow to wide. I think this makes it more readable as it uses more screen real estate - less scrolling! Let me know what you think. Or let me know if you think I'm obsessing too much about something insignificant and I should be directing that energy to more postings.

Cheers,
Dan