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.