THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

The SQL Server Best Practices Analyzer and Policy Based Management

The SQL Server Best Practices Analyzer (BPA) came out for SQL Server 2008 R2 recently, and I’ve been asked what the difference is between the BPA and Policy Based Management (PBM) that was introduced in SQL Server 2008.

While it’s true both of these tools can do similar things, each has strengths and weaknesses. The Best Practices Analyzer has a long history, and has various “rules” that compare settings on a server and provide guidance through some very nice reports. Many of these rules became Policies in SQL Server 2008. The BPA requires a separate install, PBM is installed with SQL Server 2008, and the reports are something you would have to create yourself. PBM can be run on a schedule, from a SQL Server Agent Job step or inside PowerShell, and BPA doesn’t do that out of the box. PBM also has a “SQL” task where you can define whatever you would like, BPA doesn’t have that capability in exactly that same way. 

Probably the biggest difference between the two tools, however, is that PBM can be set (under certain circumstances) to prevent an action from being taken. For instance, you can actually stop a developer from naming a database object in a certain way. Again, there are restrictions on this feature, but you can use it from time to time.

So which is better? Neither! Both have their uses, and in fact I use them both. One of the greatest strengths of Microsoft products is that you can usually do the same task in multiple ways. Of course, it’s one of our great weaknesses as well!

So as usual, the answer is “it depends”. You should learn more about both, and figure out what works best for you.

Published Thursday, July 8, 2010 7:09 AM by BuckWoody
Filed under: , ,



AaronBertrand said:

Just remember that PBM's "prevent" is poorly named.  In the current implementation, it is actually "rollback."  So, while you can set up a policy that you think will "prevent" a non-nullable column from being added to your 10 billion row fact table, the policy will actually allow the column to be created, then drop it.  A little tidbit that's useful to know *before* it happens to you.

July 8, 2010 10:57 AM

BuckWoody said:

Very good point, Aaron. As mentioned, one should read up on the options *before* implementing. Always good advice!

July 8, 2010 11:13 AM

AaronBertrand said:

Well I'm not even sure that limitation is properly documented in the official channels.  It sure has made its way into forums, twitter and Connect, though.

July 8, 2010 11:27 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement