Microsoft SQL Server 2012 Best Practices Analyzer (BPA)

RSS

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:

  • SSAS: 33 checks
  • Relational database engine: 59 checks
  • SSIS: 3 checks
  • SSRS: 4 checks
  • Replication: 8 checks
  • Setup: 2 checks

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

Microsoft SQL Server 2012 Best Practices Analyzer
Benefits: The Microsoft SQL Server 2012 Best Practices Analyzer (BPA) is a powerful tool that reviews the configuration and settings for your SQL Server 2012 instances.
System Requirements: Windows 8, Windows Server 2012, Windows 7, Windows Server 2008 R2, or Windows Server 2008; SQL Server 2012; PowerShell 2.0; Microsoft Baseline Configuration Analyzer 2.0
CONTACT: Microsoft SQL Server 2012 Best Practices Analyzer

 

Discuss this Blog Entry 4

on Feb 25, 2013
Hi Kevin, I was trying to use this tool on my WS2012/SQL2012 combo but this does not seem to work. While in BCA, there are no options in the product drop down box so also no BPA. Looking at the MS download site Windows 8 and WS are NOT supported! Might this be an error in your article or do you have a "secret" way to get it working under WS2012? Cheers, Erik
on Jul 4, 2013

I wasted a whole day yesterday trying to get this tool to work with no joy. Tried it on my local Windows 7 Pro PC and on a Windows 2012 server. Neither worked. Same issue as described above even though I followed installation instructions to the letter - Very frustrating!

on Dec 3, 2013

Hi guys, I just spent a couple of hours on this too. I found that in WS2012 MS have changed tack. You need to install the MBSA (2.3 at the current time). This includes the rules for SQL Server although it is not clear it is for 2012. I assume it is.

on Dec 4, 2013

Didn't quite work as expected. I had to install MBCA on another server in the environment (a Windows 2008 R2 server) as well as the SQL 2012 server. Running it on the 2008 R2 server connecting to another computer still had a empty dropdown box. However, and under "enter parameters" I specified the SQL 2012 server and got some results. I am not entirely convinced that it did the full check as the collected 11 results (I had no SSRS or replication).

Please or Register to post comments.

What's Tool Time Blog?

SQL Server tools and tips from Kevin Kline.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×