Using the Microsoft SQL Server 2012 Best Practice Analyzer

By:   |   Updated: 2012-06-22   |   Comments (4)   |   Related: > 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.

image005

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-06-22

Comments For This Article




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

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 (22408)

@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 (22402)

"...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 (18201)

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.















get free sql tips
agree to terms