Today, every PowerShell affectionate DBA jumps for joy. The SQL Server module is available on the PowerShell Gallery.
This means you can now install it with a single PowerShell instruction.
But you better read this first.
PowerShell module SQL Server V 21.0.17099
Yep, that's the one. This is the first version of the module that is published to the Gallery.
So, besides all the joy about the update process, we should not forget there are also improvements, bug fixes and other goodies delivered.
But, I will not talk about those here.
I did the update, and you need to know how that went, so you can do it maybe just a little bit differently!
History
A very, very short tale:
In the beginning, there was a SQL Server, but no PowerShell.
In 2008, a closed mini Powershell was built in SQL Server.
Later, a SQLPS module was created, installed from the SQL Server installation media.
Next, a new module, named SQLServer was developed and you got that by installing SSMS.
And today ...
installing from the gallery
You can take a look using your web browser:
But you don't really have to. We'll just type a few commands.
It's not very hard to install modules from the gallery. Just make sure you started your console or ISE as administrator.
Then you have to pick the right cmdlet for the install or update. You might think "I already have the SQL Server module, so I must choose 'update'", but that will tell you wrong:
Update-Module SqlServer Update-Module : Module 'sqlserver' was not installed by using Install-Module, so it cannot be updated.
Surprised?
That's because PowerShell can't do what it wants to do when updating a module. Your previous version was installed in another way and doesn't count in the versioning logic.
So, over to Install-Module:
Install-Module SqlServer This brings us the next error.
Well, it says it's a warning, but it stopped the install, so I call it an error, wouldn't you?
Ok, we need to use the Force.
Install-Module SqlServer -Force And the installation succeeds. That's what we needed.
If I would be some more prudent and thorough, I would have read the docs first. That says we have to add the -Allowclobber switch if we already have the SqlServer module. But who else would read the docs first, honest?
checking the installation
Let's check what we have:
Get-Module -ListAvailable SqlServer | Select path 
Oops!
The new version is installed as a subdirectory of the module directory:
...\Modules\SqlServer\21.0.17099\
That's actually what we can expect, because it's the way versioning is handled in modules, I think since WMF 5.1.
But .... the older version was not installed this way and is in the parent directory, without version.
...\Modules\SqlServer\
And didn't that warning say the new version would be installed 'side-by-side' with version 20.0?
importing the module
Modules are imported in a certain sequence. When a module is available more than once, the one first encountered on the Module Path will be loaded.
To check this sequence, we do:
$env:psmodulepath -split ';' 
So, in my case, modules in my 'Documents' folder will be imported first, then the \Program files\WindowsPowerShell directory, and so on.
In those directories, each module has its own subdirectory, and inside that one, each version has its own subdirectory.
The highest version number should be loaded. But since we now have a version without version number and directly in a parent directory, what must we expect?
OK, looks good. 21.0.17099 is the version we want.
Let's check the commands:
Seems good. Only thing a bit odd, is the 'script' with version 0.0.
That's because the module contains both a .psd1 and a .ps1 file in the root.
The '.psd1' defines the module type as 'Manifest', while the presence of the '.ps1' makes it a 'Script' type.
Actually, according to the documentation on Module Types on MSDN, only a '.psm1' should do that.
lesson learnt
I think on my next computer, I'll remove the existing SQL Server Module first. It's probably no big deal, but those little aberrations are the ones that may cause us headaches weeks or months later. I really like sticking 100% to the norm. Hours are wasted looking for "but what's different on my machine???". So, from experience, I know it's better to set it right from the start.
But don't forget:
it's really nice to have the Sql Server Module in the PowerShell Gallery!
And once the next version is published, you should use Update-Module SqlServer , and have no problems anymore.
And go thank Ken Van Hyning, the sqltoolsguy, Matteo Taveggia and the team!