Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Microsoft SQL Server Best Practices Analyzer

Last updated Mar 28, 2003.

With everything a DBA has to do, there's not always time to evaluate every server to ensure they are set up properly. You can take the time to learn every setting Microsoft recommends, or you can purchase a tool to do the job, or ask an expert to take a look at your systems.

You can also download the very best practices from Microsoft, and using a free tool you can create a report that will show you exactly what you need to work on, as well as a complete explanation of the settings your server should have. It's the best of all worlds: it's free, it tells you what you need to know, and it is from the people who wrote the software to begin with. This tool is called the Best Practices Analyzer, and you'll find it a great resource to evaluate your servers quickly and easily.

Let's begin with the installation of the tool. As always, do this on a test system first, and try it on a test instance. Once you're comfortable with it, you can install it in production. The normal process is to install the tool on your management system, and then adding your monitored systems to the tracking database. You don't have to install anything on your production servers.

The download for the Best Practices Analyzer will automatically start the installation when you click on it. The first two panels introduce the installation, and the third sets the location for the binary files. I left this selection at the default. You'll need a SQL Server database to hold the metadata the tool uses, and the next panel sets that database as well as the connection information. This database has to be new, one that is used only by the tool.

Once you set the database and connection, the installation takes only a few minutes to complete. You'll have a new menu item that you can use to start the tool, called Microsoft SQL Best Practices Analyzer. Start the tool and you're presented with the introductory screen:

You're presented with four steps: logging in to the tool, picking the instances to analyze, picking a "Best Practices" group to use as a measurement, and reviewing the reports.

Logging In

This part is pretty straight-forward. The important thing here is that you're logging in to the server that houses the metadata database you created during the installation, not the system you want to monitor.

Setting up the Instances

Once you've logged in to the tool, you can select the instances of SQL Server that you want to monitor. I normally add one a few (actually only one) at a time. The reason I do that is because I deal with the reports one at a time, and often what happens on one server needs to be done on another.

Notice here that you also have a chance to add trace files to the evaluation. These trace files are created by SQL Server Profiler, which I've covered elsewhere on this site. SQL Server Profiler watches activity on the server and can gather just about everything that is coming in to the server, and its reaction to the activity. You can save what the Profiler sees in a Trace File. While the Best Practices Analyzer won't evaluate every one of your SQL Statements for you, it helps to have a trace available. It makes the evaluation more robust to see what is hitting the server, and the trace file contains the reaction the server has to SQL Server activity.

There are two icons to the right of the instance name, one in the shape of an X and the other that looks like a notepad. The X icon removes the instance from the list, and the notepad icon edits the connection information.

Picking a Best Practice Group

Once you've added your instances, just click the Best Practice Groups text item in the left-hand pane of the tool. Once in that panel, you can select the items to evaluate, called Best Practice Groups, that Microsoft provides, everything from Configuration to Backups. For the first time on an instance, I normally run the "All Categories" report. This takes a little bit longer, but it's worth the time.

At the bottom of this panel is a button to create your own Best Practice Group. In this set of panels you can check off the items that Microsoft recommends that you check, and the tool will evaluate your servers based on those criteria. You can use that part of the tool to create a series of checks you want to evaluate in your environment.

Click the Create Best Practice Group to bring up the panel to check off the items you're interested in.

Once you've made your selections, select the instances you want this group to apply to and click "Save Best Practice Group" to bring you back to the panel where you can run the report.

Back at the Best Practices Group Screen, you're shown the group you just created. Click the small down-shaped arrow to the left of your group, and it will be added to the Best Practice Groups to be Executed area.

Now you're ready to go. Just click the Scan SQL Server Instances link and the process evaluates your instances. You'll get one "Next" button, and the process will begin.

Viewing the Reports

Once the process stops, you're presented with a report of the results. Icons on the left of the screen quickly show you the status of a particular item in the evaluation, and clicking on the Rule Information link gives you more information about the item you're looking at.

Each of these reports can be copied so you can paste them into a rich-text environment like Microsoft Word. You should run these reports periodically, especially after service packs or changes.

The best thing about these reports is that they still leave you in control. They simply gather the information you need to focus on and present it in an easy to understand format.

Informit Articles and Sample Chapters

I've blogged about this tool here on Informit.

Online Resources

You can download the tool here.