The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.
This download is the August 2008 release of SQL Server 2005 Best Practices Analyzer.
Download Best Practices Analyzer
Abstract courtesy : Microsoft
How to Use Best Practices Analyzer Results Wisely
A tool like this is only as useful as what you do with its report. The first time I ran one on a busy server, I got a long list of warnings and my first thought was to fix everything that same night. That is exactly the wrong move. Some items are serious, some are simple advice, and a few may not apply to your setup at all.
Here is how I read a report like this:
- Start with anything about security, backups and database consistency checks. Those protect your data.
- Next, look at configuration warnings, such as settings that were changed from their defaults without a clear reason.
- Treat performance hints as ideas to test, not orders to follow.
- Write down every change you make, with the date and the reason, so you can undo it if needed.
- Keep a copy of the full report, so you can compare the next run with this one.
Make changes on a test server first whenever you can. A setting that is right for one workload can hurt another, and a checklist tool does not know your application. It only knows the general rules. You know your system better than any tool does.
This August 2008 release was built for SQL Server 2005, and the download link above belongs to that time. Microsoft later released newer versions for newer releases, and today the SQL Assessment API does a similar job for modern versions of SQL Server. The idea stays the same: compare your server against known good practice, then use your own judgment.
Run the check again after your changes. A clean second report is the easiest proof that your fixes actually took effect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
