SQL SERVER – 2005 Best Practices Analyzer (January 2008)

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. With this tool, you can test and implement a combination of SQL Server best practices and then implement them on your SQL Server. The SQL Server 2005 Best Practices Analyzer gathers data from Microsoft Windows and SQL Server configuration settings. Best Practices Analyzer uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

DOWNLOAD TOOL HERE

Best Practice Analyzer (BPA) Tutorial

Abstract courtesy : Microsoft

How to Use the Best Practices Analyzer Report Wisely

A tool like this is most useful when you treat its report as a list of questions, not a list of orders. Each finding tells you that a setting differs from a general recommendation. It does not know your application, your hardware or the reason someone made that choice years ago.

Here is how I work through a report:

• Run the scan on a test server first, so you know how long it takes and what it touches.
• Start with the most serious findings, and read the explanation for each one before you change anything.
• Fix one group of settings at a time, and write down what you changed and why.
• Run the scan again after the changes to confirm the finding is gone and nothing new appeared.
• Keep the old reports. Comparing them over months shows whether your servers drift away from your standards.

Some findings will not apply to you, and that is fine. A recommendation for a large OLTP server may not fit a small reporting box. When you decide to ignore one, note the reason, so the next DBA does not waste time on it. A short comment in your runbook is enough. Share the final report with the team, so everyone knows which settings were changed on purpose.

This tool was built for SQL Server 2005. Later versions of SQL Server got their own releases of the analyzer, and Microsoft now also offers the SQL Assessment API, which you can run from PowerShell. Whatever tool you use, the approach above stays the same.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.