With the release of SQL Server Management Studio 17.0 (SSMS) the SqlServer PowerShell module has finally been decoupled and now can be installed independently. To take full advantage of this change I recommend using PowerShell 5.0 (if your on Windows 10 or Server 2016 then this is your default).
Full details of the SSMS 17.0 updates https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
The official installation details for SqlServer module are at
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module but they are a bit light on currently so this post will walk through the process in more detail. You can also find the public listing of the SqlServer on PowerShellGallery.
Firstly check what modules you have installed.
If you have installed the SQL Server DBEngine then you should have SQLPS for that particular version. On my server I upgraded SSMS 2016 to SSMS 16.0 so I have both 130 and 140 versions installed.
What I don't currently have installed is the SqlServer PowerShell module, and my SQLPS module hasn't been updated with the upgrade of SSMS to version 17.0
So let's download and install the latest SqlServer module.
First, check your PowerShellGet is configured for the public gallery (the default setup).
You should have the PSGallery repository setup. By default it will be untrusted, this will just mean you get prompted before installing any modules from it. It is recommended public repositories should not be marked as trusted, but internal private repositories can be to make the installations easier.
If you don't have any other repositories configured then you won't have to supply the repository name to any future commands, but if you do then you can simplify the module searches by specifying the repository name. This is very useful if you want to host private custom copies of the publicly available modules on a private repository.
Now you can search for the module in the gallery to check what the most recent version is available
NOTE: If prompted to continue enter 'y'
Add the -AllVersions parameter to list all the available versions. Currently there is only one version in the gallery but this will change overtime.
To install the module from the gallery run Install-Module. Use the Scope parameter to install for either CurrentUser or AllUsers based on your needs. With PowerShell 5.0 it is also possible to install versions side-by-side. In my case I am installing the module for all users and requiring a specific version (the current one).
To avoid the error message like "A command with name '' is already available on this system " I will also use the parameter -AllowClobber.
Now check that the module is available for use on the server with
Notice that the path is now one of the common $PSModulePath paths. This is also part of the improvements that these changes bring.
Now going forward to update the module you can use the Find-Module and Update-Module cmdlets. Look out for my post on automating that process next month.
Happy PowerShell'ing
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
Full details of the SSMS 17.0 updates https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
The official installation details for SqlServer module are at
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module but they are a bit light on currently so this post will walk through the process in more detail. You can also find the public listing of the SqlServer on PowerShellGallery.
Firstly check what modules you have installed.
Get-Module -Name SQL* -ListAvailable
What I don't currently have installed is the SqlServer PowerShell module, and my SQLPS module hasn't been updated with the upgrade of SSMS to version 17.0
So let's download and install the latest SqlServer module.
First, check your PowerShellGet is configured for the public gallery (the default setup).
Get-PSRepository
You should have the PSGallery repository setup. By default it will be untrusted, this will just mean you get prompted before installing any modules from it. It is recommended public repositories should not be marked as trusted, but internal private repositories can be to make the installations easier.
If you don't have any other repositories configured then you won't have to supply the repository name to any future commands, but if you do then you can simplify the module searches by specifying the repository name. This is very useful if you want to host private custom copies of the publicly available modules on a private repository.
Now you can search for the module in the gallery to check what the most recent version is available
Find-Module -Name SqlServer
NOTE: If prompted to continue enter 'y'
Add the -AllVersions parameter to list all the available versions. Currently there is only one version in the gallery but this will change overtime.
To install the module from the gallery run Install-Module. Use the Scope parameter to install for either CurrentUser or AllUsers based on your needs. With PowerShell 5.0 it is also possible to install versions side-by-side. In my case I am installing the module for all users and requiring a specific version (the current one).
To avoid the error message like "A command with name '
Install-Module -Name SqlServer -Scope AllUsers -RequiredVersion 21.0.17099 -AllowClobber
Now check that the module is available for use on the server with
Get-Module -Name SqlServer -ListAvailable
Notice that the path is now one of the common $PSModulePath paths. This is also part of the improvements that these changes bring.
Now going forward to update the module you can use the Find-Module and Update-Module cmdlets. Look out for my post on automating that process next month.
Happy PowerShell'ing
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
