Following the original release of SQL Server 2008 R2 we are pleased to announce the associated release of the SQL Server 2008 R2 Best Practices Analyzer (BPA).
BPA is designed for administrators who want to determine the overall conformance of their SQL Server instances and topology with established best practices. BPA examines your SQL Server instance for system events, reviews login permissions, scans your SQL Metadata for advisable settings, and recommends solutions to items that do not conform with established Microsoft best practices.
BPA runs within the framework of the Microsoft Baseline Configuration Analyzer (MBCA 2.0) and supports both, SQL Server 2008 and SQL Server 2008 R2. It is supported on a variety of operating systems including Windows Server 2003, Windows Server 2008, Windows 7.
As always, we appreciate your feedback. Subsequent releases will "learn" from experiences of our customer base and we plan on expanding the best practices portfolio over time.
For more information and download information go here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
Dana H. BurnellProgram ManagerMicrosoft SQL Server
I've downloaded BPA but each time I try to install on my machine (Windows Server 2008 R2 STD x64) I get an "Access Denied" error when the installer tries to run the "Set-WSManQuickConfig -force" command. Strange enough, if I run the program by myself It works perfectly. I've also tried to run the installer using an administrative account and I get the same error.
Any idea that can help me to install BPA?
Same for me. Installer fails. I can't read error message because console window closing too fast. I am using Windows 7 64-bit. "Microsoft Baseline Configuration Analyzer 2.0" is installed.
@Evaldas: yes, also in may case the console log windows closes too fast. To be able to read it I had to use Camtasia to record the screen and the playback it in slow-motion :)
So you can't use this tool to scan remote computers unless they're in the same domain or you have a two-way trust? What a waste of time!
Hello - our support group has published reasons and workarounds for the issue you are having. Pls refer to this blog engry blogs.msdn.com/.../known-issues-installing-sql-2008-r2-bpa-relating-to-remoting.aspx for futher information.
Sorry for the inconvencience, Matthias Berndt
Hi, I have SQL Server 2008 access through domain group. It looks like BPA needs execution user account to be explicitly registered in SQL logins. Did you think about using XP_logininfo to check for sysadmin membership instead of querying sys.server_principals?
Well I've just wasted a few hours trying to get this working on a WORKGROUP Server for a Client after persuading him to install PowerShell 2.0.
All with simialar problems to the above.
I can't say I will bother with this tool again. Having to download the equivalent of an OS Upgrade and then install two seperate programs on a Clients (closely guarded) Production Server just to get a Basic recommended Practices report is not a viable option in the real world and whoever thought it was needs to reconsider their usefulness to the human race.
And I do SQL Healthchecks for a living for a MSoft Gold Partner.
Waste of Time & a Golden Opportunity lost due to someones incompetence
Grant, did you try the known workaround for installing on a workgroup? If not it is located here: blogs.msdn.com/.../known-issues-installing-sql-2008-r2-bpa-relating-to-remoting.aspx
I have a customer who’s insisting on running this tool to ensure we followed best practises when we installed SQL on their Windows 2008 virtual environment.
The biggest complaint is that the master, model and MSDB MDF and LDF files are on the same drive where SQL is installed (C:\). The TempDB’s MDF & LDF files are split over two individual drives that are dedicated to it (H:\ and I:\), and then all other databases’ MDF and LDF files are also split across two dedicated drives (D:\ and E:\). Keep in mind this is all running on a virtual environment.
Will this tool point out these type of best practises, or will it go deeper into the actual implementation of the existing databases?
Again, here I’m hoping it will point out what we can do since all these DBs are backups that was restored from a SQL2000 instance.
Thanks in advance
JB, The SQL 2008 R2 BPA utility is not quite that sophisticated. It has two checks that may help you. The first is making sure that the Data and Log files are on different drives. The second is making sure that the databases and backups are on different drives. That is as granualer as it gets. Hope this is helpful.
I would like the documentation on the rules in the BPA.
Reading the PS scripts gives me a hint, but I can not give the Audit, internal or external, a PS script as documentation. The documentation will be compared with the guides from CIS and DISA.
How can you export the results to Excel in in SQL Server 2005 BPA?
Just wanted to elaborate further with details.
In SQL Server 2005 BPA, we can export the result to EXCEL. But, in SQL Server 2008 R2 BPA, the option is not available. You can only export the results to xml. Does anyone know how to get the results in Excel? I have attempted to convert the xml file to Excel. But, the format doesn't look good at all.