Installing SqlServer PowerShell module from PowerShellGallery.com

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.

Get-Module -Name SQL* -ListAvailable

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).

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 '' is already available on this system" I will also use the parameter -AllowClobber.

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.

Announcing the release of my SQL Server DSC Resource Module mlSqlPs on PowerShellGallery and Github

I've been automating the installation of SQL Server components for 10+ years now so it's no surprise I am a fan of PowerShell and Desired State Configuration (DSC). Given my background in large, high volume, and diverse environments, the main objective I always think about is how the process can be easily replicated time and time again but also easily adapted to fit the needs of diverse configurations.

Unfortunately to date I have found the xSqlPs DSC module falls just short of these needs, but that was also the intent of the DSC Resource Kit Waves so that it would provide the "starting blocks" for organizations to customize and extend the resources. I had already started talking with the team about how to improve the SQL resources, but now that the DSC Resource Kit modules has been made open source on github this opens the door for the community to contribute into the standard set of resources. All changes I mention below are pending pull requests back into the main branch of the xSqlPs resource on Github.

The PowerShell Team is also working on their nuget gallery at PowerShellGallery.com, which is going to greatly assist with the new PowerShellGet feature in PowerShell 5.0. The gallery is still in preview however I am starting to contribute to it and this module will be available there (link below).



To provide these features to everyone straight away I have also launched my own version of the SQL DSC module called mlSqlPs. This will allow me to get changes out quickly, while also working to merge the best of my work back into the main branch.

Version 1.0.0.0 of mlSqlPs DSC Module provides the following resources and enhancements.

• xSqlServerInstall - Enhanced
  Installs SQL Enterprise on target machine.
  This resource has been enhanced to provide capabilities for aligning to SQL best practices (e.g. Data, Log, TempDb paths)
• xSqlHAService
  Enables SQL high availability (HA) service on a given SQL instance.
• xSqlHAEndpoint
  Configures the given instance of SQL high availability service to listen port 5022 with given name, and assigns users that are allowed to communicate through the SQL endpoint.
• xSqlHAGroupconfigures
  An SQL HA group. If the HA group does not exist it will create one with the given name on given SQL instance and add the HA group database(s) to local SQL instance.
• xWaitForSqlHAGroup
  Waits for an SQL HA group to be ready by checking the state of the HA group of a given name in a given interval till either the HA group is discoverable or the number of retries reached its maximum.
• xSqlAlias - New
  Configures Client Aliases in both native and wow6432node paths. Supports both tcp and named pipe protocols.

The module contains a help file in both HTML and MD format, as well as some Sample files.

You can get the module at the following locations:

PowerShell Gallery
https://www.powershellgallery.com/packages/mlSqlPs/

GitHub - Want to contribute let me know
https://github.com/matticusau/mlSqlPs


Are you running Windows PowerShell 5.0? You can also get the module with the new Install-Module cmdlet.

#Search for the module on the default gallery
Find-Module -Name mlSqlPs -Repository PSGallery;

#Install the module from the gallery
Install-Module -Name mlSqlPs -Repository PSGallery;

#Get the list of resources
Get-DscResource -Module mlSqlPs;

And there you go, create your configuration, push or pull the mof to the node and watch the magic.


Keep checking the GitHub repository and PowerShell Gallery for updates on this module.


Legal Stuff: As always 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.