Loading SMO in Your SQL Server-centric PowerShell Modules

In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as "Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91".

Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.

1[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Now, however, we must use the fully qualified name and this presents a problem for people who use SMO because we can't be sure what version of SMO exists on the user's system unless we're specifically targeting an SMO version like Microsoft's official module, SqlServer.

The Problem

When I tried publishing dbatools with just RequiredAssemblies = @('Microsoft.SqlServer.Smo'), I received the following error:

Publish-Module fails with "The specified RequiredAssemblies entry 'Microsoft.SqlServer.Smo' in the module manifest '...\dbatools.psd1' is invalid. Try again after updating this entry with valid values.

I looked at the official SqlServer module to see how they handled RequiredAssemblies, and as mentioned previously, noticed that they addressed only the 2016 version of SQL Server SMO. I then looked through GitHub for how other people used RequiredAssemblies, both with and without SMO. Nobody else appeared to have a solution that addressed requiring one of an array of possiblities.

The Solution

I ended loading my assemblies in the psm1 file instead of using the ModuleManifest.

 1$smoversions = "14.0.0.0", "13.0.0.0", "12.0.0.0", "11.0.0.0", "10.0.0.0", "9.0.242.0", "9.0.0.0"
 2
 3foreach ($smoversion in $smoversions)
 4{
 5    try
 6    {
 7        Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
 8        $smoadded = $true
 9    }
10    catch
11    {
12        $smoadded = $false
13    }
14    
15    if ($smoadded -eq $true) { break }
16}
17
18if ($smoadded -eq $false) { throw "Can't load SMO assemblies. You must have SQL Server Management Studio installed to proceed." }
19
20$assemblies = "Management.Common", "Dmf", "Instapi", "SqlWmiManagement", "ConnectionInfo", "SmoExtended", "SqlTDiagM", "Management.Utility",
21"SString", "Management.RegisteredServers", "Management.Sdk.Sfc", "SqlEnum", "RegSvrEnum", "WmiEnum", "ServiceBrokerEnum", "Management.XEvent",
22"ConnectionInfoExtended", "Management.Collector", "Management.CollectorEnum", "Management.Dac", "Management.DacEnum", "Management.IntegrationServices"
23
24foreach ($assembly in $assemblies)
25{
26    try
27    {
28        Add-Type -AssemblyName "Microsoft.SqlServer.$assembly, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
29    }
30    catch
31    {
32        # Don't care
33    }
34}

What you can see in the code is that I iterate through all of the versions of SQL Server, including vNext, 2016, 2014 and so on, all the way down to 2005. If none are found, the module throws an informative exception. If SMO does load, the script then attempts to load the other associated assemblies.

Some assemblies such as Management.XEvent don't exist in lower versions of SMO and will throw an error that I don't care about - we handle version checks later on in our specific scripts such as Copy-SqlExtendedEvent.

Oh, and don't worry about this loaded allll of those assemblies. It only takes 11-40 ms on my machine to run the above script.

So feel free to copy the code above to your own modules that use SMO or if you have a better way, let me know. Happy Holidays!