A big part of my DBA career has centered around identifying and sharing SQL Server DBA best practices. There are literally hundreds of different best practices, and as you might expect, not every best practice applies to every SQL Server environment. That means, that as a DBA, you must evaluate the many commonly accepted best practices and determine which ones best meet your particular needs.

For experienced DBAs, identifying best practices, and determining which ones apply to their environments, is generally an easy task. But for novice or accidental DBAs, this is often a difficult task. What we really need is a tool to help novice and accidental DBAs to quickly evaluate a SQL Server instance, and then based on the evaluation, recommend any best practices that should be implemented for their particular environment. Unfortunately, no such tool exists today, although there are a number of free tools that make an attempt.

Microsoft offers two different tools that are supposed to analyze a SQL Server instance and recommend best practices.  One of these tools is called Microsoft Codename Atlanta. This is a cloud-based service (currently in beta) designed to monitor SQL Server instances and provide recommendations based on a set of established best practices. I will be evaluating this service in a blog post in the next week or so. The second tool offered by Microsoft is called the Microsoft SQL Server 2008 R2 Best Practices Analyzer. This product is available today and can be downloaded free from Microsoft.

The Microsoft SQL Server 2008 R2 Best Practices Analyzer will work with both SQL Server 2008 and SQL Server 2008 R2. There are different versions of this tool for older versions of SQL Server.

If you have never heard of this free tool before, you might be thinking, wow, this is the answer to all of my problems. Here’s a tool that can analyze my SQL Server 2008 instance and tell me what best practices I need to implement. While this sounds great in theory, this free tool may be a disappointment to many users. The problem is that is doesn’t offer advice on many of the most important best practices, only identifying a small number of potential problems, many of which are rather obscure. In addition, many novice DBAs will not be able to fully understand many of the recommendations, which means they may not be able to fully benefit from the tool as much as more experienced DBAs can. On the other hand, I don’t want to say that you shouldn’t run this tool on your SQL Server instances, as it can be useful. Just keep your expectations low.

How to Install the Microsoft SQL Server 2008 R2 Best Practices Analyzer

The first step is to visit the Microsoft SQL Server 2008 R2 Best Practices Analyzer web page and read about the tool, learning about the installation pre-requisites. The first choice you must make is to determine if you want to download either the 32-bit or the 64-bit version of the software. Second, if they are not already installed on the SQL Server instances you want to analyze, you also have have to download and install the following two free applications before installing the Microsoft SQL Server 2008 R2 Best Practices Analyzer.

• PowerShell V2.0: If PowerShell V2.0 is already installed on your SQL Server box, then you don’t need to download and install it again. Otherwise, it must be downloaded and installed as your first step.
• Microsoft Baseline Configuration Analyzer: This tool is essentially a wrapper application for the Microsoft SQL Server 2008 R2 Best Practices Analyzer. In other words, you must first download and install this free application, then download and install the Microsoft SQL Server 2008 R2 Best Practices Analyzer. Once both applications are installed, then the Microsoft SQL Server 2008 R2 Best Practices Analyzer will run within the context of the Microsoft Baseline Configuration Analyzer. How this works will be demonstrated shortly.

The SQL Server 2008 R2 Best Practices Analyzer has some known issues. If you run into problems installing it, start out by visiting this web page.

Once you have all three applications installed, you are ready to begin using it.

Using the Microsoft SQL Server 2008 R2 Best Practices Analyzer

Once it is installed, running the tool is simple. First, start the Microsoft Baseline Configuration Analyzer. After it starts up, you will see the following screen.

Notice that the first step is to “Select a Product”. This is a drop-down box that allows you to select the SQL Server 2008 R2 BPA to run. As I mentioned earlier, the Microsoft Baseline Configuration Analyzer is a wrapper program, and is where the Microsoft SQL Server 2008 R2 Best Practices Analyzer runs from.

If you want to scan a remote SQL Server instance, then click the “Connect to Another Computer” link, select the instance to scan, then click on the “Start Scan” link. Otherwise, the scan will be performed on the local SQL Server instance.

After you click on the “Start Scan” link, the following screen appears.

Typically, the first two options above are left blank. If your box has multiple SQL Server instances, then you will need to enter the instance name to scan. If you have a single, default instance, then leave these options blank.

Next, select those SQL Server services that are running on the server that are to be scanned. If you accidently select a service that is not running, then you will get an error message that the service is not running after the scan completes.

For the SQL Server instance I want to analyze, I have selected the SQL Server Engine, the SQL Server Setup, and Integration Services. As you might have guessed, the Microsoft SQL Server 2008 R2 Best Practices Analyzer only performs those analyses that you select, as each option has a different set of best practices it analyzes. For my example, I only want three different analyses to be performed.

Once you have selected which services to analyze, click the “Start Scan” link and the scan will begin, as shown below.

The scan won’t take very long, and when it is done, you will see a report similar to the following screen.

The report has two tabs.  The first tab is “Noncompliant”, which is used to list all of the best practices that the tool thinks you have broken. It is divided into both Errors and Warnings. The second tab is “All”, which displays not only noncompliant best practices, it lists all of the best practices that were analyzed. Let’s start with the “Noncompliant” tab by expanding both the Error and Warning categories, which looks like this.

As you can see, the tool is telling me that the SQL Server instance I analyzed has 8 Errors and 12 Warnings. While some of the messages are fairly straight-forward, many of them are not. To find out what a particular message means, click on the Error or Warning. For example, if I click on the first Error, the following yellow screen appears, which offers a brief explanation of the Error.

As you can see, the additional information is more useful than the original Error message, but it doesn’t include a lot of detail. To see an even further detailed explanation, click on the “More Information” link, which opens up a web page with a much better explanation, as shown below.

Now, this is much more useful information. Depending on the Error or Warning, the web page you are directed to can be very long and detailed. In most cases, I found the explanations understandable, although many novice DBAs may find themselves a little hard to understand. The web pages follow a format of Symptoms, Cause, Resolution, and More Information.

At this point, your task would be to review each of the Errors and Warnings provided by the Microsoft SQL Server 2008 R2 Best Practices Analyzer, read each message and related webpage, and based on what you learned, perhaps take action to correct the Error or Warning. I say “perhaps” because not all of the Errors or Warnings may really be applicable to your situation, or you might not care about the Error or Warnings, choosing to ignore them based on your knowledge of your SQL Server instance.

This focus of this blog post is on how to install and run the tool, not to explain each best practice the tool analyzes.

For example, let’s say that after some research, I determine that the”Autogrow Failed or took a long time” Error, the one in my example, is not important to me. I can turn it off by clicking on the “Exclude this Result” link in the message window, which you see below.

When you exclude any of the Errors or Warnings, a new tab appears on the screen that includes your Exclusions, as you see below.

By excluding Errors or Warnings that you don’t care about, you  won’t see them in the Noncompliant tab, but they will still appear under the Exclusions tab. If you want to re-include any of the Errors or Warnings you have excluded, you can re-include them by clicking on them and clicking on the “Include this Result” link.

Now that we have had a brief look at the Noncompliant tab, let’s take a quick look at the All tab,which is shown below.

This tab is interesting because it lists all of the various best practices that the tool analyzes. Just like with the Errors and Warnings, you can click on them to open up the yellow description box, and you can also click on the “More Information” link to view the related web page information. Just reading through each of these is an education in and of itself.

At this point, you now know everything there is to know about using the Microsoft SQL Server 2008 R2 Best Practices Analyzer. On the other hand, the hard part is determining if a particular Error or Warning should be corrected. While the web-based help pages are very useful in explaining them, much of the content is technical and may be beyond the skill level of many novice and accidental DBAs.

Because of this, my suggestion is that unless you know exactly what a particular Warning or Error message means, and the implications of making changes to correct them, that you don’t make any changes based strictly on what the tool says. Instead, if you see a Warning or Error that looks like it needs to be corrected, be sure to research it first using other resources, talk to other DBAs, and get their input before you begin making any changes. And of course, be sure that you test any potential change on a test box before making a change on the production box, as learning by trail and error is not a good idea on a production SQL Server.

Although this tool is incomplete in the best practices in analyzes, and often the Errors or Warnings may be hard to decipher by novice DBAs, it still can be a useful tool, and one that I think you should check into if you have not done so already.