SqlServerDsc: Using AMO client library for `Microsoft.AnalysisServices.Server`

[johlju]

The assembly Microsoft.AnalysisServices.dll is shipped with SQL Server, the assembly is added to GAC. Using an assembly from GAC can be troublesome since it could be that the correct assembly version need to be loaded for working with a particular SQL Server major version to avoid compatibility issues. Since only one version can be loaded into the one session there can be issues having the wrong version of the assembly.

NOTE: The assembly is shipped with both SqlServer and dbatools and are not dependent on GAC.

There is an external client library that holds the Analysis Service assemblies: Analysis Services client libraries. By downloading an installing AMO it will provision the assembly Microsoft.AnalysisServices.dll and Microsoft.AnalysisServices.Core.dll to the GAC. By installing that client library and keeping it up to date it should hopefully be backward compatible and we can always make sure to load that latest version.

See .\x64_16.0.5230.0_SQL_AS_AMO.msi /? for silent install.

It is then possible to use it like the following:

Using partial loading (first assembly version found in GAC):

PS > $analysisServiceAssemblyInformation= [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.AnalysisServices')
PS > $analysisServiceCoreAssemblyInformation= [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.AnalysisServices.Core')
PS > $analysisServicesObject = New-Object -TypeName 'Microsoft.AnalysisServices.Server'
PS > [System.AppDomain]::CurrentDomain.GetAssemblies() | ? Location -like '*Analysis*' | % { Write-Verbose ("GAC:{0}`t`tVersion:{1}`t`tLocation:{2}" -f $_.GlobalAssemblyCache,$_.ImageRuntimeVersion,$_.Location) -Verbose }
VERBOSE: GAC:True  Version:v4.0.30319
Location:C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.AnalysisServices\v4.0_15.0.0.0__89845dcd8080cc91\Microsof
t.AnalysisServices.dll
VERBOSE: GAC:True  Version:v4.0.30319
Location:C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.AnalysisServices.Core\v4.0_15.0.0.0__89845dcd8080cc91\Mic
rosoft.AnalysisServices.Core.dll

Loading the specific assembly version :

PS > $analysisServiceAssemblyInformation = [System.Reflection.Assembly]::Load("Microsoft.AnalysisServices, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
PS > $analysisServiceCoreAssemblyInformation = [System.Reflection.Assembly]::Load("Microsoft.AnalysisServices.Core, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
PS > $analysisServicesObject = New-Object -TypeName 'Microsoft.AnalysisServices.Server'
PS > [System.AppDomain]::CurrentDomain.GetAssemblies() | ? Location -like '*Analysis*' | % { Write-Verbose ("GAC:{0}`t`tVersion:{1}`t`tLocation:{2}" -f $_.GlobalAssemblyCache,$_.ImageRuntimeVersion,$_.Location) -Verbose }
VERBOSE: GAC:True  Version:v4.0.30319
Location:C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.AnalysisServices\v4.0_15.0.0.0__89845dcd8080cc91\Microsof
t.AnalysisServices.dll
VERBOSE: GAC:True  Version:v4.0.30319
Location:C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.AnalysisServices.Core\v4.0_15.0.0.0__89845dcd8080cc91\Mic
rosoft.AnalysisServices.Core.dll

[johlju]

Closing this. The issue was just to document this somewhere if it is needed in the future.