Login

Use Best Practices Analyzer for MS SQL Server

If you’re configuring MS SQL Server, or have been running it for a while, you might run into some issues. Fortunately, there’s a tool you can use to make sure you’re using best practices before real problems crop up. It’s called the Microsoft SQL Server 2012 Best Practices Analyzer (BPA).

Kevin Kline at SQL Server Pro supplies the details in his Tool Time blog. This tool checks the settings of your SQL Server 2012 instances, and compares them with best practices. When it finds any settings that don’t fall in line with those practices, the tool tells you how to change you settings so you won’t face certain issues later.

If you want to use the Best Practices Analyzer, you can download it from Microsoft’s Download Center. Supported operating systems include Windows 7, Windows Server 2008, and Windows Server 2008 R2. In addition, to use the tool, you’ll need PowerShell V2.0 and Microsoft Baseline Configuration Analyzer V2.0.

Once active, the BPA can assess your entire SQL Server stack in several ways. As Kline explains, “The tool performs its analysis not only on the relational database engine but also on SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).” Replication and setup settings also fall under its purview. In total, the tool performs more than 100 checks to different parts of your MS SQL Server stack.

You may have wondered why the BPA requires you to have PowerShell installed. Well, you can use the capabilities of PowerShell combined with this tool to scan local and remote computers. You will need to change certain settings on your PowerShell, however, and explicit details on this topic are beyond the scope of this article. Microsoft helpfully provides an article on installation and configuration for Windows Remote Management .

Once you install the BPA, you can simply operate its graphical user interface to choose a target server and scan in its Home settings. Then, choose the parts of its SQL Server stack that you want examined. It’s literally a simple matter of going down a list and clicking the check boxes next to the relevant parts of the stack. You may want to do this if you want to exclude replication settings, for instance, and have the BPA focus on the other parts of your SQL Server stack.

When the BPA completes its scan of the areas you’ve indicated, it will deliver an analysis. You can look at either an overview or a detailed report. It’s just a matter of clicking the appropriate radio button. You can also take a closer look at the data the BPA collected. If you scanned a number of machines, you can even use the Filter option to examine detailed information about a particular computer.

Microsoft naturally assumes you’ll want to save the results, and they made it easy to do so by providing an Export Report option. You don’t need to export all of the results, however – and if there’s a lot, you may not want to. Once again, the Filter option comes to the rescue, allowing you to weed out certain parts of the results so you can focus on the areas of most concern.