Policy Evaluation Modes

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:

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

Comments

• Anonymous
  June 16, 2009
  PingBack from http://fixmycrediteasily.info/story.php?id=7398

• Anonymous
  April 28, 2011
  I only have On demand and On schedule but cannot find On change - Prevent! How can I fix this?