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.

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.

trella

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!

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
2 comments on “The PowerShell Module Formerly Known as SQLPS
  1. Ritesh Patel says:

    Hi Chrissy,
    Thanks for your blog post. I’m still a bit confused however. I wish to call powershell 5.0 functionality from a sql server 2012 agent job step using type Powershell.

    SQL Server 2012 installs with sqlps 2.0 which means that at the moment I have to use the cmdexec within the Agent step as Powershell.exe … ….etc.

    Are you saying that this new update does not fix that issue?

    Thanks,
    Riitesh

Leave a Reply

Your email address will not be published. Required fields are marked *

*