SQL Server 2008 introduces "Policy Based Management" (PBM) as a way to better manage your servers. This was a feature that I really thought had, and still has a lot of potential for making the life of a DBA easier.

The tagline for PBM is "manage by policy" and that is a great description of what you can do. Essentially you set up a series of rules on anything from the amount of memory that should be configured to naming conventions to whatever interesting things you can write with in PBM expressions. There is a great deal of possibilities here, and I think it's a great addition to SQL Server 2008.

That's not to say that it's not without bugs or problems. There are a few things that I think could be improved, and I'll tackle more details in future posts.

Today I wanted to note that one of the "enforcement" methods for dealing with a failed policy, or rather, some action that would break the policy, is the on change - prevent method. It sounds as though the policy would prevent some change that might break the policy, and that's what happens...

eventually.

The policy enforcement is through triggers and this means that the action is completed, the trigger checks for compliance, and then when it fails, the trigger rolls back the work. However, the work gets done, and then undone.

So imagine that you have a policy not to delete rows in some table for auditing purposes. If someone issues a delete of 1,000,000 rows, they get deleted, the work gets done, and then the trigger rolls things back.

Not exactly the workload you want on your production server.

Ideally the server would check for a policy violation before executing a statement and if it determined the policy is being violated, the user is informed immediately, and no work would be done.

I'm hoping Microsoft realizes this as an issue in SQL Server and finds a way to implement some type of "before" trigger to handle this in SQL 11 or SQL 12.