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.


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 :3

$smoversions = "", "", "", "", "", "", ""

foreach ($smoversion in $smoversions)
        Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
        $smoadded = $true
        $smoadded = $false
    if ($smoadded -eq $true) { break }

if ($smoadded -eq $false) { throw "Can't load SMO assemblies. You must have SQL Server Management Studio installed to proceed." }

$assemblies = "Management.Common", "Dmf", "Instapi", "SqlWmiManagement", "ConnectionInfo", "SmoExtended", "SqlTDiagM", "Management.Utility",
"SString", "Management.RegisteredServers", "Management.Sdk.Sfc", "SqlEnum", "RegSvrEnum", "WmiEnum", "ServiceBrokerEnum", "Management.XEvent",
"ConnectionInfoExtended", "Management.Collector", "Management.CollectorEnum", "Management.Dac", "Management.DacEnum", "Management.IntegrationServices"

foreach ($assembly in $assemblies)
        Add-Type -AssemblyName "Microsoft.SqlServer.$assembly, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
        # Don't care

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!

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
5 comments on “Loading SMO in Your SQL Server-centric PowerShell Modules
  1. Ben Miller says:

    Are you saying that you load EVERY smo version? That won’t be a good idea for those that use New-Object to get the objects in SQL. When you have multiple versions of SMO loaded and do a New-Object then it will use the lowest version which could be problematic if you have 10 on your machine and get a version 10 to manage a 13 version of SQL.

  2. Ben Miller says:

    Nevermind. I see what you did. You use the latest first in the list and New-Object actually uses the first one that is loaded. So if I load first then I load then the New-Object will use If you load 13.0 first then it will use that even if you load 14.0 after. Thanks for letting me post.

  3. Jakob Bindslet says:

    Great solution Chrissy. I’m gonna borrow it :-)

  4. Lesley Jacobs says:

    I encountered this problem as well, but some solution I came across solved it like this:

    $sqlServerSnapinVersion = (Get-Command Restore-SqlDatabase).ImplementingType.Assembly.GetName().Version.ToString()

    The way I read it, it boils down to letting Powershell look up the version itself to determine which version of SQL Server to use. That way you don’t need to use any kind of iteration, as it only returns a single version of it. I thought it might be interesting to share that here.

    We wanted to restore a database, so it’s a little more specific, but this is how we implemented it in our script. Might help someone out there by providing a ready-to-use example :)

    $relocateParameters = “Microsoft.SqlServer.SmoExtended, Version=$sqlServerSnapinVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

    $RelocateDataFile = New-Object “Microsoft.SqlServer.Management.Smo.RelocateFile, $relocateParameters”(“$Database”, “$DatabaseLocation\\$Database.mdf”)

    • Chrissy LeMaire says:

      Hey Lesley,
      Thanks for the comment! That is indeed a valid way of loading the assemblies, but only if you have the SqlServer or SQLPS module installed. If your module relies on the SqlServer module and you use a psd1, you can add SqlServer to RequiredModules and it’ll automatically load everything for you πŸ‘

Leave a Reply