SQL Best Practices Analyzer - II

I have already blogged about this in a previous post of mine (Best Practice Analyzer) but I was recently working on the SQL Server 2005 version of the tool and thought that this deserved a second mention.

This tool has been enhanced a lot and provides a great deal more information than it's SQL 2000 counterpart. This creates a XML data output file in your %appdata%/Microsoft/SQL BPA folder. This output can be imported using the SQL BPA UI and then a set of reports can be generated to check the following:

Gathers configuration information from an instance of SQL Server.

Performs specific tests on the instance of SQL Server.

Proactively verifies that the configuration is set according to recommended best practices. Some high level checks are even performed on the Operating System level.

Reports all settings that differ from the default settings.

Reports recent changes in the instance of SQL Server.

 

On a broader level, the tool verifies the above mentioned based on rules divided into the following categories:

1. Security rules
2. Database Engine rules
3. Analysis Services rules
4. Replication rules
5. Integration Services rules

For example, if you applied the initial release version of SQL Server 2005 SP2, existing SQL Server 2005 maintenance plans and SSIS packages that contain cleanup tasks might run those tasks at shorter intervals. The tool if it scans your SQL Server instance and finds out if you are on a build lower than the one mentioned in KB933508, then it would provide the recommendation to apply the fix.

The SQL BPA UI can be used to run scans on remote machines also. So, there is no need to install the tool on the SQL Server box which you want to scan. You can also configure the type of scan you want the SQL BPA tool to perform on your server.

Microsoft PSS also has the capability to include this tool as a part of the PSSDIAG collection that they send out to collect diagnostic data from the instance based on the need to collect BPA analytics data.

For a more detailed information about the above mentioned points, you can always refer the SQL Server Best Practices Analyzer Help chm file.