SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Policy Based Management - On Change Prevent, Doesn't

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...


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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by sunilbanani on 23 March 2009


I have sql server 2000 installed on my system. SQL Agent is running under local system account. Agent is in running state. I am not able to stop or restart agent services. Job create are getting failed due to login failure issue. Kindly suggest.


Posted by Steve Jones on 23 March 2009

Blog comments are not the place to ask questions. Please do so in the forums.

Posted by xuegang.huang on 30 March 2009

Hi Steve, it is an interesting posting. My question is how deep the "policy-based management" can be set. Is it possible to link the policy with certain server statictics so that a new configuration (e.g., amount of memories for different SQL Server components, amount of allowed memories for certain users, amount of threads allowed...) can be automatically set and running when the SQL Server performance is changed into a pre-defined profile?

Posted by Brian Clark on 31 March 2009

I wonder what kind of overhead PBM adds to a system? Does every SQL statement now have to be evaluated by the PBM engine after being executed?

Leave a Comment

Please register or log in to leave a comment.