My Favorite Blogs

Tuesday, July 27, 2010

Microsoft SQL Server 2008 R2 Best Practices Analyzer how to audit or analyze SQL Server Configurations.

What is Best Practices Analyzer (BPA)?
Microsoft Best Practices Analyzer is a diagnostic tool which uses to collect information about a physical Server and SQL Server instances. The tool will verify the settings and configurations that are according to the recommended best practices by Microsoft. This tool will lists all configurations and settings differ from the best practice recommendations and list potential problems in the installed SQL server instance and recommend the resolutions for the problems.

Microsoft SQL Server 2008 R2 BPA is pre-requisite installed Microsoft Baseline Configuration Manager (MBCA V2) framework and PowerShell V2.0 on either on Windows Server 2003/2008/2008 R2 or Windows Vista or Windows Vista operating systems, MBCA is a new framework comes with Windows Server 2008 R2 and Windows 7 versions and it is a new standard and utilizes a model architecture made up of xml and PowerShell scripts. It supports both local and remote execution of BPA against both local and remote servers. We can also use both GUI and command line scanning abilities.

This version of BPA supports the SQL Server Database Engine, SQL Setup, Replication, SSAS, SSRS and SSIS as well. This tool will give a proactive advice to the SQL Users about the settings of an installed SQL instance.

How to Install?
Make sure the Windows PowerShell installed on the server, the SQL 2008 BPA R2 is built on the MBCA V2 framework, so install the MBCA V2 first then install the SQL 2008 R2 V2 on the server. It has simple steps to install the tool and nothing specific to specify while install.

How to Analyze?

Microsoft Baseline Configuration Analyzer 2.0 (MBCA) is the interface for SQL 2008 R2 Best Practices Analyzer (BPA). Go to Start, All Programs , click on MBCA 2.0, you will get the GUI wizard to execute the SQL Server 2008 R2 BPA.

In the Select a Product Combo box select “SQL Server 2008 R2 BPA” (that would be the only one listed in) by default it will be connected to the local SQL instance to analyze and if needed you can connect to a remote computer by clicking “Connect to Another Computer” link on the right side of the combo box. Click on the “Start Scan” link just below the combo box (See Figure 1) to scan the SQL configurations as per Microsoft’s Best Practice Analyze standards.
Figure 1

When you click on the “Start Scan” the next screen will be popup to enter parameters to do the analysis. Enter the Physical Server name, SQL instance name (if differed from server name else same) in the text boxes and check the option boxes to scan the configurations to analyze (See Figure 2).
Figure 2

Click on the “Start Scan” hyper link and scanning will be started and in progress. (See Figure 3)

Figure 3

 Once the scanning process completed then you can see the Baseline Configuration Analyzer Report, on the report page you can find two tabs Non-complaint and all (see Figure 4), the Non-complaint tab will list all the errors and warnings. The below errors says that the backup of the databases in the instance are outdated mean that there is no recent backup for the databases. The Replication “Replication is not configured” error shown up because there is no replication configured in the instance but I selected the option to scan the replication settings.

Figure 4

On the All tab you can find the analyze results on each categories (see Figure 5)

Figure 5

I intentionally copied a backup file on the data drive before the scan process and it caught this as an issue and specified as “Database files and backup files exists on the same volume” and also given Impact and Resolution for the issue. (See Figure 6)

Figure 6 

The BPA tool analyze the recommended best practices by Microsoft to bring the SQL Server up to the standard to avoid any configuration or settings issues in an environment. The noted feature for SQL Server 2008 R2 BPA is able to scan both the local computer and remote computers.



  1. Hello Dude,

    Microsoft SQL serve is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions. In this section you will find information for several versions of SQL Server. Thanks a lot......

  2. Shading code extends that need to be done yet can hold up until the most elevated need home change tasks are finished. Experience the rundown and change the text style shading of these activities.

  3. the figure is important to learn the management system of Microsoft SQL server.

  4. Thanks for sharing us this informative blog. Really I like it. It's very helpful for us.