Microsoft Best Practice Analyzer –
Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter or exclude results from BPA reports that they do not have to see. Administrators can also perform BPA tasks by using either the Server Manager GUI, or Windows PowerShell cmdlets.
You will find more details in –
Installation of BPA – SQL 2012
• PowerShell V2.0
• Microsoft Baseline Configuration Analyzer V2.0
• User should have admin access on server and SQL Server
o Note – You need to add your individual id or else you will get login error after running BCA.
Steps by Step Installation –
Step 1 – Download and Install the Microsoft Baseline Configuration Analyzer v2 (BCA) (follow default download instructions) – This will install to C:\Program Files\Microsoft Baseline Configuration Analyzer
Download link – https://www.microsoft.com/en-us/download/details.aspx?id=16475
Step 2- Install the Microsoft SQL Server 2012 Best Practices Analyzer (SQL BPA) (follow default download instructions) – This will install to C:\Windows\System32\BestPractices\v1.0\Models\SQL2012BPA\
Note – You will not able to see any icon for (SQL BPA) as it’s only install executables.
Step 1 – Open Microsoft Baseline Configuration
Note – If you wanted to connect remote computer, click on Connect to Another Computer.
Step 2 – Select “SQL Server 2012 BPA” from drop down.
Step 3 – Go to Enter Parameters and select parameters you wants to select.
Note – If it’s remote server provide server name in Alternate_Server_to_scan (Only server name not instance name), provide instance name in SQL_Server_Instanace_name fields.
Step 4 – Click on “Start Scan”
Step 5 – Analyze your result.
I was configuring BPA 2012 in windows 2012, after installing both BCA and SQL BPA, I was not able to see “SQL Server 2012 BPA” in drop down and to resolve the issue I have performed below steps –
1. Navigate to the directory C:\Windows\System32\BestPractices\v1.0\Models and right-click the SQL2012BPA folder and make a copy.
2. Navigate to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\ and paste the SQL2012BPA folder in this new location.
(Note – ProgramData is hidden folder which you need to see after checking “Hidden Items” in view of your file explorer)
3. Start the BCA with Run As Administrator and you will now see the SQL Server 2012 BPA listed in the “Select a product” dropdown.