Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the Microsoft SQL Server 2012 Best Practice Analyzer


By:   |   Read Comments (4)   |   Related Tips: More > SQL Server Configurations

Problem

Database Administrators are often asked questions like "are all the SQL Servers within the organization configured according to industry standards?" In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2012 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to best practices or not.

Solution

The Microsoft SQL Server 2012 Best Practices Analyzer (BPA) is an excellent tool which is available as a free download from Microsoft. Using the BPA tool, a DBA can quickly gather information from Microsoft Windows and SQL Server configuration settings. The BPA tool basically uses a predefined set of SQL Server 2012 recommendations and best practices to identify potential issues within the database environment. You can download the latest version of Microsoft SQL Server 2012 Best Practices Analyzer (BPA) for free from the following link.

The following are required for using SQL Server 2012 Best Practices Analyzer:

PowerShell V2.0
Microsoft Baseline Configuration Analyzer V2.0

Prior to the installation of Microsoft SQL Server 2012 Best Practice Analyzer you need to download and install Microsoft Baseline Configuration Analyzer 2.0 otherwise you will see the below screen when you double click SQL2012BPA_Setup64.MSI or SQL2012BPA_Setup32.MSI based on your environment when trying to install Microsoft SQL Server 2012 Best Practice Analyzer.

BPA Installation Error

Once you have successfully installed Microsoft Baseline Configuration Analyzer 2.0 and Microsoft SQL Server 2012 Best Practice Analyzer. You can use the BPA tool by clicking Start > Programs > Microsoft Baseline Configuration Analyzer 2.0. In Microsoft Baseline Configuration Analyzer 2.0, select a product such as SQL Server 2012 BPA as shown in the below snippet.

Configuration Analyzer Screen

Next, click Connect to Another Computer and choose Local Computer and then click OK to close the "Connect to Another Computer" window. Then in the Microsoft Baseline Configuration Analyzer 2.0 window, click Start Scan to begin the scan.

Connect to Another Computer

In the Microsoft Baseline Configuration Analyzer 2.0 Enter Parameters screen, you need to specify the SQL Server Instance Name, for a default instance you need to specify the instance name as MSSQLSERVER and for a Named Instance you need to specify the SQL Server Instance Name as shown in the below snippet.

Using Microsoft SQL Server 2012 Best Practice Analyzer you can scan the SQL Server Database Engine, Analysis Services, Replication, Integration Services, Reporting Servers and SQL Server Setup. You can choose the required parameters and click the Start Scan at the bottom of the screen to begin the scan.

Enter Parameters for BPA Scan

In the below snippet you can see the Microsoft Baseline Configuration Analyzer 2.0 is scanning to identify potential issues.

Once the Microsoft SQL Server 2012 BPA 1.0 has completed the scanning you will be able to see the Baseline Configuration Analyzer Report which will be categorized into Errors and Warnings as shown in the snippet below.

Error and Warning

Once you expand the Error category as shown in the below snippet, you will be able to see different errors that exist on the SQL Server Instance as per the rules configured in the Best Practices Analyzer.

BPA Configuration Analyzer Report

You can click on the Error to view the Detailed Explanation of the issue encountered and to resolve the issue follow the resolution steps mentioned.

BPA Analyzer Report

Advantages of Using SQL Server 2012 Best Practice Analyzer

  • Using this tool a DBA can determine whether the SQL Server is configured as per the best practices recommended by the SQL Server Products Team.
  • This tool validates your instance of SQL Server with certain built-in rules that can help you rectify common installation and configuration issues.
  • This tool recommends fixes for most of the potential problems on an instance of SQL Server and it's an excellent free tool to identify potential bottlenecks within your SQL Server Instance.

Disadvantages of Using SQL Server 2012 Best Practice Analyzer

  • I feel some of the resolution messages are not very clear and if you have any doubts I would recommend you to click the "More Information" option under each Error or Warning to get a complete understanding before implementing the suggestion.
  • This tool is a great starting point to identify issues, but this tools does not address every potential issue for a SQL Server instance.  You still need to educate yourself on best practices for setting up SQL Server.

Troubleshooting Issues with SQL Server 2012 Best Practice Analyzer Installation

  • As a best practice, a person who is installing SQL Server 2012 Best Practice Analyzer must have their Windows Account within the Windows Local Administrator Group and SQL Server Administrator Group on the server where this tool is installed.
  • If you receive any issues related to PowerShell, it is recommended to take a look at the Additional Information section of the Microsoft SQL Server 2012 Best Practice Analyzer Download Page.
Next Steps


Last Update:






About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, August 24, 2015 - 1:51:46 PM - Marc Back To Top

The tool never enables the "Start Scan" Button after selecting the local Server option. I ran it as local Admin and have sysadmin previleges. Anyone knows why?


Monday, February 25, 2013 - 12:00:53 PM - Greg Robidoux Back To Top

@PF - thanks for that catch.  Spell check strikes again.  The artcile has been updated.

Greg Robidoux


Monday, February 25, 2013 - 9:50:00 AM - PF Back To Top

"...categorized into Errors, Warnings and Complaints as shown in the snippet below."

One complaint about your article: are you aware of the difference between the words 'complaint' and 'compliant'? Microsoft BPA doesn't make complaints - only readers do.


Tuesday, June 26, 2012 - 10:45:30 AM - Bill Back To Top

I always found it funny that you can do a fresh install of SQL Server using the MS default settings and then run the BPA and find all sorts of errors.


Learn more about SQL Server tools