SQL Server Best Practice Analyzer (BPA) – v2012 – Against SQL Server v2014 and Beyond

Background

There are a couple of things one needs to do to get “SQL Server Best Practice Analyzer” (BPA) v2012 to work against MS SQL Server v2014 and above.

In an earlier post we discussed how we determined that we need to have “SQL Server Shared Management Objects” (SMO) installed.

 

Guidelines

  1. Copy SQL2012BPA
    • Copy SQL2012BPA from C:\Windows\System32\BestPractices\v1.0\Models
    • To C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\
    • Special Thanks to Rob “Barkz” Barker
  2. Download the following “MS SQL Server Microsoft® SQL Server® 2012 Feature Pack” components
    • Microsoft® SQL Server® 2012 Shared Management Objects
    • Microsoft® SQL Server® 2012 Analysis Management Objects
  3. Modify the Powershell in this way
    • Microsoft® SQL Server® 2012 Shared Management Objects [ SQL Server SMO ]
      • $SMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91″)
        • Original
          • if($SqlServerVersion -ne 11) # Not Equal
        • Revised
          • if($SqlServerVersion -lt 11) # Less Than
      • Microsoft® SQL Server® 2012 Analysis Management Objects [ SQL Analysis AMO ]
        • $AMOAvailable = [System.Reflection.Assembly]::Load(“Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91”)
          • Original
            • if($SqlServerVersion -ne 11)
          • Revised
            • if($SqlServerVersion -lt 11)

 

 

Download

Versioning

Which Versions of SQL Server Feature pack do we need?

Took a good look at the code and this is how it reads…

SMO Assembly Reference

Code


$SMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")

AMO Assembly Reference – AnalyisServices.ps1

Code


# AMO Assembly Reference
$AMOAvailable = [System.Reflection.Assembly]::Load("Microsoft.AnalysisServices, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91")


Explanation

The code is specifically looking for Version=11.0.0.0.

SQL Server version 11 is MS SQL Server v2012.

Links

Here are the components of “Microsoft® SQL Server® 2012 Feature Pack” that we need.

Product Version x64 x32
 Microsoft® SQL Server® 2012 Shared Management Objects ( SharedManagementObjects.msi )
Link Link
Microsoft® SQL Server® 2012 Analysis Management Objects ( SQL_AS_AMO.msi )
Link Link

 

 

Please download and install.

 

Scripts

Modified the PowerShell scripts in the following way

Original


if($SqlServerVersion <strong>-ne</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}

Revised


if($SqlServerVersion <strong>-le</strong> 11)
{
	Get-LogText $cError "Engine Rules Execution Interrupted - The instance being scanned exist for SQL Server version which is not supported." >> $EngineLogFile
	AddElementToDocument $XmlDoc $tns $SqlServerVersion "SufficiencyCheck" "SQLServerVersion"
	$XmlDoc
	exit
}



 

Github

Here is the Github repository for the Scripts.

 

Commendation

Rob “Barkz” Barker

Special commendation to Mr. Rob Barker’s ( Barkz ) work:

GETTING SQL SERVER 2012 BPA WORKING ON WINDOWS SERVER 2012 R2
Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

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