The SQL Notebook Experience, Featuring Powershell

Rob Sewell takes a break from book-writing and talks about using Powershell in SQL Notebooks:

Yes, it’s funny but also it carries a serious warning. Without understanding what it is doing, please don’t enable PowerShell to be run in a SQL Notebook that someone sent you in an email or you find on a GitHub. In the same way as you don’t open the word document attachment which will get a thousand million trillion pounddollars into your bank account or run code you copy from the internet on production without understanding what it does, this could be a very dangerous thing to do.

With that warning out of the way, there are loads of really useful and fantastic use cases for this. SQL Notebooks make great run-books or incident response recorders and PowerShell is an obvious tool for this. (If only we could save the PowerShell output in a SQL Notebook, this would be even better)

“It’s a bit hacky” is a generous statement, but it’s really cool that Rob figured out a way to do this. There is a Powershell kernel for Jupyter, but I’ve not had the best experience adding new kernels to Azure Data Studio (at least not F#’s kernel, which I tried).

Multi-Server Patching with dbatools

Eric Cobb shows how you can use dbatools to upgrade multiple SQL Server instances:

Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you’re going to be spending a considerable chunk of time on it.

Thankfully, the dbatool Powershell Module makes this process much easier! In fact, once you get it figured out, using Update-DbaInstance to patch your SQL Servers becomes very simple. But, getting it figured out can be a little confusing, so I’ve outlined the steps below that I use. The biggest hangups I had were setting up a central patching location and using a credential to access it.

Click through to see an example of this in action.

Powershell Modules Everywhere

Kevin Chant has some thoughts on whether you should have SQL Server Powershell modules on every server:

Recently I’ve seen recommendations about putting PowerShell modules on every SQL Server. I must admit it has got me thinking if this is indeed worthwhile.

In addition, it makes me wonder if it’s actually better to put the Powershell modules on a select number of management servers instead?

If you are wondering which modules I could be talking about, I mention some in a previous post which you can read in detail here.

Read on for Kevin’s thoughts on the matter.

Determining Your OS in Powershell

Patrick Gruenauer shows how you can determine your operating system in Powershell Core 6 and Powershell 7:

Recently, I discovered three PowerShell Core variables that could be very helpful when it comes to determining the operating system in PowerShell. PowerShell has become a cross-platfrom tool and can be installed on Linux or MacOs, too. So it could happen that you have to determine the operating system first before starting any other actions.

Click through to see what they are and how to use them.

Troubleshooting DSC in Push Refresh Mode

Jess Pomfret continues a series on Powershell Desired State Configuration:

One of the biggest obstacles people face when using DSC is the troubleshooting and reporting pieces. There are options here to integrate with third party tools to create a more polished enterprise solution, but if you’re going with just straight DSC you might feel it is lacking some in this area.

We do however have several tools available to troubleshoot issues with configurations or to monitor our nodes to determine whether they are still in the desired state. I’m specifically going to look at the options available if you’re using DSC in the Push refresh mode.

Click through for discussion of the PSDesiredStateConfiguration module as well as DSC-related event log messages.

Powershell and Windows Terminal Profiles

Jeffery Hicks shows how you can modify your Windows Terminal profile using Powershell:

I recently updated my Windows 10 systems to the 1903 release. One of the reasons is that I wanted to try out the new Windows Terminal preview. You can find it in the Windows Store. This is bleeding edge stuff and far from complete but promises to a great addition. Now you will be able to have all your command terminals, in one tabbed application and easily be able to switch between them. As I said, this is far from being a finished and polished product. Right now, if you want to add a new profile, that is another terminal, you have to manually edit a json file. If you have VS Code installed, the file will open in that.  Otherwise, I’m assuming you’ll get whatever application is associated with the .json extension.

Read on for a Powershell one-liner which lets you create a terminal profile.

Using Show-Command in Powershell

Kenneth Fisher walks through the Show-Command cmdlet in Powershell:

Years ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that will help me learn to script. At least in a very basic way. For example, if I want to see what parameters are actually available for Get-Help and maybe script out a template to work with then I can do this:

Show-Command Get-Help

Read on to see how you can put this into action.

dbatools and Linux

Chrissy LeMaire takes us through dbatools support on Linux:

As a long-time Linux user and open-source advocate, I was beyond excited when PowerShell and SQL Server came to Linux.

A few of the decisions I made about dbatools were actually inspired by Linux. For instance, when dbatools was initially released, it was GNU GPL licensed, which is the same license as the Linux kernel (we’ve since re-licensed under the more permissive MIT). In addition, dbatools’ all-lower-case naming convention was also inspired by Linux, as most commands executed within Linux are in lower-case and a number of projects use the lower-case naming convention as well.

Considering how many OS-specific operations there are, the percentage of Powershell commands in dbatools which work is excellent.

Pester and Testable Powershell Scripts

Shane O’Neill has a public service announcement:

Now scripts are notoriously hard to test, I’ve written about how I’ve done that before but, honestly, if you really want to know then you need to check out Jakub Jares ( blog | twitter ).

Knowing how difficult testing scripts are, the first thing I decided to do was take the functions in the script and split them out. This way they can be abstracted away and tested safely.

I also didn’t want to take on too much at one time so I choose a random function, GetPreviousTag, and only actioned that one first.

Shane also found a bug in the first script, underscoring the importance of good tests.

RegEx Capture Names in Powershell

Jeffery Hicks shows how you can use regular expressions to create capture names in Powershell:

Once you have matching patterns, you can define them with a name. The general layout looks like this:


The parentheses are key. My datetime pattern can be defined as a named capture:


Click through to see how you can combine these and turn them into Powershell objects.


July 2019
« Jun