To help database professionals strengthen SQL Server, the SQL Server Development
Team introduced the SQL Server Best Practices Analyzer (BPA), a tool that checks
your SQL Server for common best practices and when it finds something outside
of the norm, helps you ensure that you're implementing the conventional wisdom
on SQL Server configurations. In its first release (with SQL Server 2000), BPA
checked more than 70 parameters on a SQL Server instance and in individual databases.
The Microsoft SQL Server Manageability team, led by program manager Paul Mestemaker,
has introduced many improvements in the SQL Server 2005 BPA. The tool
includes a predefined list of SQL Server 2005 recommendations and best practices,
which it compares against the settings in use in the current database environment,
pointing out potential problems as it finds them. In addition, the SQL Server
2005 BPA includes the following upgrades:
Better rules. The SQL Server customer support team was directly
involved in developing new rules that address common SQL Server 2005 problems.
The rules address the relational engine and security settings as well as settings
in SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).
In addition, a feedback component lets users report new findings that Microsoft
can channel back to active BPA users through an auto-update feature.
Better XML integration. You can save the results of BPA scans
as XML (instead of only to a SQL Server repository). Plus, you can save scan
setup settings as XML and later reuse them through the GUI or command-line interface.
Better reporting. A new pivot feature in the scan report makes
it easier to see problems and remedies. New search filters make it easy to find
specific elements of a report and greatly simplifies scans of large, multi-server
environments. The scan report also includes more information. For each potential
problem, BPA provides an article describing how the problem was detected, background
information about the problem, how to remedy the problem, and where to get more
information.
Better BPA UI. You can set BPA to automatically run during low-utilization
times. You set up automated scans through XML or the new command-line interface,
SQLBPACmd.exe, which offers granular control of which rules to scan. SQLBPACmd.
exe is automatically installed in your client's <drive>\Program Files\SQL
Server Best Practices Analyzer directory. Check the product documentation for
full details about how to use the BPA command-line interface.
Note that the T-SQL code-checking best practices have been removed from this version of BPA. Microsoft has given no official word about whether similar code-checking
features will appear in another tool, such as a Visual Studio plug-in.
For each server that the tool scans, BPA will consume 5MB–10MB of RAM,
use 50–75 percent of CPU, and require 0.5MB–2MB of disk space
to store XML data. Therefore, I recommend running BPA on a client against a
target server rather than on the server that you're scanning.
You can read about the SQL Server 2005 BPA in the SQL Server Manageability
Team blog at http://blogs.msdn.com/sqlrem/archive/2007/01/12/
SP2-and-BPA-FAQ.aspx and at http://blogs.msdn.com/sqlrem/default.aspx.
You can send Paul Mestemaker and the team your ideas through the SQL Server
Connect page at https://connect.microsoft.com/SQLServer.
And of course, we want to hear your feedback on the Tool Time discussion forum
at http://www.sqlmag.com/go/tooltime
End of Article
