SQL Server 2008 Management Improvements – Policy Based Management

I’m here at the SQL Connections conference in April of 2008 and I’m giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post I’ll talk about one of the new features in SQL Server 2008, Policy Based Management.


One of the challenges you face as a DBA is trying to maintain consistency across your organization. With rules like Sarbanes-Oxley (here in the States), ITIL and other standards you have to maintain, it’s not a luxury to have things done a certain way, it’s a requirement. We created Policy Based Management (PBM) to handle situations like this, but I think you’ll find as you discover more about it that you can use it for even more.


First, let’s talk about what PBM actually does. Put simply, it allows you to specify settings and states on your SQL Server Instance and it enforces those settings for you, and/or tracks them.


Take a moment and digest that – it’s bigger than it looks. You can probably already think of something that you want to enforce, such as the Database Recovery Model or the “Auto Close” setting, but there are other applications of this feature, all the way down to where developers put database objects or how they name them.


Let’s look at this using a simple example, one we use quite often in our briefings. Let’s assume that you have a particular naming convention you use in your organization. Perhaps you want all stored procedures to start with the letters “usp_”. That’s a policy you want to enforce. With PBM, you do exactly that – you instruct the system to check the name of the stored procedure and ensure that it meets your requirements. You have the choice (in this case) of checking that when the developer tries to create the procedure, or you can check it after the fact and see which stored procedures violate your policy.


So how do you do that? Well, before we discuss the actual process we need to get a little terminology out of the way. The easiest way to do that, and in my opinion the easiest way to create a Policy in PBM, is to use a sentence that describes that you want to do.


“I want to ensure that no developer can create a stored procedure that starts with any name other than “usp_”.


Starting at the end of the sentence, we see that we are working with the name of a stored procedure, which is one of its properties. So the lowest level of a PBM Policy might appear to be a “Property” of the object – but in fact it isn’t. We thought long and hard about this, and in our discussions we realized that there are other bits of information you might want to track – things that either aren’t covered in a property of an object, or perhaps you might want to combine the common properties of lots of objects at one time. Since this is, in effect, a view of one or more aspects of a database object, we hit upon the name “facet”, meaning “a face or particular view of a single object”. So the lowest level of a PBM Policy is a facet. If it helps to think of this as a property, that will work just fine until you play with this feature more. Probably the biggest takeaway here is that the facet isn’t connected directly to the object – there are just tons of facets, some of them applying to multiple objects (everything has a name, for instance) and others applying to only a few objects or even just one. But they are defined there on the system, separated from the objects.


So the next part of the sentence, working backwards, is the object itself – in this case, a stored procedure. In PBM, we call these “targets”, since they are what the Policy “points at”. So now we have an object we want to talk about (stored procedures) and what we care about within that object (the name). We have the target and the facet.


Let’s continue on with the sentence. The next part is where it gets very interesting – it’s what we want to know about that facet (the name) of the target (any stored procedure). Remember that we said that we want to ensure the name “starts with usp_”. In a PBM Policy, this is a condition. Boiled down, it says “the name facet on a stored procedure target should be usp_ and then something else”.


Now all that is left is the way this PBM Policy is evaluated. What that really means is when the Policy is checked. There are few options here.


The first option, as we have described in our example, is to “Check on Change”. This means that the Policy (with our condition, target, and facet) is evaluated whenever a developer creates or modifies a target, such as a stored procedure. So that means whenever the developer creates or alters a stored procedure, the name is checked to ensure that it meets the Policy condition.


Because this particular object, a stored procedure, can be wrapped in a transaction, it has the ability to be “rolled back”. You’ve probably done this in code - start a transaction, do some work, check for errors, if there’s an error, rollback the transaction, and if there is no error, commit the transaction. In our sample Policy, that’s exactly what we want – only the error this time is based on our condition. While this can’t be done with every kind of database object (not everything can be wrapped in a transaction), there are a lot of things you can “enforce” this way.


If we create a PBM Policy with this evaluation, then any time a developer tries to create a stored procedure without the name starting with the letters “usp_”, it will be rolled back. By the way, you can set up a message to them, and the Policy will also log the violation.


The next kind of evaluation you can do is to check the policy manually. This means you point the PBM Policy at one or more servers and it will show you on the screen the databases that it evaluates, and whether they pass or fail. Like all the Policy runs, the results are logged to the Application Event Log in Windows and in the SQL Server Logs. Of course, since it is after the fact, it won’t prevent any actions – just report on them.


You can also schedule a Policy to be run on the server. That means that at a time you specify, the Policy will “wake up”, run, and log the results.


That’s quite a few terms – let’s look at them again, from the bottom to the top:


Facet – The lowest level in a policy. Think of it as a single property or a group of properties, but not connected to any particular object.

Target – The object type that you want to evaluate, such as a stored procedure, a table, or a database.

Condition – The evaluation you want to perform, such as “name must be X”, or, “must be set to the ‘Full’ Recovery Model”

Policy – All of the above objects together, along with the time the Policy runs, and any help information (including web links) you want to provide when the Policy is violated.


In future posts I’ll describe how you can use this technology to do more than just ensure compliance with a set of rules you have in your organization.


Here’s the link for this feature in Books Online: http://msdn2.microsoft.com/en-us/library/bb510667(SQL.100).aspx

Comments (2)
  1. bhlaws@gmail.com says:

    Thanks for the good description, Bucky. The info on MSDN is rather lacking, so this helps.

    I’ve been trying to figure out whether or not a configuration server can also work against SQL 2005 or even SQL 2000. I’m guessing not. Any idea?

    Also, I was thinking about running a configuration server under a VM. Do you know what the licensing policy is for a configuration server? I imagine you still need a full license the same as any other production server.



Comments are closed.

Skip to main content