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