Installing The SQL Server Module from the PowerShell Gallery

A long time ago, on a server far, far away

PowerShell automation for SQL Server is pretty rad. You don’t need me to tell you that: lots of amazing people are doing amazing things with it. One of the things (if not the main thing) required for this level of awesome is the SQL Server PowerShell module in general, and the SMO libraries in particular. Previously if you wanted to interact with these things with PowerShell or import the module, you’d have to at least install SQL Server Management Studio (SSMS) on a machine.

For local machines that you do your everyday work on, this is probably fine, if not required for you to do you for your daily job. Once you write a kick-ass script to do something, though, you’d probably want that script to be run and executed on remote servers as either a SQL Agent job or Windows scheduled task (or through some sort of automation tool, take your pick). If you code relies on SMO libraries or uses Import-Module sqlserver  (or Import-Module SQLPS ) it’s going to fail on any server that doesn’t have it installed. Which means you’d previously have to install SSMS on servers. Whether or not SSMS belongs on servers is up for debate, but if all I want is my automation scripts to work somewhere, I hate installing unnecessary software where I don’t need to. Especially 1.5GB worth of something when all I really care about is roughly ~80MB. Plus, have you sat through an SSMS install recently? It isn’t what I’d call “snappy.”

The PowerShell Gallery

The PowerShell Gallery is a software repository maintained by Microsoft. Essentially, if you have a module, you can publish it. Then, anyone can get your module right in their own local environments. There’s even code review; if you submit anything that doesn’t meet their guidelines, you’ll get notified. For example: I published a module for updating Google Dynamic DNS services via PowerShell. I had some things in the code that weren’t considered best practices:

Once I had them fixed, I just had to re-upload them and boom, I’m published.

The SQL Server module follows this same paradigm. The tools team writes a module, and in addition to including it in the SSMS installer they can put it here as well. Which means now you can just use the Gallery cmdlet Install-Module  to install the module:

Note: you may wonder why I put the -Force  parameter on my script there; I already had the module installed, so in order to show the example, -Force  will override and reinstall any module you already have installed.

And if you want to get the source module, or install it yourself, you can use the Save-Module  command to download it all, and peek at it. For example, here’s the directory structure:

Score.

The Fine Print

I bet you’re excited to get started. I am too! If you’re running Windows 10 or Windows Server 2016, this “just works.” If you’re on anything else though, you’ll need some updates. For example, if you’re still running a lot of Server 2012 R2 boxes, running Install-Module yields this:

That’s because, out of the box, Server 2012 R2  is running PowerShell 4.0. These Gallery cmdlets require PowerShell 5. To upgrade, you either need to upgrade to PowerShell 5.0 and that means installing Windows Management Framework 5.0. This is compatible with versions of Windows as far back as Windows 7, and Windows Server as far back as 2008 R2. Anything earlier, and you’re out of luck. This also requires the .NET framework 4.5 (or above). That means system updates, which could (potentially) lead to system reboots. Plan (and for the love God test) accordingly!

There’s a couple other hitches as well. One, and this sort of goes without saying, you need internet access for this to work. If your machines are behind any kind of filtering or firewall restrictions that prevent them from talking out to the internet, you’ll need to either open them up or use the Save-Module  feature to download the bits and install them yourself. Secondly, you need Administrator access for this to work. And three, if you do install them manually, you might have different versions installed for different users (or service accounts).

It’s a beautiful day

Aside from the fact that installing the PowerShell module for SQL Server is now easier than ever, this change reflects something bigger and more exciting: the fact that Microsoft has been listening to community feedback. This, more than anything, is the most encouraging and exciting part of this change. There’s also some new cmdlets added this release and some new awesome features in SQL Server Management Studio.

Overall, I’m thrilled, and hopefully you are too. What are you waiting for? Get that new module and try it out today! And keep your feedback coming to Microsoft in any form you can. It’s obvious they are listening.