Microsoft SQL Server 2012 Best Practices Analyzer (BPA)

The Microsoft SQL Server 2012 Best Practices Analyzer (BPA) is a powerful tool that reviews the configuration and settings of your SQL Server 2012 instances. In addition, the tool highlights settings that differ from best practice recommendations. When the tool finds settings that don't meet best practice recommendations, the BPA also provides advice on how to apply settings to help minimize potential problems.

The BPA now includes several assessment features for the entire SQL Server stack. 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). The assessment also covers replication and setup settings. The tool provides several checks, including:

The tool can use PowerShell remoting to scan local and remote computers. This task requires you to modify your PowerShell settings to increase the maximum number of concurrent shells per user and requires explicit acceptance during installation. For more information on PowerShell remoting, see the Microsoft article "Installation and Configuration for Windows Remote Management."

Features

Once you've installed the BPA, invoke the tool and choose a target server to scan in the Home settings. Next, select parts of the SQL Server stack that you want the BPA to examine. For example, Figure 1 shows how to exclude replication settings by selecting the appropriate choices on the Enter Parameters settings.

Figure 1: Excluding Replication Settings 

After you make your selections, the BPA will scan the parameters that you specified. When the BPA finishes its scan, you'll be able to review the analysis results via an overview or a detailed report, as Figure 2 shows.

Figure 2: BPA Analysis Results 

You can select the Results radio button to see the BPA analysis findings. Alternatively, clicking the Collected Data radio button shows the configuration data that the BPA scan collected. For either report type, click the Filter option to locate the computer name for the scanned server to view detailed information. Then you can look for the SubModel value that correlates to the various parameters of the scan, such as SubModel = Engine for a scan of the database engine best practices.

You can also save the results by using the Export Report option. If the BPA returned a lot of results, you might want to filter out various aspects of the report using the Filter option. You can also read the report through the Microsoft Baseline Configuration Analyzer (MBCA) GUI if you're familiar with that tool.

System Requirements

The BPA runs on the following OSs: Windows 8, Windows Server 2012, Windows 7, Windows Server 2008 R2, and Windows Server 2008. The tool also runs on any edition of SQL Server 2012, but it's not backward compatible. The BPA requires PowerShell 2.0 and MBCA 2.0. In addition, the user must be an administrator on the target machines because MBCA requires administrator privileges. Also, the user must have systems administrator privileges on the target instance of SQL Server.

You can download the BPA from the Microsoft Download Center, where you can also download the MBCA tool. As always, SQL Server Pro wants to hear your feedback at the Tool Time discussion forum.