You may recall that earlier this year PowerShell MVP Chrissy LeMaire ( b | t ) and I did a Q & A with Ken Van Hyning ( b | t ), Engineering Manager for SQL Server Client Tools team.
Back at the end of May I asked Ken if he could share with us what new PowerShell features would be coming at the end of June. One of the things Ken told us was new cmdlets every month and we can now see the first deposit on that promise.
Even the Longest Journey Starts with a Single Step
For me, this is a lot like when Neil Armstrong landed on the moon. Except that instead of stepping onto the moon we’re all the way back on Earth, stepping into the rocket ship, or just now launching; but that’s OK! We’ll get there, and it’s going to be an amazing ride. Oh the places we will go, with this PowerShell for SQL Server thing!
What’s happening?
A new module name for starters: SqlServer. To be able to achieve this monthly release cycle and add lots of features without breaking anything currently running in someone’s production SQL Server Agent environment, the SQL Tools team had to create all this new functionality in a new module, with a new name. You will now need to Import-Module SqlServer to get all of the new capabilities.
Chrissy and I inquired why the SQL team needed to make this change. The full answer to that is probably long enough to make its own article (or two) so I will not go into detail today. I will say that from what we learned, this sounded like the best options given the whole situation.
25 new cmdlets
The SQL team just added 54% more PowerShell cmdlets than we had available in SQL Server just last week. And there’s going to be more! For this particular release the cmdlets included a backlog of Always Encrypted cmdlets, as well as some of the top requests from the community which included cmdlets for SQL Agent Jobs and reading the SQL Server Error Log.
Iterative Development
This is a whole new world for Microsoft and especially for the SQL Server [box] product team. They have been doing these monthly releases for several months before SQL Server 2016 ever shipped and have already worked out quite a large number of kinks to make it more fluid.
Verb Pairs
PowerShell is intended to use pairs of Verbs to do things. So if you can Get-Something you should be able to Set-Something, if you Read-Something you should be able to Write-Something, if you Add-Something you should be able to Remove-Something; you get the idea. With that in mind, when we asked for Get-SqlErrorLog that’s all most of us focused on. When Matteo from the SQL Tools team was building Get-SqlErrorLog he knew it wouldn’t be proper if there wasn’t a Set-SqlErrorLog (even though I don’t think we asked for it).
The help for Set-SqlErrorLog says it “Sets or resets the maximum number of error log files before they are recycled” which is something readily available in the SMO code. That’s great because now I can change that setting on all my servers with about a line and a half of PowerShell code! But by Matteo adding that simple Set-SqlErrorLog it got me thinking, what if? What if your SQL Server usually runs for a long time before a reboot and you want set a maximum size for your SQL Error Logs to grow to before they automatically start a new one? Wouldn’t that be GREAT!?!
Asking for More
So, even though setting a max size for my SQL Error Logs was kind of a pipe dream, I asked for it. Here’s the response I received: “I did not see it exposed in SMO. However, I see (https://support.microsoft.com/en-us/kb/2199578) that SQL Server supports it… so perhaps we could look into adding it if there is demand (one thing that I like about the idea of “frequent” SSMS releases, is that we should be able to get a feeling of the current implementation and improve/fix/iterate a little… which is closer to the scripty-nature of PowerShell; do you think the community would like the idea as well? Or do you think they would be quickly upset if something changes a little from release to release?)”
Well community, would you like to be able to set a maximum size for your SQL Error Logs? Answer here: https://connect.microsoft.com/SQLServer/feedback/details/2878016
As for Matteo’s second question: “Or do you think they would be quickly upset if something changes a little from release to release?” My response is: Are you freaking kidding me?!?! We as a community forget to ask for something in the first place, and then we ask you for it, and a month two later we might have it. And you think people are going to be mad about this?? Mad in love maybe! J
What next?
Join the conversation and help Microsoft decide. We have a Trello Board to help organize the community’s thoughts. Once we have a well-formed Idea we eventually submit a Connect item for it. We have been trying to hold off on submitting too many more Connect items while waited for this release of SSMS to come out. We’re hoping that with this crop of new cmdlets even more people will join the conversation now.
Right now based on the vote count on Trello, the next three cmdlets that we will ask for will probably be:
- Add-SqlDatabaseUser
- New-SqlLogin
- Get-SQlConfiguration (sp_configure)
In addition to the Trello board, we have also setup a Slack channel for people from the SQL Community to talk about PowerShell, SSMS, SSDT, and lots of other topics. Everyone is welcome to join. (just please accept the invite email right away so that Slack doesn’t think that Chrissy is spamming tons of people.)
Here are some of the cmdlets the community asked for:
CMDLET | Description |
|---|---|
Get-SqlAgent | Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server. |
Get-SqlAgentJob | Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent. |
Get-SqlAgentJobHistory | Returns the JobHistory present in the target instance of SQL Agent. |
Get-SqlAgentJobSchedule | Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job. |
Get-SqlAgentJobStep | Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job. |
Get-SqlAgentSchedule | Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent. |
Get-SqlErrorLog | Retrieves the SQL Server Logs. |
Set-SqlErrorLog | Sets or resets the maximum number of error log files before they are recycled. |
If you would like to learn more
I wanted to let you know that you can read more about these updates:
- To read the full announcement from Microsoft check out this post on the Data Platform Insider blog.
- To read more about the new SQL Agent cmdlets read this post from Chrissy LeMaire.
- To read more about the new Get-SqlErrorLog cmdlet read this post from Aaron Nelson.
- To read more about the update to Invoke-SqlCmd read this post from Laerte Junior.
Also, the Chrissy, Laerte, Rob Sewell, & I will be showing a lot of these new cmdlets in action during a session on Wed, Jul 06 2016 at 12-noon EDT (GMT-4) for the PowerShell VC PASS. You can register for that here and if you miss the session you can catch the recording on our YouTube channel http://sqlps.io/video.
Thanks for reading, I hope you enjoy the new cmdlets and I hope you’ll join the conversation as Microsoft continues to build more for us.
