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.