After that success, we had the chance to do a Q & A with Ken Van Hyning ( b | t ) Engineering Manager for SQL Server Client Tools at Microsoft.

Q & A

[an & cl] Thank you so much for taking the time to answer our questions! As the “SQL Tools Guy”, you are an essential part of our experience with SQL Server. Can you tell us a little about yourself?

[kenvh] I am the Engineering Manager for SQL Client Tools.  The SQL Client Tools covers SQL Server Management Studio, SQLPS, SQL Agent, SQL Profiler and a few other management tools.  I have been in the SQL Server organization since 2007.  During that time, I have worked predominately on the tools or Azure SQL Database service.  In the fall of 2015 I came back to lead a renewed effort in our SQL management tools.  We have been rebuilding the team and have focused a lot of effort in the last year to enable the tools to ship monthly updates independent of SQL Server itself. This is the key foundational work for us to be able to deliver a much higher volume of improvements across all our tools including SQLPS.

[cl] I know the community is eager to hear more about the new developments with SQLPS. They were quite unexpected.

[kenvh] I must say, it was a lot of fun sending you the tweet that the fixes made it into our March Refresh and then seeing your response!  There is a bit of a backstory here folks might find interesting.  A few years back the SQL organization needed many of the engineers in the team to go help on Azure SQL Database.  This included many of us from the tools team which had obvious impact on our ability to do anything significant in the SQL Client Tools space as a whole.  We have been reinvesting in the tools again and I get a chance to be a part of leading our SQL Client Tools Renaissance we have begun.

Although this has been unfortunate for our tooling investments, I am personally very excited cultural changes the last few years have had across the SQL organization.  We now have a much more hands-on, data-driven, and continual improvement mentality.  We are bringing that to the tools team and I expect folks will be very happy with the improvements they see across the SQL Client Tools month over month moving forward.

[cl] When it comes to administration, many server platforms are going "PowerShell first", then GUI. Is this the goal SQL Server as well?

[kenvh] This is an area that we need to focus more on in SQL Server in how we approach manageability.  Today, we are still not PowerShell first; we are PowerShell also.  In some of our recent investments for SQL 2016 we have started making the right architectural changes to get our APIs cleanly separated from our UI.  Historically, too much of our business logic was embedded within the WinForms or WPF code within SSMS.  That has made it difficult to get parity between SQLPS and SSMS.  I see a great opportunity for us to move to a PowerShell first model and base our early testing on our CMDLETs.

[an] Nearly every action inside of SSMS that allows you to "Script Action to New Query Window" should also have an option to "Script Action to PowerShell".  Something like this will greatly help the people getting started with PowerShell to find a task in SQL Server they want to automate and have a ready-made example of how to script that in PowerShell.  Will this type of functionality be added soon?  If so, hopefully a lot of those Actions eventually become new cmdlets or functions in the SQLPS module, and not just SMO code.

[kenvh] This is an interesting idea.  Today we don’t have the capability to do this.  Our management stack, predominately SMO/SFC knows how to script out T-SQL.  Our PowerShell CMDLETs are mostly wrappers on top of our management stack but the stack below these wrappers has no knowledge of PowerShell.

[an] Will this type of functionality be added soon?

[kenvh] I don’t have anything concrete in this area, but I will put this on our radar to discuss on the core API side and how we might integrate PS scripting in addition to T-SQL.  Candidly though, even if we enabled this there would be another large project to retrofit the hundreds of dialogs in SSMS to support PS scripting.  A community engagement vector?

[an] What level of integration can we expect for using PowerShell within SSMS? Will we be able to edit our PowerShell scripts (using some kind of rich editor) inside of SSMS? How soon would this type of integration be available?

[kenvh] I really haven’t investigated this avenue much.  We wouldn’t want to invest in building our own PS Editor, per se, so I would be looking for a PowerShell editor that could be hosted within SSMS.  As SSMS is built on the Visual Studio 2015 Isolated Shell, one opportunity might be to see if it is possible to enable Adam Driscoll’s PowerShell Tools for VS 2015 within SSMS.  We haven’t investigated that though, so I can’t speak to the viability at this point.  We have run into numerous VS packages that aren’t included in the isolated shell, so there may be some challenges.  I’d love to get a gauge on the demand for this within SSMS. 

[an] Regarding existing PowerShell efforts for SQL Server, have you looked at some of the community projects for working with SQL Server in PowerShell?  Projects like SQLPSX, SQLSPADE, & dbatools, just to name three.  If so, any chance you'll be looking to replicate that functionality?

[kenvh] These are all great examples of the pent-up demand for SQL PowerShell CMDLETs.  I think the model that makes the most sense is to find a way to leverage the power of the community and work like this more officially within SQLPS itself.  Although I have a dedicated engineer to SQLPS now and we have a partnership model across the SQL organization to contribute features we would be remiss to not find a way to leverage the skills and passion of the community.

[cl] So what does SQLPS the roadmap look like? How long before we reach 700+ cmdlets like Lync, Exchange and SharePoint?

[kenvh] Strategically, we have only had one toe in the water regarding PowerShell for many years.  That amounts to a pretty big missed opportunity on our part.  As we embark on this SQL Client Tools Renaissance, I see elevating PowerShell for SQL Server as a great way for us to deliver significantly increased management capabilities for a very modest investment engineering wise.  SSMS was never designed to provide at-scale manageability.  We have begun these investments now.  Some of the initial priorities are to get some of the long standing requests addressed first.  We then will be focusing on making an “app local” version of SQLPS, so that there is no SxS issues when installed on a machine with SQL Server installed.  We also need to invest in better telemetry, reliability and the SQLPS redistribution. I can’t share exact release dates, but with our new monthly tools updates, we will be having incremental progress each month.

One of the pragmatic realities is the vast amount of code that is built into SQL Server Management Studio that isn’t easily usable by SQLPS.  Though I would like to be able to get complete parity for SQLPS with the SSMS UI, that would be a massive undertaking that would take many months.  We will instead focus on a customer and data-centered approach to building out the set of CMDLETs.  So yes, it is fair to say the number will grow and I hope that we will be able to get the CMDLETs most in demand out fairly quickly.  And of course, if we shift to a PowerShell first model then we don’t create more debt.

[cl] Right now, SQLPS primarily focuses on Availability Groups. What's your next functionality focus, what is the timeline, and how did you determine what to prioritize? Is there anything the community can do to help?

[kenvh] One big area is Always Encrypted.  That should be coming out around the time SQL 2016 RTMs.  As an aside, I would encourage folks to create or UpVote Connect items as suggestions of the CMDLETs they would like to see most!  That really helps us make community driven decisions.

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. 

Connect is great for qualitative data but we are working now towards making all the tools have quantitative signals as well.  In SSMS, we now get basic usage and reliability telemetry.  This enables us to get visibility into which features are used most heavily, where common errors are happening, or conversely, which features are used very little (all of the data is anonymized and we go through strict compliance standards.)  I would like to see us enable that same quantitative approach for SQLPS and enable us to be much more proactive on where to focus our engineering efforts.

[cl] Speaking of Connect Items!

• Invoke-SQLcmd should be able to return results as an Object Type other than just System.Array
• SQLPackage.exe - Needs to be made into at least 3 cmdlets
• Essential Job support is missing from the SQLPS module
• Basic Login management functionality is missing from SQLPS

 [cl] What are your plans for older versions of SQLPS? Will they be updated in the CUs?

[kenvh] Candidly, no plans for the older versions.  That’s because we have changed the nature of the SQL Client Tools all together.  They are no longer tied to a particular version of SQL Server.  They are now independent of the SQL Server version and are an independently released download.

 [cl] PowerShell does not appear to be heavily embraced by the SQL Server community (although does have a strong following with those who have taken the plunge). How do you plan to integrate the two communities? Will PowerShell be something that is marketed to the SQL Server community?

[kenvh] I expect that there will always be a bit of a VENN diagram when it comes to the intersection of roles, skills and tools for managing SQL Server.  If we do this right though, this shouldn’t be an orthogonal set of investments.  There will be some scenarios where PowerShell shines, there will be some scenarios where T-SQL within an editor shines, and of course, there is a place for GUI wizards, dashboards and reports.   The key for us is to deliver these capabilities with proper architectural layering, so that we can build various experiences on a set of common APIs.  As we do that, I expect we will see a much better pickup of SQLPS within the SQL Server community.

[cl] Do you have any plans to reexamine older SQL Tools Connect bugs (for sqlcmd, SQLPS, etc) that are still marked as unresolved/active?

[kenvh] As you say, these tools have been around a while and we have bugs and suggestions that have been submitted going back over 10 years.  I can say for certain the team lacks now amount of input on things that could be fixed or improved.  It goes back to the prioritization question earlier.  This is one of the signals on what we could do, but we would prioritize newer, high upvote count items first.  As we engage better with the community and get the engineering team in direct contact with folks like yourselves I’m sure we will get great input on what would have the most impact!

 [an & cl] This is a huge one. Right now, the Connect Item requesting that Microsoft open source SQLPS has nearly 200 upvotes. Do you plan to open source SQLPS and if so, what is the timeline?

[kenvh] This is a very intriguing prospect.  We don’t have anything specific to announce but there are some other projects at Microsoft that have gone this direction.  If we do decide to do this, I’ll be very happy to share that straight away!

[cl] The PowerShell team has placed its documentation on GitHub, and mirrors it to the official documentation on Microsoft's site. This allows the community to contribute to documentation and examples. Do you plan to do the same for SQLPS?

[kenvh] This is a little outside my wheelhouse but seems like a great idea.  I will ask our documentation partners and see if they have plans in this area and use this as example for them to check out.

[an] Even though with SSMS being a separate install now, the SQLPS module is still going to be installed on the server itself even if they don’t choose to install SSMS, right?  That way you could for basic management of your databases in an emergency situation.  (If not, will that be installed via feature pack like before?)

In that scenario where you have the SQLPS module on a server and no SSMS, will you be able to stay up to date with the latest cmdlets by updating the module somehow?  Sort of how you can Update-Help in PowerShell these days?

SQLPS will still be shipped with the server as part of SQL Agent, so yes, it is there even without the tools install.  We are planning on making a change in the June update to finish the separation of the tools SQL PowerShell (including what is in the powershelltools.msi package) to use an app local version of the tools. 

SQL Server 2016 will continue to use the version of SQLPS that shipped in the box.  Our principle is that tools installs really shouldn’t stop on server binaries.  Unfortunately, that wasn’t a principle over the last many years as the tools were a feature of the engine setup.  We are breaking that pattern so that we can ship regular updates of the tools and have them be “universal” in the versioning (i.e. not tied to SQL 16 and its servicing CU/SP constraints). 

This does have the implication though, that the SQLPS on the engine will not change via the monthly updates.  It will only be updated via the engine’s servicing model.  Depending on demand, we can push new PowerShell updates in the SQL Server 2016 Service Packs.  I don’t know how many folks use the SQLPS as an agent task though and if it is important to get that as regular update so let me know what you think on that front. 

[cl] Speaking of tools, we fired up Start PowerShell from within SSMS 2016 March Refresh and noticed a new process called sqltoolsps.exe. Can you tell us a little more about that?

[kenvh] Ah, yes, this is part of the foundation work the team has been working on for the better part of the last year.  You can read about that effort in my blog post. 

One of the goals we are working on for the SQL Client Tools is to make them “app local” and have no shared dependencies with the SQL Server engines.  We got a fair number of the components set up to do this but the entire dependency chain for the tools didn’t get done in time for SQL Server 2016 RTM.  SQLTOOLSPS.EXE is part of that effort. 

Since SQLPS.EXE ships with the engine we will soon move the tools to leverage non-GAC’d versions of the management stack.  We will also update the PowerShellTools.msi to uses these non-GAC’d versions of assemblies.  Correspondingly, the tools will switch to using SQLTOOLSPS.EXE.  I hope to have this out sometime this summer and we will blog about it and work with our PowerShell MVPs to help us make this transition.

[an] Finally, how about something awesome in PowerShell that would be really nice to have in SQL Server: Have you played with Out-GridView (OGV) much?  Among other things, OGV allows you to pipe a result set to it, and then sort/filter the result set without having to re-query or anything. Can we get that kind of functionality built into returning the results of queries To Grid in regular ol' SSMS? 🙂

[kenvh] These are all great examples of the pent-up demand for SQL PowerShell CMDLETs.  I think the model that makes the most sense is to find a way to leverage the power of the community and work like this more officially within SQLPS itself.  Although I have a dedicated engineer to SQLPS now and we have a partnership model across the SQL organization to contribute features we would be remiss to not find a way to leverage the skills and passion of the community.  As to the “To Grid” feature in SSMS… this sounds like a great idea for an SSMS extension.  If someone is interested in collaborating on this, I could hook them up with one of our SSMS experts and see if we could get an SSMS extension built to support this.  Though we may want to start with the core PS editor idea. J

[an & cl] Well, thanks again for taking the time to answer our questions. I know the community will have additional questions for you. Would the SQL Tools team be willing to do an AMA, similar to the one held earlier on Reddit? If so, we can let people know on the post to keep an eye out.

[kenvh] Absolutely. Let’s shoot for something in mid to end of May.

Also, I want to thank you for your passion and rallying the community to draw attention to the desire to see all the improvements in SQL PowerShell.  I look forward to getting some great improvements and a much higher bandwidth engagement with the community in the months ahead.

[an & cl] Our pleasure! We look forward to speaking again with you in May.

To the reader, we hope you’ve enjoyed this interview! Considering Ken’s response, we setup a Trello board “PowerShell & SQL Client Tools (SQLPS & SSMS)” to keep track of suggestions for SQLPS and PowerShell support in SSMS. Please come upvote your desired cmdlets and priorities!