Nearly everything has changed for SQL Server PowerShell
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
Light
Dark
0 comments