Where is my SQL BPA(Best Practice Analyzer) for SQL 2014/2016/2017?

  • Article

We recently built a SQL 2016 Server for a client and wanted to check if the best practices have been followed or not.

Most of us have used the tool BPA (Best Practice Analyzer) to evaluate an instance.

The Microsoft SQL Server 2012 BPA is a diagnostic tool that performs the following functions:

  • Gathers information about a Server and a Microsoft SQL Server 2012 instance installed on that Server
  • Determines if the configurations are set according to the recommended best practices
  • Reports on all configurations, indicating settings that differ from recommendations
  • Indicates potential problems in the installed instance of SQL Server
  • Recommends solutions to potential problems

 

The latest version I worked with was the BPA for SQL 2012 found here

Now we didn’t have this tool available for download for SQL 2014 or SQL 2016. It appears the BPA has been replaced by Policy Evaluation using built in Best Practice Templates. Here is how you can perform one.

These are the policies that will be evaluated as part of the template :

/en-us/sql/relational-databases/policy-based-management/monitor-and-enforce-best-practices-by-using-policy-based-management?view=sql-server-2017

 

To perform an on-demand evaluation by using Object Explorer

  1. Start Management Studio, and then connect to the Database Engine.
  2. In Object Explorer, expand Management, expand Policy Management, right-click Policies, and then click Evaluate.

2. In the Evaluate Policies dialog box, next to the Source box, click the ellipsis ( … ) button

3. In the Select Source dialog box, you can select either Files or Server as the source of the policy files to evaluate. Click Files, and then select the individual policy files that you want to evaluate. To do this, follow these steps:

  1. Click Files.
  2. Next to Files, click the ellipsis ( … ) button.
  3. In the Select Policydialog box, browse to the following folder, which contains the best practices

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033

  1. Select one or more .xml policy files to evaluate, and then click Open.

The list of selected files appears in the Files box.

  1. In the Select Sourcedialog box, click OK.
  2. If the Loading Policiesdialog box appears, click Close.
  3. The policies that you selected are listed on the Policy Selection page. Be aware that a warning icon next to a policy indicates that the policy contains scripts.
    1. Click Evaluate to evaluate the policies.

 

In the Results table, the results for each policy are listed. A red "x" icon indicates that policy compliance failed.

You can click on the Details to see what policy has failed

Make sure that you fully understand the policy setting before automatically updating a target instance.