The SQL Server Best Practices Analyzer (BPA), a free download for SQL Server 2000, will help database administrators test and monitor instances of SQL Server against Microsoft's best practices recommendations. Regular use of the tool could help administrators maintain consistent operational procedures across their SQL Server infrastructure and improve the performance and efficiency of the underlying databases.

Maintain Performance, SQL 2005 Compliance

The BPA categorizes SQL Server best practices into a set of about 70 rules, which the tool groups into sets of operational categories, such as Backup and Recovery, Configuration, and Database Administration. For example, the Database Administration category includes a rule that checks levels of index fragmentation, which can grow as underlying database tables are updated, causing performance to degrade. Regularly checking the fragmentation level can help administrators identify appropriate times to either defragment or rebuild fragmented indexes.

In addition to checking SQL Server instances for compliance with best practices, the BPA can also examine whether databases will be compatible with SQL Server 2005. For instance, the BPA checks for user tables and stored procedures that will not work with SQL Server 2005.

Many rules are configurable: for example, the BPA’s default best practice for the interval between database backups is 30 days. Administrators can change this setting to whatever interval they consider appropriate. However, administrators cannot create their own rules.

The BPA comes with several prepackaged reports that can be distributed using SQL Server Reporting Services, a SQL Server add-on that supports managed reporting. For example, the tool includes a report that describes all the rules defined by the BPA and another report used to summarize the results of BPA compliance analyses. In addition, BPA analyses can be launched via a command-line tool, making it easy for administrators to include the BPA in their existing database administration or management scripts.

Availability and Resources

Available now, BPA runs on Windows 2000 Server, Windows XP, and Windows Server 2003 and requires Internet Explorer 6.0 or later and the .NET Framework 1.1.

The tool can be downloaded at www.microsoft.com/downloads/details.aspx?FamilyID=b352eb1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en.