By: Ashish Kumar Mehta | 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.
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.
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.
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.
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.
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.
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.
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
- Be proactive and check your SQL Servers to ensure they are meeting the industry best practices, so you can confidently answer management when they ask about your SQL Server environment.
- Additional tips on SQL Server Configuration can be found here
- Check out these related tips:
About the author
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
