The SQL Server Best Practices Analyzer and Policy Based Management

The SQL Server Best Practices Analyzer and Policy Based Management

Rate This
  • Comments 4

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.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • If the BPA does not include checking VLF count, it should be.

  • Interesting... I'm running x86 Dev 2K8 R2 edition on WinXP SP3; BPA is not supported on this setup, 'cause (pre-req) MBCA won't install on WinXP.

  • Interesting... I'm running x86 Dev Edition 2K8 R2 edition on WinXP SP3; BPA is not supported on this setup, 'cause (pre-req) MBCA won't install on WinXP.

  • AFAIK, BPA rules are fixed and cannot be changed or augmented by the customer.  PBM, OTOH, lets the customer create arbitrary rules over arbitrary target sets.

Page 1 of 1 (4 items)