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 2008 R2 Best Practice Analyzer


By:   |   Updated: 2010-12-07   |   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.

using Microsoft SQL Server 2008 R2 Best Practices Analyzer

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 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.

you can scan SQL Server Database Engine, Analysis Services, Replication, Integration Services, Reporting Servers and SQL Server Setup

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

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 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

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 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.

view the detailed explanation of the issue encountered

Next Steps


Last Updated: 2010-12-07


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.



    



Tuesday, January 25, 2011 - 10:04:26 AM - Jeremy Kadlec Back To Top

Mark,

I have not seen the errors you mentioned.  I guess the first place to start is on the download web page.

Do you meet all of the requirements on this page (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591&displaylang=en) for the "System Requirements" section?

Are you able to run the PowerShell commands in the "Additional Information" section?

Since you have posted your issue, do you have any other updates?

Thank you,
Jeremy Kadlec


Friday, January 21, 2011 - 3:16:10 PM - Mark Back To Top

Not having much luck with this utility.  I was hoping that Jeremy's hints would help but so far no go.

On my 2008 R2 database server I installed both products but when I try and start it I get an error about "There were errors in loading the format data file: Microsoft.PowerShell, xxxxCmdlets.Formats.ps1xlm:  File skipped because of the following validation exception: AuthorizationManager check failed..". ????

I then installed BPA on another windows 2008 R2 server (application server) where I can successfully launch it but since I don't have a local database I need to connect to a remote server and that connection always fails as well with a long WinRM error message.  If I run winrm quickconfig on both servers it reports WinRM is already set up to recieve requests on this machine and for remote management".   However If I try winrs commands they do not seem to work ie "winrs -r://http://localhost "ver".

I am logged on as domain administrator on both servers.  I've created a matching SQL Server account with sysadmin fixed role but not working here.  Any troubleshooting suggestions?


Thursday, January 13, 2011 - 4:36:00 PM - Jeremy Kadlec Back To Top

Ashish,

Thank you for the tip.

One item that I ran into when using the Microsoft SQL Server 2008 R2 Best Practice Analyzer with SQL Server 2008 R2 is that you had to have the Microsoft Baseline Configuration Analyzer 2.0 installed first.

Here is the is the download URL - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1B6E9026-F505-403E-84C3-A5DEA704EC67&displaylang=en

Another issue I ran into on 1 machine was the need to have my Windows account in the Windows local admin group and the SQL Server System Admin group.  I believe a group I was in met both of these requirements, but I received some obscure errors until I added my individual account (Windows) and login (SQL Server).

I hope this helps.

Thank you,
Jeremy Kadlec


Tuesday, December 07, 2010 - 2:47:01 PM - Ashish Kumar Mehta Back To Top

Hello Jack,

You can use Microsoft SQL Server 2008 R2 Best Practice Analyzer with SQL Server 2008 and SQL Server 2008 R2.

Thanks
Ashish Kumar Mehta


Tuesday, December 07, 2010 - 9:03:55 AM - Jack Hellmuth Back To Top

Does this work with earlier versions of SQL Server?


Tuesday, December 07, 2010 - 1:59:26 AM - Jerry Back To Top

Great Article, indeed BPA is a great tool available for DBA's.


Learn more about SQL Server tools