PowerShell for the DBA - CMDLETs and Advanced Functions

By:   |   Updated: 2022-05-24   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > PowerShell


Moving forward with the PowerShell tutorial series, it is now time for me to present CMDLETs (pronounced as commandlet) and advanced functions. Personally, I think that CMDLETs are one of the key things that make PowerShell special, and we'll see in this tutorial how they can be used to solve problems and do cool stuff!


In this article, we will learn the basics of a cmdlet and take a look at examples to get a better grasp as well as a quick look at advanced functions.

Initial considerations

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

Important note: I will be presenting all the examples using Windows, but these should work in any OS of your choosing where you can run PowerShell.

PowerShell cmdlet

Let's take a look at what Microsoft's official documentation states about cmdlets:

"A cmdlet is a lightweight command that is used in the PowerShell environment. The PowerShell runtime invokes these cmdlets within the context of automation scripts that are provided at the command line. The PowerShell runtime also invokes them programmatically through PowerShell APIs."
"Cmdlets perform an action and typically return a Microsoft .NET object to the next command in the pipeline. A cmdlet is a single command that participates in the pipeline semantics of PowerShell. This includes binary (C#) cmdlets, advanced script functions, CDXML, and Workflows."

With that said, let's take a look at some examples to see cmdlets in action right away.

Simple cmdlet Example

Let's use a very simple cmdlet "Get-Date", and see what we get:

powershell commands

A few takeaways from this very simple execution:

  • I've written the cmdlet with upper and lower case letters on purpose, just to demonstrate right away that they are not case sensitive (which isn't a surprise at this point because that's how it is in PowerShell across the board).
  • You just write the cmdlet in the console, hit enter, and you're done. Of course, there are cmdlets that can take parameters, but we'll get to that right in a moment.
  • The Get-Date cmdlet simply returns the current date and time of the host where it's being executed, nothing fancy there.

Cmdlet with Parameters Example

How can I specify parameters for a cmdlet? By specifying the -DisplayHint parameter and passing the value "Date" you'll get only the date part from the same output we got in the previous example.

powershell commands

Pretty straight forward right? Now, what if I want to store the output of a cmdlet and use it in my script, is it possible? Let's find out:

powershell ise

This particular example is quite interesting because there are several things going on:

  1. I'm storing the output of the Get-Date cmdlet in the variables $startDate and $endDate, respectively.
  2. I'm using the Start-Sleep cmdlet to introduce a 5 second delay in the execution of my script so that I'm sure that $startDate and $endDate are exactly 5 seconds apart.
  3. By using the New-Timespan cmdlet and passing the $startDate and $endDate as parameters, I get information on how apart are those two dates, in terms of time.

So with that super-simple script, we learned that the output of a cmdlet can be stored in a variable, and that by throwing other cmdlets into the mix, you can create unique logic flows that will allow you to tackle the problem you have in front of you. By the way, if you want to know what cmdlets are available for you to use, simply run the "Get-Command" cmdlet and it will list all the ones available at your disposal; some will be there out-of-the-box, and some will be made available under certain conditions (e.g. by installing a certain module or by developing a function) which we will explore in a moment.

powershell commands

Ways to get CMDLETs at your disposal

There are several ways to get cmdlets.

1- cmdlets for SQL Server

Since this series of PowerShell articles are meant as a guide for the SQL Server DBA, let me address this by providing an example specifically for SQL Server. There are two modules for SQL Server, provided by Microsoft:

  • SqlServer: this includes a set of cmdlets that allow you to perform several actions on your SQL Server instance or in a specific database. You can take a look at the available list of cmdlets on the Microsoft website.
  • SQLPS: this is the module used by SQL Agent to run PowerShell commands in job steps. Here's the list of cmdlets available on Microsoft's website.

Important note: there will be cmdlets that won't work for all available flavors of SQL Server. For instance, you won't be able to use the "Stop-SqlInstance" cmdlet against a SQL managed instance.

Here's how you can install these modules: (note: make sure that you open the PowerShell console with admin rights or else it will fail).

powershell commands
powershell commands

After the installation, you can confirm that it succeeded by typing "Get-Module SqlServer -ListAvailable".

powershell commands

2 - Develop and Deploy a cmdlet

You can develop a cmdlet in C# or any other .NET language, and then you import it into your PowerShell session. I'm not going to expand on this particular section as it is not the main intent of the series. Regardless of that, I'd still like to leave you with some guidelines provided by Microsoft if you'd like to venture into the development of cmdlets.

3 - Advanced Functions

The third option leads me to the other topic for this article, advanced functions. Yes, you can create functions in PowerShell that work exactly the same as cmdlets. But how are advanced functions different than the cmdlets from point 2? When you develop a cmdlet in a .NET language, it is compiled and a .dll is generated for you to import and use. With advanced functions, you skip the compilation part and develop a function that you can use right away within your scripts.

If you ask me which one I think is better, I'd say both have their own set of characteristics and it would be up to you to pick the one that you think suits better for what you are trying to accomplish.

A couple of key points to mention:

  • Compiled cmdlets probably will perform better in the long run (I guess).
  • Compiled cmdlets will not expose the code that was used to develop them, as opposed to the advanced functions which can expose the code by opening the script where it was created.

How can I create a PowerShell advanced function?

Let's take a look at a very simple example to get a feeling of what one looks like.

In my previous article, I talked about basic functions in PowerShell, so this looks pretty much the same. The main difference would be the "[CmdletBinding()]" attribute and the "Process" block.

The CmdletBinding attribute is similar to the CMDLET attribute that is used in compiled cmdlet classes, to identify the class as a cmdlet.

The process block is basically equivalent to the ProcessingRecord method that is used by compiled cmdlets to process the data that is passed to the cmdlet.

windows powershell ise

By creating advanced functions, PowerShell makes available a new set of properties that you can use for your function. You can get the detailed list on Microsoft's website if you'd like to learn more.

Next Steps
  • In case you missed it, check out the previous article PowerShell for the SQL Server DBA – Basic Functions.
  • Cmdlets are incredibly helpful in the development of scripts, so make sure you take advantage of them to craft your solutions.
  • When developing your own cmdlet, make sure to follow as many best practices as you can.
  • When building a custom proprietary logic through a cmdlet, make sure to use the compiled approach, to protect your intellectual property.
  • In the next article we will be taking a look at pipelines, so stay tuned!

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-05-24

Comments For This Article

Tuesday, May 24, 2022 - 9:59:37 AM - Alejandro Cobar Back To Top (90106)
Thanks for pointing this Jay, I will take it into consideration for my future articles.

Tuesday, May 24, 2022 - 8:45:37 AM - Jay Butler Back To Top (90103)
Good column. I know that this is a dip your toe in the pool type of intro. But, it is worth pointing out that MS is no longer developing Windows PowerShell (v5.1) and the PowerShell ISE. While PS 5.1 will be around for a few more years, it is probably good to start targeting the multiplatform PowerShell 7.x. I have moved all of my PS development into Visual Studio Code. The base product is far more powerful than the ISE and there are plenty of VS Code extensions that can make you even more efficient.

get free sql tips
agree to terms