SQL Server Best Practice Analyzer – “Engine – Management Studio is unavailable”

Background

Trying to use “SQL Server Best Practice Analyzer” v2012 against a SQL Server 2014 box.  But, failing badly…

Error

Collected Data

Rule

Rule – SufficiencyCheck / IsManagementStudioAvailable

 

Here is what things look like in the “View Report“\”View Baseline Configuration Analyzer Report“\”Results” tab

managementstudioisunavailable

 

And, in the “View Report“\”View Baseline Configuration Analyzer Report“\”Collected Data” tab

sufficienycheck-20161020-0458pm

 

Check the Code

The SQL Server Checks are performed by codes installed on C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models.

For the SQL2012BPA Engine they are placed in a relative path “SQL2012BPA\Engine

Here is what that folder looks like….

sql2012bpa-engine-folder

The affecting file is Engine.ps1

And, the code that throws the error is ….

smoavailable
Specifically

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

Code

Took out that code and fleshed it out a bit and this is what it looks like …


$EngineLogFile = "engine.log"
$IsManagementStudioAvailable = $true

## StrictMode set to Latest
#Set-StrictMode -Version Latest

$ErrorActionPreference = 'Stop'

#XML Document related variables
$cSQL2012BPA = "SQL2012BPA"
$tns = "urn:SQL2012BPASpace"

[String]$cInfo = "[I]"
[String]$cError = "[E]"


function Create-DocumentElement( $ns, $name )
{
    [xml] "<$name xmlns='$ns'/>"
}

function XmlEncode([String]$source)
{
    [System.IO.StringWriter]$writer = new-object ("System.IO.StringWriter")
    [System.Xml.XmlTextWriter]$xmlWriter = new-Object ("System.Xml.XmlTextWriter") $writer
	
	$xmlWriter.WriteValue($source)
	return $writer.ToString()
}


# ------------------------------------------------------------------#
# Function Name	: Get-LogText($TextType, [String]$TextDetails)		#
# Description	: Generates log file content with date time details	#
# Arguments		: $TextType	- Error or Info							#
#				  $TextDetails - dynamic text as per Rule IDs		#
# ------------------------------------------------------------------#
function Get-LogText($TextType, [String]$TextDetails)
{
	return $TextType + " " + (Get-Date).ToString() + " [" + $TextDetails + "]"
}



### Parent Node creation for the XML
$XmlDoc = Create-DocumentElement $tns $cSQL2012BPA

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

if($SMOAvailable -eq $null -or $SMOAvailable -eq "")
{
	Get-LogText $cError "Engine Rules Execution Interrupted - Management Studio is unavailable." >> $EngineLogFile
	$IsManagementStudioAvailable = $false
	AddElementToDocument $XmlDoc $tns $IsManagementStudioAvailable "SufficiencyCheck" "IsManagementStudioAvailable"
	$XmlDoc
	exit
}
else
{

	$text = (Get-LogText $cInfo "Microsoft.SqlServer.Smo, Culture=Neutral,Version=11.0.0.0, PublicKeyToken=89845dcd8080cc91 - Loaded successfully!").ToString()
	$text >> $EngineLogFile

}

#$text = (Get-LogText $cInfo "RID0018 – SQL SERVER Engine service account - Execution Completed").ToString()
$text = (Get-LogText $cInfo "Execution Completed").ToString()
$text >> $EngineLogFile
 

# ---------------------------------- #
# XML Document Creation Code - End   #
# ---------------------------------- #

#Send the xml Doc to the output stream
$XmlDoc

 

Conclusion

It seems that the problem is likely not “SQL Server Management Studio“, but “SQL Server Management Objects” (SMO).

But, that will have to wait until we can install SQL Server Management Object on the DB Server we are targeting.

 

Additional Reading

  1. Rob “Barkz” Barker
    • Pure Storage – PowerShell Guy
      • GETTING SQL SERVER 2012 BPA WORKING ON WINDOWS SERVER 2012 R2
        Link
  2. Adam W. Sexton
    • Known issues installing SQL 2008 R2 BPA relating to Remoting
      Link
  3.  social.msdn.microsoft.com/Forums
    • SQL Server 2008 R2 BPA \ SQL Server  >  SQL Server Manageability
      Link
    • SQL Server 2008 R2 Best Practice Analyzer (BPA) : problems connecting to sql
      Link
  4. Microsoft Connect
    • SQL Server 2008 R2 Best Practice Analyzer (BPA) : problems connecting to sql – by VinnyP22
      Link

 

 

One thought on “SQL Server Best Practice Analyzer – “Engine – Management Studio is unavailable”

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