Now SQL Server 2008 has BPA too


This has been an ask from the community for a long time now. We just launched SQL Server 2008 R2 Best Practices Analyzer. The SQL Server rules are executed using the Microsoft Baseline Configuration Analyzer framework. The rules are all Powershell based. So you need Powershell 2.0 and MBCA 2.0 to use SQL Server 2008 R2 BPA. This version of BPA has an extensive set of rules which can validate a lot of commonly known issues in areas like SQL Setup, Engine, Replication etc. This makes the work of a DBA much easier. Another cool feature about this tool is that you can run remote scans also against other SQL instances.

Addendum: April 13th, 2011

Note: It works with SQL Server 2008 and SQL Server 2008 R2.

SQL BPA – Part 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.

  • Best Practices Analyzer


    I have seen a lot of people asking "Are we following best practices for our SQL Servers?". The Best Practice Analyzer can try and guide you along a path which would lead to the right answer. A simple answer is not possible because of the following reasons:
    1. We are not conversant with your environment and setup
    2. We do not know what kind of business implementation this SQL Sever is
    3. We do not know what constraints are present which prevents some of the best practices from being followed
    4. We do not know which data/databases are critical/non-critical etc. and also do not know what kind of SLAs need to be met.

    Even then, an attempt is made using the Best Practice Analyzer to verify if common best practices are being implemented across your SQL Server. It creates a repository on the our server and stores the analysis for the server in the database which can be used at a later date.
    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 tool is available at:
    For SQL Server 2000
    http://www.microsoft.com/downloads/details.aspx?familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
    For SQL Server 2005
    http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en