Azuredatastudio: Add PowerShell Notebooks

[SQLvariant]

Issue Type: Feature Request

Please add support for PowerShell Notebooks. This will be extremely useful for resetting demo environments, or just setting up a demo to work. As just one example, a PowerShell notebook could be used in a training scenario to guide the user to download the correct version of the AdventureWorks database:

$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak"
$BakFile = "$($Home)\Downloads\AdventureWorks2016.bak"
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile

Azure Data Studio version: azuredatastudio 1.5.1 (6a500715a7f2a9c5466d66b3cc312308bf59846d, 2019-03-16T01:14:53.392Z)
OS version: Windows_NT x64 10.0.17763

[potatoqualitee]

yes please

[sanderstad]

Oh yes I need this badly. It will make our lives so much easier

[clumnah]

This would be extremely helpful in having a Notebook that can do both T-SQL and Powershell for Database Migration purposes.

[shaneis]

This would also be boundlessly helpful with troubleshooting, documentation, automation, and delegation.

[irwins]

Supporting PowerShell will make creating interaction very useful!

[stevenjudd]

Another way to PowerShell all the things. Yes please.

[garyhampson]

Add my voice to the chorus shouting for this to be implemented. Can see my team moving a significant portion of our Production Runbooks to Notebooks from being hosted in Confluence.

[TylerLeonhardt]

@kburtram, do you think you can explain the work involved for supporting this? Maybe a community member will pick it up before you folks can get to it.

[kburtram]

@chlafreniere do you know at a high-level what would be involved to support this scenario?

[jpomfret]

This would be great!

[TylerLeonhardt]

ping @chlafreniere

[jcbarr]

I'm all for this!

[snordog]

I am all for this as well!

[potatoqualitee]

checking every day for updates 😄

[kburtram]

I moved into our Planning milestone so we can discuss in our team triage next week. I don't think this would be too difficult since I believe there is already support for running bash commands, though we'd need @chlafreniere or @kevcunnane to confirm since they own the Notebook feature area.

[chlafreniere]

Love all of the interest here 😄.

At a high level, to bring PowerShell support to the SQL kernel, another magic would have to be created (similar to %%lang_python and %%lang_r that @kevcunnane started) that, under the covers calls into the VS Code terminal APIs (as this already has logic to determine if PowerShell is installed, which we'd want to leverage instead of re-inventing the wheel again!)

Some unknowns that require investigation:

• If PowerShell isn't on a user's machine, what does the VS Code API return to us?
• Can more than one instance of PowerShell exist on a machine? In that case, how should we handle that through the terminal APIs?
• This approach will still mean that variables cannot be shared between cells. Would tie into a larger work item that we have already around how to accomplish this for SQL (maybe a first step is releasing this without variable/context sharing?)

Note that it appears that a community-driven PowerShell kernel exists: https://github.com/vors/jupyter-powershell. This doesn't solve the SQL + PowerShell in a single notebook scenario, but may be something that some folks can play around with 😄.

[fMichaleczek]

@jaykul

[TylerLeonhardt]

@chlafreniere Does Azure Data Studio support someway to swap out the Jupyter kernel? That might be a decent first step. Allow swapping to the pre-existing PowerShell kernel (P.S. AFAIK, this is the most robust at the moment https://github.com/jaykul/jupyter-powershell 😃).

That said, adding PowerShell to the SQL kernel would also work (and be the ideal situation) but it seems like a larger investment? FWIW, PowerShell does has an SDK that might be useful here... or PowerShell Standard which provides a consistent API between Windows PowerShell and PowerShell Core. Please don't hesitate to reach out to our team for assistance! Also there are some incredible community members on this thread as well.

[chlafreniere]

When we startup the notebook, we ask Jupyter for a list of kernels that are installed (and add SQL to the list). I haven't tried the Jupyter-PowerShell kernel myself, but if you were to install it on your machine (looks like they give a couple of options on that page for installing locally), it should "just work" and show up as a kernel in the dropdown in ADS.

Invoking powershell from the SQL kernel definitely requires some investment on our part, but it's on our roadmap (in addition to bash). Sounds like a great idea to collaborate with your team on the right approach, @TylerLeonhardt, once we have a proof-of-concept up and running (mainly, getting the "magic" working where we're invoking another process correctly and plumbing through stdin/stdout). We're super heads down on perf/stability improvements right this second, but the energy on this thread is fantastic, and we totally hear you.