SQL Server 2008 R2 Best Practices Analyzer

Maybe I'm a little late to the party, but I thought I would share with you the most recent release of Microsoft's Best Practices Analyzer for SQL Server 2008 R2 (you can also read the original blog announcement from CSS). Let me be clear: this tool is not just for people who are already on SQL Server 2008 R2.  It will also happily analyze your SQL Server 2008 instances, so don't be fooled by the label.

That said, getting the thing installed was an exercise in patience.  Like most people, I clicked the download link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

And I was off to the races.  Or so I thought.  I still haven't developed a healthy habit of READING before CLICKING.  The first time I ran the installer, I got this complaint about not having the Baseline Configuration Analyzer installed (I'm typing out the error message so that users experiencing the same thing can find this post, and I'm showing a screen shot to show how asinine it is to put a URL in a dialog but not have the ability to click or copy it):

Microsoft Baseline Configuration Analyzer 2.0 is not installed. Please install it from http://www.microsoft.com/downloads/details.aspx?FamilyID=1b6e9026-f505-403e-84c3-a5dea704ec67

To help out a bit, here is the link to download the Configuration Analyzer (which, I will admit, was much easier to obtain from a Google search than by transcribing the link from the dialog) :

http://www.microsoft.com/downloads/details.aspx?FamilyID=1B6E9026-F505-403E-84C3-A5DEA704EC67

If you're not on Windows 7 or Windows Server 2008 R2, you have the potential to receive error messages about missing PowerShell 2.0, as follows:

Microsoft Baseline Configuration Analyzer 2.0 requires PowerShell 2.0 to be pre-installed on the system.

No worries, you can download PowerShell 2.0 as part of Windows Update:

 

Or if you don't want to go through the hassle of Windows Update, you can also get it by installing the Windows Management Framework Core Package individually:

http://support.microsoft.com/?kbid=968930

(Now why, since PowerShell 2.0 is only a 6 MB download, it couldn't be published as part of the Configuration Analyzer setup, or at least to go fetch it dynamically during installation, I have no idea.)

I got a little bit further into the installation once I had the Configuration Analyzer installed.  But then, I discovered in a very round-about way, that there is an issue with the installer when you try to put the BPA on a machine that is not connected to a domain (e.g. just about every virtual machine I own).  When it tried to run the PowerShell commands during setup, I saw a PS window briefly, along with some scary-looking red text, and then it disappeared and setup returned this error message (again typed out for SEO goodness):

There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor.

Which is great to know that it wasn't BPA's fault, but something else's.  But would it have been that hard to consume the error message that obviously came back from PowerShell?  Or better yet, return an error message like this:

Google "BPA 2.0 error powershell"

Because as it turns out, there is a very easy fix to this, and it was the first hit for the search above.  If you want to install BPA onto a machine that is not domain-savvy, instead of double-clicking the MSI file, run this command line (replacing <path> with the folder where the MSI file is located, and changing <platform> either to 32 or 64):

msiexec /i <path>\SQL2008R2BPA_Setup<platform>.msi SKIPCA=1

(Thanks to Adam Saxton for pointing this out on the CSS SQL blog.)

Once I got the BPA running, it did quickly point out several issues I was aware of (including bad partition alignment on a non-essential drive, some databases that were intentionally offline, and the fact that the error logs have a lot of history), as well as a couple of new issues:

 

The one I've expanded is one that Bob Ward talked about in a blog post yesterday as one of the key reasons to use the new BPA:

http://blogs.msdn.com/b/psssql/archive/2010/08/24/why-use-sql-server-2008-r2-bpa-case-1-missing-updates.aspx

Obviously it finds a bunch of things that you may already have alerting for, and probably some things that you don't.  In any case, this can be a very quick way to assess a SQL Server instance without having to rummage around for all of this information manually, and even with the stumbling you may have getting it configured, it should pay off rather quickly.  There are plenty of things it doesn't look for, and you can get that information from other places as well – for example, check out the BLITZ! takeover script from Brent Ozar (blog | twitter):

http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

 Anyway, I haven't really offered any input that you can't see elsewhere, but I hope it is useful nonetheless.

EDIT: If you are running on Windows Server 2003

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

5 Responses

  1. Darryll Petrancuri says:

    I'm having a problem with the BPA. Once I install it on one of the nodes of a 2008 R2 Cluster and run it, the product dropdown is empty, regardless of whether I run as administrator or not.
    Can anyone offer some insight?
    Thank you!

  2. Ashish says:

    Thank Aaron , you did great job , lots of help to get rid of these error

  3. Jen Underwood says:

    THANK YOU for posting the fixes for this annoying install of a good program!

  4. Aaron Bertrand says:

    Yep, I give people crap about that all the time.  But in this case, Ctrl+C is ignored… Try it!

  5. Ben Thul says:

    Regarding your too-much-in-a-dialog-box issue, here's a nifty trick: with the dialog box focused, hit ctrl-c like you're copying text.  Now paste it in some sort of text editor (like notepad).  You should get a textual representation of the dialog box.  Enjoy!