Using SQL Server Best Practices Analyzer in SQL Server

In this rather short post, I would like to talk about a very handy and easy to use tool (SQL Server 2012 Best Practices Analyzer-SBPA) that provides a lot of information about all the SQL Server 2012 components installed on the system and checks if the configurations are setup according to the recommended best practices.This tool is available for previous versions of SQL Server and SQL Server 2012.You can download the SQL Server 2012 version of the tool from this link.

The supported operating systems are Windows 7, Windows Server 2008, Windows Server 2008 R2.

There is a chance that the SBPA will fail during installation. You probably will have to download and install Microsoft Baseline Configuration Analyzer V2.0 first.

After I install the SBPA tool, I launch it (Admin Priveleges).

Have a look at the picture below

Then I click Start Scan.A new window appears.In this window I can specify the instance name of the SQL Server I want to scan and then choose what SQL Server components I want also to analyse. In my case I specified the SQL Server Engine & Setup of my SQL Server Developer 2012 version.

Have a look at the picture below

Then I click Start Scan once more.Then the scanning begins.Upon completion of the scanning by the SBPA 2.0, a report is generated and is categorized into Errors, Warnings and Compliants. Have a look at the picture below

You will be able to see different errors that exist on the SQL Server instance in relation with the rules configured in the Best Practices Analyzer.We can also click the detailed explanation for each error. In my case the error I decided to look into tempdb-wrong configuration error.There is a also a resolution for the error.

Have a look at the picture below

You can expand the Warning section of the report.In my case one of the warnings is that the AdventureWorks2012 database is configured with the Simple Recovery Model.I will have to decide depending on my disaster/recovery requirements if I need to change that setting or not.

Have a look at the picture below

To recap, this tool validates an SQL Server instance against a fixed set of built-in rules that can help us identify common installation and configuration issues.

Hope it helps!!!

About these ads