Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using the Microsoft SQL Server 2005 Best Practices Analyzer


By:   |   Last Updated: 2010-03-29   |   Comments   |   Related Tips: 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 2005 Best Practices Analyzer to analyze a SQL Server instance to determine whether it is configured according to the best practices or not.

Solution

The Microsoft SQL Server 2005 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 2005 recommendations and best practices to identify potential issues within the database environment. You can download the latest version of Microsoft SQL Server 2005 Best Practices Analyzer (BPA) for free from the following link.

1. Once you have installed Microsoft SQL Server 2005 Best Practices Analyzer (BPA). You can use the tool by clicking Start | Programs | SQL Server 2005 BPA | SQL Server 2005 Best Practices Analyzer. In Microsoft SQL Server 2005 Best Practices Analyzer, select Go to Welcome Screen to start using the tool as shown in the snippet below.

The Microsoft SQL Server 2005 Best Practices Analyzer (BPA)

2. In Welcome to the Microsoft SQL Server Best Practices Analyzer screen, you need to choose the very first option Select options for a new scan as shown in the below snippet.

Select options for a new scan

3. In Start a New Best Practices Scan screen, you will find different options available to scan such as the SQL Server Instance, Computer or a File. There are two different types of scanning available namely Limited or Detailed. I would suggest you to use Detailed Scan when you are running this tool for the first time on an Instance or on a Server to get the complete configuration information.

 you will find different options available to scan such as the SQL Server Instance, Computer or a File

4. Once you have selected SQL Server Instance Name option as shown in the snippet above, you will be asked to provide information like SQL Server Component, SQL Server Instance Name, Authentication Mode, Login and Password. Once you have provided the entire set of information necessary, click Next to continue.

you will be asked to provide information like SQL Server Component, SQL Server Instance Name, Authentication Mode, Login and Password

5. In the Configure Database Engine screen, you can choose the databases against which you want to run the BPA tool to analyze the best practices as shown in the snippet below. Click Next to continue.

choose the databases against which you want to run the BPA tool to analyze the best practices

6. In Start a New Best Practices Scan screen, you need to enter a name for this scan and click on Scan selected components to start scanning.

click on Scan selected components to start scanning

7. Once the scan has completed successfully, you will get the high level summary of the scan as shown in the snippet below. You can view the detailed report by clicking View a report of this Best Practices scan.

View a report of this Best Practices scan

8. In the View Best Practices Report screen, you can see a very detailed report with respect to the scan which you have performed. There are three different ways by which you can view the report namely List Reports, Tree Reports and Other Reports.

List Reports, Tree Reports and Other Reports

9. If you want to view any report at a later time, you can do so by selecting Select a Best Practices scan to view from the right panel and then by selecting the report of your choice.

Select a Best Practices scan to view f
Next Steps


Last Updated: 2010-03-29


get scripts

next tip button



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




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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools