By: Ashish Kumar Mehta | Comments (6) | Related: 1 | 2 | More > DBA Best Practices
Problem
Database Administrators are often asked questions like "are all the SQL Servers within an organization configured according to the industry standards?" In this tip, you will see how a Database Administrator can quickly use Microsoft SQL Server 2008 R2 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not. Database administrator can also use SQL Server 2008 R2 Best Practice Analyzer to analyze SQL Server 2008.
Solution
The Microsoft SQL Server 2008 R2 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 2008 R2 recommendations and best practices to identify potential issues within the database environment. You can download the latest version of Microsoft SQL Server 2008 Best Practices Analyzer (BPA) for free from the following link.
Once you have installed Microsoft SQL Server 2008 R2 Best Practices Analyzer (BPA). You can use the tool by clicking Start | Programs | Microsoft Baseline Configuration Analyzer 2.0. In Microsoft Baseline Configuration Analyzer 2.0, Select a Product as SQL Server 2008 R2 BPA as shown in the below snippet.
Next, click Connect to Another Computer and choose Local Computer and then click OK to close Connect to Another Computer window. In Microsoft Baseline Configuration Analyzer 2.0 window, click Start Scan to begin the scan.
In 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. Using Microsoft SQL Server 2008 R2 Best Practice Analyzer you can scan 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 2008 R2 BPA 1.0 has completed the scanning you will be able to see the Baseline Configuration Analyzer Report which will be categorized into Errors, Warnings and Complaints 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 exists on the SQL Server Instance as per rules configured in 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.
Next Steps
- Be proactive and check your SQL Servers to ensure they are meeting the industry best practices, so you can confidently answer your management when they come to ask you about the 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
