Why Your PowerShell Scripts Break When You Install SQL Server Management Studio 2016

If you’re like me, you’re pretty pumped for the release of SQL Server 2016. Chances are you’ve either downloaded the latest RC or the SSMS 2016 Preview Build. What you might not know (or have tried yet) is that your PowerShell scripts are probably going to break. And by probably I mean they will. Let’s look at a really good example: Backup-SqlDatabase. For this demo, I already had a developer instance of SQL Server 2014 installed, and I then installed the SSMS 2016 Preview build (although you’ll also encounter this with any other version of SQL Server combined with either SSMS or a RC build of SQL Server 2016).

I wrote a script that leverages the Backup-SqlDatabase cmdlet. It’s a cmdlet that comes standard as part of the SQLPS module and it… backs up a SQL database. Pretty straightforward, but I like it because it allows you to pass in parameters for a set of backup devices, which means you can multi-thread your backups to multiple files:

$fullbackup = New-Object "Microsoft.SqlServer.Management.Smo.Backup"
$fullbackup.Devices.AddDevice("C:\temp\test_backupfile.bak", "File")
$fullbackup.Devices.AddDevice("C:\temp\test_backupfile.bak2", "File")
$parameters = @{
       Path = "SQLSERVER:\SQL\LOCALHOST\DEFAULT"
       Database = "AdventureWorks2014"
       BackupAction = "Database"
       FormatMedia = $true
       Initialize = $true
       SkipTapeHeader = $true
       BackupDevice = $fullbackup.Devices
}
Backup-SqlDatabase @parameters

If you run this when you have multiple versions of SQL Server Management Studio installed, you’ll get an error that looks like this:

Which is a weird error: “Hey Drew, you know that object type you wanted (that’s already a type that you said it was) and what you plan to use it for? Guess what? I can’t convert it to what it needs.” Strange, and extremely frustrating. It’s part of the reason I haven’t played around much with SQL Server 2016 because installing it on my local machine kept breaking my PowerShell scripts, which I kinda sorta need to use often. I was relegated to using it in a VM if I wanted to test or show off anything. But now that we’re approaching release, I really need to figure this out.

What’s in a manifest, anyway?

Modules in PowerShell can have manifests, which essentially are used to customize the environment or load types that the module needs to function. It’s one of the main reasons I always tell people to load a module instead of doing things like

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

Or

Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Culture=Neutral,Version=11.0.0.0,PublicKeyToken=89845dcd8080cc91

to load the assemblies you need. But that’s what modules are for! Loading assemblies is something a module should do, NOT you (unless there isn’t a module). And that’s what manifests do. It turns out that the manifest for the SQLPS module doesn’t fully qualify the names of the assemblies when they load them (see above), which results in multiple versions being loaded. I confirmed this with Matteo Taveggia of the MSSQL Tiger Team. His explanation:

The reason why you see multiple assemblies (same name, different version) loaded in the PowerShell process is that:

• The SQLPS manifest (typically found under “C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.PSD1) tells PowerShell that a handful assemblies are Required: this causes PowerShell to load them when the module is imported [“RequiredAssemblies” in the .PSD1 file]
• Those assemblies are not specified using their fully qualifies name and since the GAC has multiple version, the most recent is picked (this explains why you ended up with bunch of a 13.0.0.0 assemblies) – these assemblies are loaded before the module is imported
• When the module is finally imported, most of the assemblies are loaded once again (this time with the 12.0.0.0 version) as the result of being referenced by the SQLPS module
• When PowerShell tried to resolve names/types gets confused and yield the mysterious error that makes you and me even more confused L

The flow outlined above explains why your attempt to work-around by tweaking the PSModulePath environment variable and removing the SQL2016. Your workaround did not work because of the following:

• The way PowerShell does the module lookup is sequential: this means that the original PSModulePath and the tweaked one always made PowerShell import the 12.0.0.0 module (in one case because the “120” path came before the “130”, in the other case because you just removed the “130” path
• However, you were victim of the “loading of the RequiredAssemblies” logic described above, which is not related to the PSModulePath

As I said, this desire of picking the right module lead to identify the second versioning issue:

• It turned out that SQLPS.PSD1 (the manifest file) was never updated in the version. If you run “Get-Module -ListAvailable” on a machine with multiple versions of SQL Tools, you’ll likely see: 

• This means that the user has no way to specify which module version he/she wants to import.

Fixing the Problem

One of the steps I tried to remedy the problem was removing the SQLPS module directory from the PSModulePath environment variable, to see if the Import-Module would skip over it. Turns out I was only half right: I should have removed non 2016 versions of the module path, as Matteo goes on to explain:

The workaround I would recommend, is similar to what you tried:

1. Update the PSModulePath to exclude the “120” path
2. Just let PowerShell find and load the SQL2016 (aka “130”) module and assemblies
3. Use the cmdlets, SMO etc… normally

I’d be surprised if you ran into “breaking changes” or other issues because of this. I would not recommend touching/editing the manifest files: these files are digitally signed, so changes to them could/would be detected by PowerShell (esp. if you are running in a severely locked down environment)

So, we just have to update our environment variables and delete any reference to non-130 versions in the paths:

Then, fire up a new PowerShell session and validate your module paths with $env:PSModulePath:

And viola, our code works again!

Community Outreach Is Key

One thing I can’t stress enough about SQL Server is how awesome the community is and this topic is certainly no exception. If you don’t already, you should follow the MSSQL Tiger Team on twitter (and why aren’t you on twitter, again?). Don’t be afraid to tweet questions to them! That’s how this post came about, and they weren’t aware of the problem and now they’re committed to a fix. I’m a huge proponent of the SQLPS module and feedback for this team is vitally important. But the same could be said for any part of SQL Server. And I’d be remiss if I didn’t thank the following people especially for their time and effort:

• Matteo Taveggia (@matteo_taveggia) for taking the time to dive into the issue and finding the answer with the SQL Server SQLPS team
• Ken Van Hying (@sqltoolsguy) for facilitiing the conversations with the teams at Microsoft
• Amit Banerjee from Microsoft of putting me in touch the SQLPS team
• The rest of the SQL Server Tiger Team (@mssqltiger) for being so engaged and passionate about community feedback on their product
• Mike Fal (@mike_fal)for helping me validate the fix and testing with me

This fix should apply to any cmdlet you’re experiencing problems with after you install SSMS 2016. But I’d love to hear about any other problems this helps you with in the comments!