Microsoft SQL Server 2012 Best Practices Analyzer

The Microsoft SQL Server 2012 Best Practices Analyzer(BPA) is a diagnostic tool that performs the following functions:
  • Gathers information about a Server and an instance of Microsoft SQL Server 2012 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
For every configuration, the SQL Server 2012 BPA provides the following results:
  • Compliance / non-compliance – Compliance results are returned when an instance of SQL Server satisfies the conditions of a Best Practices rule. Non-compliance results are returned when an instance of SQL Server does not satisfy the conditions of a Best Practices rule.
  • Impact of non-compliance
  • Recommendation
  • Links to more detailed information and related topics
Pre-requisites for Running SQL Server 2012 BPA

The computers that are running SQL Server 2012 BPA must have the following tools installed:

  • Power Shell 2.0
  • Microsoft Baseline Configuration Analyzer v2.0.
  • SQL Server SQL Server 2012 Management Studio tools
Setting Up the Scan through MBCA 2.0

Follow these steps to scan a SQL Server instance by using SQL Server 2012 BPA in the MBCA application.

BCPA_2.0

  1. Open the Baseline Configuration Analyzer by clicking Start and then All Programs.
  2. Right-click on the Microsoft Baseline Configuration Analyzer, and then click Run as administrator.
  3. On the Home page, select SQL Server 2012 BPA from the Select a product drop-down list.
  4. To start a scan, click Start Scan on the Home page.
  5. On the Enter Parameters page, enter the following parameters:

BCPA_2.0_Parameters

Alternate_Server_to_Scan – This field specifies the server to be scanned.

To run the scan on a remote server, expected values of this field are:

  • ComputerName
  • IP address: n.n.n.n
  • FQDN (Fully Qualified Domain Name)

To run the scan on a local server, expected values of this field are:

  • Blank
  • localhost
  • . (dot)

Note: Run the Powershell commands before performing the scan on the alternate server.

SQL_Server_Instance_Name – This field specifies the SQL Server instance to be scanned. Expected values of this field are:

  • Blank – Points to default instance of SQL Server
  • “MSSQLSERVER” – Value of default instance of SQL Server
  • Named Instance – Any named instance value of SQL Server

Each of the following six check boxes correspond to the SQL Server categories listed previously. Select at least one category in order to run a successful scan.

  • Analyze_SQL_Analysis_Services
  • Analyze_SQL_Server_Engine
  • Analyze_SQL_Integration_Services
  • Analyze_SQL_Server_Replication
  • Analyze_SQL_Reporting_Services
  • Analyze_SQL_Server_Setup

Note: Only one SQL Server instance can be scanned at a time through the MBCA GUI.

  1. After specifying the parameters, click Start Scan.
  2. Wait for the scan to finish. When a scan is finished, the MBCA GUI displays the scan results on the View Reports page.

There are two sets of configuration settings that must be set prior to using MBCA’s “Connect to another computer” feature.

First, you must set CredSSP on both the client and the server.

  • Using the Group Policy Editor (gpedit.msc) make sure to enable “Allow Delegating Fresh Credentials” and check “Concatenate OS defaults with input above”.
  • Add the server or domain to the list of servers in the format “WSMAN/*.domainname.com

Next, enable and configure PowerShell Remoting on both the Client and Server by running the following commands in a PowerShell command window opened with elevated permissions.

Note: You can configure a single machine as both a client and a server simultaneously so that you can scan from either computer.

Enable PowerShell Remoting

  • Enable-psremoting –f
  • Settings for a client
    • Enable-WSManCredSSP –role Client –DelegateComputer [NetBiosNameOfServer]Or
    • Enable-WSManCredSSP –role Client –DelegateComputer [FQDN OF SERVER]
    • Settings for the server
      • Enable-WSManCredSSP –role Server
      • Set-item WSMan:\localhost\Shell\MaxMemoryPerShellMB –Value 20000
      • set-item WSMan:\localhost\Shell\MaxShellsPerUser –value 20

Please refer MBCA help file for more details on using this feature.

Note: To scan instances of SQL Server that are enrolled in a SQL Server Utility Control Point, run BPA directly against the enrolled instances of SQL Server.

Validations Prior to Scanning

SQL Server 2012 BPA performs several validations on the machine that will be scanned. If the prerequisites are not met, appropriate error messages display.

The SQL Server 2012 BPA validations prior to scanning are:

  • SQL Server 2012 Management Studio must be installed.
  • The SQL Server services corresponding to the SQL Server instance, which is being scanned, should be running.Note: This check is not applicable to Reporting Services (Report Server Service) because the Reporting Services scan is independent of the Report Server Service. It accesses configuration files for the scan.
  • The user’s login must exist on the instance being scanned.Note: This check is not applicable to Reporting Services and Analysis Services because a SQL Server login is not required for scanning Reporting Services and Analysis Services.
  • The user must be a member of the Systems Administrator role on the instance of SQL Server.

Some validation checks specific to each category are listed below.

Analysis Services
  • Analysis Services database(s) must be available.
  • SQL Express Edition does not support Integration Services.
  • The Distribution database must be installed and configured for the SQL Server instance.
  • SQL Express Edition does not support Replication.
  • The Report server must be configured.
Integration Services

SQL Express Edition does not support Integration Services.

Replication
  • The Distribution database must be installed and configured for the SQL Server instance.
  • SQL Express Edition does not support Replication.
  • The Report server must be configured

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 showing in the following figure:

result

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

You can download the BCPA from the following URL:

http://www.microsoft.com/en-us/download/details.aspx?id=29302

Advertisements

About Zaim Raza

7 Years of professional experience in Business intelligence using SQL Server and Microsoft SharePoint/.net Technologies. Experience in Data warehousing, Data Modeling, Data profiling, Data Integration, Data cleansing, consultations and enterprise reporting. I want to work for organization where I am able to utilize my skills, experience and knowledge to transform the technology into business value.
This entry was posted in SQL Server features. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s