PowerShell for the SQL Server DBA - Pipelines

By:   |   Updated: 2022-06-08   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > PowerShell


Problem

Moving forward with the PowerShell series, it is now time to talk about pipelines. As I mentioned in my previous article, likewise CMDLETs, I think that pipelines are one of those key things that make PowerShell special, and we'll see how they can be used to solve problems in a particular way.

Solution

In this article, we will cover the basics of pipelines in PowerShell and take a look at several examples to get a better grasp of them.

Initial considerations

  • A very basic knowledge of coding in any other programming language will be assumed.
  • No direct interaction with SQL Server will be covered in this article, that will be covered in a separate article.

Important note: I will be presenting all the examples using Windows, but that shouldn't be a showstopper for you to follow along in any other OS of your choosing.

Pipelines in PowerShell

Talking about pipes, not in the programming space but in the real world, a pipe is something used to carry stuff from point A all along the way to point B.

In computing, a pipeline is a set of data-processing elements connected serially, where the output of one element (pipe) is the input for the very next element within the pipeline.

In PowerShell, this concept is applied exactly like that, and it opens up a new door with many possibilities to craft a variety of solutions for a range of problems. As with all of the articles in the series so far, let's start learning about pipelines with example.

Simple Example

Let's see what happens when we take the output of the classic Get-Date cmdlet and send it to the Format-List cmdlet. By the way, the Format-List cmdlet formats the output of a command as a list of properties in which each property is displayed on a separate line.

Note: to separate each element of the pipeline you do it with the character "|" as follows:

Get-Date | Format-List
powershell commands and output

Using Multiple Pipes

Am I limited to just pipelining 2 elements? Let's find out.

Get-Date | Format-List | Out-File -FilePath .\Pipeline.txt

In the third pipe I've instructed PowerShell to grab the output of "Format-List" and send it to a file, and it works flawlessly!

powershell commands and output

I'm not sure if there's any limit in the amount of elements that the pipeline can have, at least I haven't read anything from Microsoft's official documentation or anywhere else, so I guess you can go wild there.

More Pipeline Examples

Let's take a look at more examples to see the things we can achieve with pipelines.

System Administrators often use PowerShell for their day-to-day tasks. A classic example would be the usage of the "Get-Service" cmdlet to retrieve the list of services available in the server, and the respective status (Running/Stopped) for each. Since the output that the Get-Service cmdlet returns is a bit large, sometimes SysAdmins just want to check a few items and move on to another thing.

Let's see how we can filter the list of services by using a pipeline.

powershell commands and output

If we use the "Where-Object" cmdlet, along with a specific property selector, we can filter only the service with a specific value, like this:

Get-Service | Where-Object -Property Status -eq Running
powershell commands and output

We can use a very similar approach if we want to check if there are services that match a certain string, like this.

Get-Service | Where-Object -Property DisplayName -match 'SQL*'

For this example, I want to see if there are services in my server that have anything to do with SQL.

powershell commands and output

An automation use case could be if you create a script that constantly checks if the SQL services are up and running, and attempt to start those in a Stopped status, which in theory should be up and running. This could be achieved by modifying a bit the previous pipeline, as follows:

Get-Service | Where-Object -Property DisplayName -match 'SQL*' | Where-Object -Property Status -eq Stopped | Start-Service

In a similar fashion, SysAdmins also use a lot the Get-Process cmdlet to query the list of processes being run in the system:

powershell commands and output
powershell commands and output

Pass Script Output Down the Pipeline

A valid question you might ask is: "can I pass the output of my custom script down the pipeline?". Let's find out.

I've created a script called pipe1.ps1 that defines an integer variable and returns its value times 2. Then, that output is passed down the pipeline and it displays the received value and the same value slashed by half.

powershell commands and output

Chain PowerShell Scripts with Pipelines

Another valid question might be: "can I chain my PowerShell scripts in the pipeline?". Let's find out.

Script pipe1.ps1 this time, for simplicity, will only return an integer.

As you can see, the trick for the script that will receive something from the pipeline is to set the ValueFromPipeline=$true in the attributes section of the "Param" block.

powershell commands and output

And if I change the value being returned by pipe1.ps1, the output will react accordingly:

powershell commands and output

Pretty cool if you ask me! Because this allows you to build complex solutions in a modular fashion. One use case that comes to my mind is having one script that returns a list of SQL Server instances and another script that grabs each instance and performs a connectivity test.

I'll definitely be having some fun trying to come up with cool SQL Server-specific examples in future articles to share with you.

Next Steps
  • In case you missed it, check out the previous article PowerShell for the DBA - CMDLETs and Advanced Functions
  • As I've mentioned several times throughout the series, make sure to adhere as much as possible to best practices. Be very descriptive with the naming of your scripts so that even chaining them through pipelines makes enough sense for you, and anyone within your team.
  • Use pipelines to leverage your creativity while designing solutions through scripting. By combining pipelines with what we have learned so far, you definitely can come up with very cool solutions.
  • In the next article we will be taking a look at how to perform DBA tasks using SQL Server CMDLETs and pipelines, so stay tuned!





get scripts

next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips


Article Last Updated: 2022-06-08

Comments For This Article




Monday, June 27, 2022 - 2:33:34 AM - Alejandro Cobar Back To Top (90211)
Thank you for your words Edul, they mean a lot to me!!!

Tuesday, June 14, 2022 - 8:26:39 AM - edul chikhliwala Back To Top (90159)
Very useful and informative series on Powershell scripting.
I love the examples which you provide to illustrate the concepts.
Please keep up the good work and thank you!














get free sql tips
agree to terms