SQL 2008 Policy Based Management Strangeness

As I mentioned last time, I’ve started playing around with SQL Server 2008’s policy based management functions. So far, I’m liking what I see. I have several SQL Servers I am responsible for and I think this will provide a nice way for me to ensure the servers and databases are configured they way I want them to be – and that they stay that way. I found a couple good articles that walk you through setting up policy based management.  One in particular that I like is this one from mssqltips.com which sets up a policy to monitor the number of your virtual log files. (See Kimberly L. Tripp’s blog post 8 Steps To Better Transaction Log Throughput for details of why you should be checking this on  a regular basis.) SQL 2008 ships with some pre-defined policies that are based on best-practices. They are installed with SSMS by default and you can import them into SSMS to start using them. (See http://blogs.msdn.com/b/sqlpbm/archive/2008/07/15/out-of-box-policies.aspx for details.) But for purposes of this article (and for my own education), I will be creating policies from scratch. I won’t go into the details of facets and setting up conditions and policies in order to use the policy based management tools. I think the mssqltips article does a good job of that. What I will be talking about is the behavior of this tool, which in some instances, leaves me a bit puzzled.

In my testing and experimenting, I have really only been checking a single database property – the AutoShrink property. I want to make sure this is set to False for all my databases. (See Paul Randal’s blog post here for why.) Before I get too into things, let me step back and mention that policies can be evaluated at four different times, called Evaluation Modes – on demand (manually), on a defined schedule (with SQL Agent), and when the underlying property you are watching changes (called OnChange). This last option is broken down further into two options – log only and prevent. Log only will simply note the change (and policy violation), but allow the change to happen. Prevent, on the other hand, will roll back the change so that your policy is strictly enforced. Which OnChange Evaluation Mode you can use is dependent, according to http://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx, on if the referenced facet has DDL event coverage. Fair enough. But this is where I see the first of two strange things happening.

Strangeness The First

As I mentioned, I am doing my testing looking at the AutoShrink database property. When I made my first policy, I defined my condition using the Database Options facet. Makes sense to me, since AutoShrink is a database option. Here is my condition definition.

Later on, I discovered the AutoShrink property can be found in two other facets – namely, the Database Performance facet and the Database facet. So, for testing, I made a condition using each of those facets:

You’ll note the check expression is identical for each of these conditions: @AutoShrink = False.

My next step was to create a policy using each of these conditions. Here are my three policies:

Notice anything strange? Of the three policies, only the policy created using the Database Option facet gives me the On Change: Log Only evaluation mode! Why? Each of these policies is checking exactly the same thing. Obviously, this has something to do with the way Microsoft has created and implemented facets. But by placing properties in more than one facet, we get into this situation where you can have policies that check the identical property but have different evaluation modes based on how you (or someone else) happened to create the condition.

Ok. So why do I care? This is where I ran into the second strange behavior.

Strangeness The Second

When a policy is checked and a violation is found, SQL Server will either raise an alert or not. It all depends on how the policy was evaluated. If you manually evaluated the property, no alert is raised. Instead, you get a dialog box that shows the violation with a big red X. But if the policy was evaluated any of the other ways (scheduled or via an OnChange event), SQL will raise an alert. You can then configure Database Mail to send an email or other notification when one those these alerts is raised. (See http://technet.microsoft.com/en-us/library/bb510667.aspx for a list of the alert message numbers.)

Given that I am checking a database-specific property, I would really like it if my alert included the specific database that violated the policy. That’s not too much to ask, I think. The policy that uses the On Change: Log Only evaluation mode sends me the following alert when I set a database to AutoShrink = True:

DATE/TIME:  6/1/2010 11:18:48 AM

DESCRIPTION:      Policy ‘AutoShrink Is False Policy-dbOptionFacet’ has been violated by target ‘SQLSERVER:\SQL\EICOMDB\DEFAULT\Databases\SJSTestDB’.

COMMENT:    (None)

JOB RUN:    (None)

Nice! Exactly what I want! The alert tells me which database violated the policy and I can go fix it. But wait! The policy that uses the Scheduled evaluation mode, sends me the following alert:

DATE/TIME:  6/1/2010 11:20:01 AM

DESCRIPTION:      Policy ‘AutoShrink Is False Policy-dbFacet’ has been violated.

COMMENT:    (None)

JOB RUN:    (None)

Suck! No database name! So if I got this message, I’d need to connect to the server handling my policy management, right-click the policy, choose View History, expand the history entry, and resize the lower window to see which database violated the policy.

That’s a lot of extra work, especially when I KNOW another option exists for providing the info I need in the alert email itself.

So…. You can see how I got on this track. I obviously want to use the On Change: Log Only evaluation mode for policies whenever possible, as that provides me with the most information in my alert. However, depending on how the policy was created and which facet it is based on, the On Change: Log Only evaluation mode might not be available. But if the policy is able to be re-written using a different facet to check the exact same condition, that evaluation mode might be available.

Holy cow.

Get your act together Microsoft. I know Policy Based Management was a new feature in SQL Server 2008, but is it asking too much to for it to have some sort of consistent behavior? Is this fixed in 2008 R2? And will Policy Based Management even be around in the future? I am loathe to spend any amount of time navigating the various idiosyncrasies of the tool to get it to perform they way I want if it is going to disappear in the next version.

Does anyone have any insight into this behavior? Or suggestions?

Update: This Microsoft white paper talks about how different facets have different Evaluation modes. They don’t really explain why, but at least they mention it.

6 thoughts on “SQL 2008 Policy Based Management Strangeness

  1. Shaun – Thanks for the feedback. This is a great post.

    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

  2. Just a follow up to my last comment.. What I would really like would be for the text in the alerts for the different evaluation modes to be the same and to include enough information for me to fix the problem, or at least point me where to go (other than the policy manager server). For example, if the policy failed is being evaluated against a database, include the name of that database.

  3. Hi there,
    I wander if the inconsistency you’re mentioning in your post was updated/improved in the newer versions (2012/2014).
    Can you confirm/dis-confirm ?

  4. Roni – truthfully, I haven’t used policy based management functionality much, so I don’t have an answer for you.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.