The PowerShell Module Formerly Known as SQLPS

I am still so floored by the additions and enhancements made to the module formerly known as SQLPS, now known as SqlServer. Not only do the new additions bring the total number of cmdlets from 46 to 71, but they align what we voted for on the Trello board. That's a 54% increase in the number of cmdlets! We'll be up to 700 like Lync and them in no time ;)

So what's new?

As you may know by now, the SQL Server Tools team introduced a ton of new cmdlets for SQL Server Agent and SQL Server logs, as well as fixing a few things with Invoke-SqlCmd and the provider.

The Tools Team also added Always Encrypted which received no votes on the Trello board, but we knew this was coming because Ken Van Hyning said "Currently we prioritize via a couple of signals. Primarily, there is our Connect and CSS channels where we get input from customers. We also have some business directives around new advancements in SQL Server, like Always Encrypted. This isn’t really the way we need to run this though."

What's particularly amazing is that our input actively guided the module as it was being developed! It's really incredible to have such a direct impact on a tool that will help us with our jobs every day.

What else is new?

In addition to the new Agent, Error Log and Always Encrypted cmdlets, Invoke-SqlCmd now supports outputting to .NET DataTables/Sets/Rows. The SQL Server team also added a ConnectionString parameter for those of you who would like the full flexibility of a ConnectionString.

The SQL PowerShell provider also now properly supports the WhatIf and Confirm parameters.

What about scripts that Import-Module SQLPS?

The SQL Server Tools team really put a lot of thought into this, and SQLPS will remain for now, it just won't get updated. In talking to the SQL Server PowerShell Engineer, Matteo Taveggia about it, he said

If you are on a clean machine (or even if you had machine with the June Refresh SSMS) and you install the upcoming version of SSMS, you will indeed get both the new SqlServer module (new location) and the old SQLPS (same old location).

I did this intentionally to smooth the transition for existing users and avoid breaking them unexpectedly. Basically, their scripts (which have Import-Module SqlPs) will continue to work – they just are not going to get the new stuff / updates. Then, when they learn about the new cmdlets and fixes, they can start migrating at their own pace.

Brilliant!

What about the community module SQLPSX?

SQLPSX is a community module that was built to fill the gap of commands that aren't available in SQLPS, now SqlServer. SQLPSX had a module inside called SqlServer, which presents a naming conflict. This conflict can be resolved, however, by using the -Prefix parameter.

Import-Module -Name SQLPSX -Prefix x

Then the commands available within the SQLPSX module will be prefixed with an x, for instance Get-xSqlTable.

Join us on Trello and vote vote vote vote

If you've already joined the SQL PowerShell Community Board on Trello, get over there and vote for the next set of cmdlets. If you haven't joined, head on over and vote. You can vote without being a member of the board, but if you'd like to join, comment here or on our comment card. While you're at it, join the #powershell channel on the SQL Server community's Slack.

It appears that Microsoft also really benefits from code attachments. If you have any to contribute, which would help with the development, please do attach. They make cmdlets (formalized PowerShell commands), but .ps1 totally works.

Any guesses as to what cmdlets are next? Hint: it's up to us to decide with our votes!