With an Iron Fist (SQL PBM)

Maybe you were a system admin that got sucked into the exotic land of database administration because there was no one else to do it. Maybe you are a DBA on purpose. It might even be possible that one day you were writing web applications in your remote and intentionally isolated cube at the far end of the “trailer park” and faster than you can say DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS you found yourself in charge of an unwieldy, quickly expanding and barely governed SQL Server environment.

In any case, you may have wondered, if there were some way to control the environment of SQL Server proactively and to have an automated way to enforce all of those naming convention standards and object settings that you TALKED about, documented and trained on. Have I got news for you!

In SQL Server 2008, a new feature called Policy Based Management (PBM) was introduced. The idea is that if you have a setting or convention that you wish to check for and/or ENFORCE (get your control freak on), you just create a policy that defines the object (maybe a database), a facet (the property that can be checked for or enforced, like the recovery model of a database), and a condition (the value of the property, like Full for the recovery model). Systems Administrators (yes the “A” is capitalized!) will recognize this concept as having some similarity with Group Policy in Active Directory Domain Services, although the application and verification of the policy is done quite differently.

The really fun part is that using Central Management Servers in SSMS, you can enlist one or more instances to monitor or enforce, manually or on a schedule, from one instance. You can do ad-hoc policy checks and enforce the policy on objects that are not in compliance and you can include all objects of the type within an instance or you can un-enlist individual objects.

Some examples of the types of policies you might create and assign might be controlling role membership and preventing future modification by doing a ROLLBACK when someone is added (sounds like Restricted Groups in AD), or perhaps you need to make sure that every instance in your organization must use Windows Authentication only. Maybe, instead of asking nicely and resending out that paper policy that governs object name conventions, you just need to make those ornery developers name all of their user stored procedures with a “usp_” prefix (I love you guys, really). Whatever the standardization need, PBM probably has the property you need to get a handle on. And standardizing is the whole point. It is an interesting exercise to document standards. It is a whole lot more satisfying to be able to inflict -- um – enforce them.

Hopefully, I will be speaking on this topic for SQL Saturday in Kansas City, on October 2, 2010 http://www.sqlsaturday.com/53/eventhome.aspx or you can spend some time trying it out in one of our Microsoft 6158 classes in Leawood, KS, (many of which can be attended remotely) http://www.centriq.com/CentriqSchedule.aspx.

Now go forth and standardize!


- Peter Trast, SQL Expert; MCITP DBA, MCITP EA, MCT LinkIn with Peter