SQL server 2008 R2 Best Practice analyzer

To find out the configuration best practices for your SQL server 2008 R2 and 2008, you can use this “Microsoft SQL Server 2008 R2 Best Practices Analyzer(BPA)” using the following link.

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

There are two prerequisites, Please download and install prior to install the best practices analyzer. 

1. Microsoft Baseline configuration Analyzer 2.0
2. PowerShell V2.0

Once, you done with installation.

Configuration:

Important note: Check with administrator before you run the following command

SQL server BPA can connect to both local and remote sql servers.

1. Open the Powersheel as an administrator
2. Enter the following command
   “Enable-PSRemoting”
3. Enter “Y” to overwrite the PowerSheel configurations

Scan for Best practice:

1. Open the “Microsoft Baseline configuration Analyzer 2.0” from the start menu.
2. Select “SQL server 2008 R2 BPA”
3. Click on the parameters tab and enter the required parameters and Select the required objects like “Database Engine”, “Analysis Services” ,“Integration Services” and Etc.
4. Wait the for the reports to come.

We can see the Errors and warning with different Components. These information is very useful for configuring you SQL server 2008 R2 and 2008.

following are some use full error messages for your reference.

Error in Engine:

Category: Performance

Source: localhost

Issue: This instance of SQL Server has only one tempdb data file

Impact: Under a heavy load, the tempdb database can become a single point of contention and affect concurrency and performance

Resolution: To reduce contention in the tempdb database, increase the number of tempdb data files, and configure the required startup trace flags.

 

Warning in Analysis Services:

Category: Performance

Source: localhost

Issue: One or more dimensions have all attribute relationships defined as Flexible

Impact: The RelationshipType property determines whether Analysis Services creates rigid or flexible aggregations. After an incremental update on a dimension, flexible aggregations are dropped and must be reprocessed, but rigid aggregations persist

Resolution: Examine the relationship between attributes and define them as Rigid when the relationship between the attributes is not likely to change such as City and State

 

Warning in Integration Services:

Category: Performance

Source: localhost

Issue: The Sysssislog table was found in the master or msdb database

Impact: Integration Services package logging can generate a large volume of lengthy log entries. You may experience decreased performance if you log to a system database, such as the master or msdb database

Resolution: A good practice is to create a separate database for Integration Services logging. You do not have to create the Sysssislog table manually. If the table does not already exist, Integration Services creates this table for you when you select a SQL Server database as your logging destination

 

Thanks,

Gowdhaman