2014-04-07

Add AMO without SQLPS

When installing a SQL Server Analysis Services instance without a Database Engine instance to comply with the principle of Least Service the namespace Microsoft.AnalysisServices part of AMO is not available locally, and the PowerShell command
Import-Module -Name SQLPS -DisableNameChecking
fails with the error
Import-Module : The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.
It looks like the SQLPS is not part of a bare Analysis Services installation where the feature selection only is
/FEATURES="AS"
in a command-line installation.

To use the namespace Microsoft.AnalysisServices I found out that all I had to do was to add the type Microsoft.AnalysisServices, but the PowerShell CmdLet Add-Type requires a full assembly name.
The article "Powershell Add-Type – Where’s That Assembly" by Kyle Neier gives the full name for what looks to be SQL Server 2005 with the version 9.n. I am working with SQL Server 2012 that has version 11.n, and would like to prepare for SQL Server 2014 (version 12.n) and beyond. This gives that a simple command like
Add-Type -AssemblyName 'Microsoft.AnalysisServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
is not good enough as it is version-dependant.

Kyle's article showed me where to look for the version and token, and some manual browsing showed me that the assembly is located among the combined (32/64 bit) assemblies. A little split and merge of strings gave this little script to add the type Microsoft.AnalysisServices independant of SQL Server version
$Assembly= $(Get-ChildItem 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.DeploymentEngine').Name.Split('_')
$AssemblyName = "Microsoft.AnalysisServices, Version=$($Assembly[1]), Culture=neutral, PublicKeyToken=$($Assembly[3])"
Add-Type -AssemblyName $AssemblyName

To get the token I take the third item in the array Assembly. The second item is a empty string as there are two underscores ('_') in the folder name between the version part and the token part of the folder name.

I think I am home-safe. That is a least until the path is changed...