2016 was the best year in SQL PowerShell history! I guess I should have mentioned that here sooner.

Wired® Magazine recently said that Microsoft SQL Server was the surprise hit of the year. The primary cited reason is the SQL Server port to Linux, but there are several other reasons to be really excited about SQL Server and SQL Server PowerShell is one of them.

Independent, frequent releases of SSMS

Thanks to the new release cycle of SQL Server Management Studio (SSMS), which comes out nearly every month, the SQL Tools team could deliver even more without making us wait until the next major release of SQL Server.

You can download the latest version of SSMS 2016.

A new SQL PowerShell module

When the first update-release of SSMS after SQL Server 2016 was ready, the SQL Tools team also announced that they would be putting the new cmdlets that they had been building into a new PowerShell module named SqlServer.

This let them give us code fixes and new cmdlets, without affecting the version that SQL Agent uses.

Import-Module SqlServer

New cmdlets!

As of this writing, we have received more than 30 new cmdlets since SQL Server 2016 became generally available.

Sign ins

Add-SqlLogin

Get-SqlLogin

Remove-SqlLogin

Data

Read-SqlTableData

Read-SqlViewData

Write-SqlTableData

Error log

Get-SqlErrorLog

Set-SqlErrorLog

SQL Server Agent

Get-SqlAgent

Get-SqlAgentJob

Get-SqlAgentJobHistory

Get-SqlAgentJobSchedule

Get-SqlAgentJobStep

Get-SqlAgentSchedule

Always encrypted

Add-SqlAzureAuthenticationContext

Add-SqlColumnEncryptionKeyValue

Complete-SqlColumnMasterKeyRotation

Get-SqlColumnEncryptionKey

Get-SqlColumnMasterKey

Invoke-SqlColumnMasterKeyRotation

New-SqlAzureKeyVaultColumnMasterKeySettings

New-SqlCngColumnMasterKeySettings

New-SqlColumnEncryptionKey

New-SqlColumnEncryptionKeyEncryptedValue

New-SqlColumnEncryptionSettings

New-SqlColumnMasterKey

New-SqlCspColumnMasterKeySettings

Remove-SqlColumnEncryptionKey

Remove-SqlColumnEncryptionKeyValue

Remove-SqlColumnMasterKey

Set-SqlColumnEncryption

Code fixes

WhatIf and Confirm are now fully supported in the SQL PowerShell provider. Before, if you tried to use -WhatIf or -Confirm in your script, it would just go ahead and run without prompting you.

Improvements to existing cmdlets

Most notably, improvements to Invoke-SqlCmd to allow a connection string or to output results as a DataTable.

Invoke-Sqlcmd

-ConnectionString Parameter

-OutputAs (As) Parameter

Three big fixes

Much of this new SQL PowerShell movement was started by the Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?” blog post, which asked Microsoft to fix three big issues that were hindering the adoption of PowerShell within the SQL Server community.

The SQL Server team then recruited Matteo Taveggia ( twitter ) from another team to become the SQL PowerShell engineer and help address the following three issues (as linked to their related Connect Items)

• SQLPS module is slow to load
• Loading SQLPS module changes current directory to PS SQLSERVER:\>
• SQLPS module uses unapproved PowerShell verbs

If you’ve worked with SQLPS and have been frustrated by these issues, they’re now resolved, along with several other fixes!

The community becomes empowered

of these changes and fixes really excited the SQL PowerShell community in SQL Server 2016, and Ken Van Hyning, Engineering Manager for SQL Server Client Tools ( twitter ), is partly to thank for that. Ken paved a path for the SQL Server team to interact directly with the community and spoke about their new “community focused engineering” at a number of SQL Server conferences.

If you haven’t been part of this movement already, you are encouraged to join in! Links for community involvement are at the bottom of this article.

SSRS joins the fun

Seeing the excitement of customers over improvements to the primary SqlServer module energized the SSRS team, who tapped Microsoft’s new push for openness and began to develop their own module.

Around Spring of 2016, the SSRS team started working on PowerShell commands for working with your SSRS instance. This group decided to go straight to GitHub to share their cmdlets, so they could benefit from quick feedback and a rapid development cycle.

In November of 2016, the SSRS team announced the availability of these commands on GitHub.

Analysis Services (SSAS) gets in gear

This is an area where we definitely need lots more community involvement. I have filed a couple Connect items for a better PowerShell experience for SQL Server Analysis Services (SSAS). One item has already been approved and will hopefully be in the next build of SSMS 16. I have filed another item for pipeline support that will hopefully get enough votes to be worked on.

But we need more ideas! Getting this far took a TON of input from the community, and especially when it comes to SSAS, we need more help, more voices!

Master Data Services (MDS)

Master Data Services is another feature of SQL Server and, from what I’ve heard, it’s slowly and steadily growing its user base. You can use the MDSModelDeploy tool to create a package to deploy your data and changes from one environment to another. Unfortunately, this tool is completely DOS-based and is a huge example of how an entire software package would be easier to develop if only it had a PowerShell command in place of this MDSModelDeploy.exe utility.

Specifically, Parameter Validation Sets would be very helpful, but auto-completion of object choices on the instance of MDS that you’re trying to work with would really help customers develop and promote MDS packages to their production environments much more quickly.

If you would like to see this capability, vote for this Connect Item.

Power BI

And finally, we’d really like to see PowerShell for Power BI. The community recently came together and gave over 660 upvotes to the request for PowerShell for PowerBI. And community member, Rob Sewell ( t ), came up with a few ideas for what those commands should be.

Join us!

This was a team community effort and we need people like you to join in and tell us which commands are important.

sqlps.io/vote – SQL PowerShell cmdlet design and voting

sqlps.io/ssms – SQL Management Studio improvements

sqlps.io/slack – SQL Community Slack

#trello-powershell

#trello-ssms

#sqlpsx

#dbatools

#dbareports

#closedasfixed

@closedasfixed – Twitter Open source Twitter bot powered by PowerShell

sqlps.io – PowerShell Virtual Chapter of PASS

Aaron Nelson & Chrissy LeMaire