I’m opening up a can of worms with this topic. PowerShell usage is a heavily debated topic in the SQL Server community. This is similar to the classic “Tabs or Spaces” as an indentation style debate, there’s no clear winner. Both sides love to argue their preference. We all know spaces win. Or do tabs win?
I’m a DBA, why should I learn PowerShell?
That depends. Is there anything in your current workload that you wish you didn’t need to manually perform? Without using SQL Server Management Studio (SSMS) or remoting to a server, wouldn’t it be nice to see if the SQL Server Agent is still running after server patches completed? Sure, there are numerous tools available to help you perform these tasks in other ways. Unfortunately, these tools usually aren’t free.
So, what is PowerShell, anyway?
PowerShell is a command-line interface built on .NET. It’s native to the Windows OS, but recently in 2016, it was announced it would become open-source and cross-platform. It was initially developed as a Command-line interface to automate more tasks than were otherwise available in Command Prompt.
It uses “cmdlets” to implement functions following a Verb-Noun naming pattern. The verb specifies the action to perform and the noun specifies which resource or object the action will be performed against. Cmdlets are associated with classes through assemblies. If the “System” assemblies don’t offer what you need, you can load additional classes using the “Import-Module” cmdlet.
Many people familiar with Windows use File Explorer or Command Prompt to navigate or identify the contents of a directory. We can perform this same task in PowerShell using the “Get-ChildItem” cmdlet.
I was able to accomplish this in PowerShell to view the contents of a specific directory with the “-Path” parameter, looking in my “C:\Temp” folder. Typing “Get-ChildItem” may seem like a bit much to type for something as simple as this task. In Command Prompt this is equivalent to using the “dir” command. Fortunately, PowerShell offers aliases that work synonymously with the full cmdlet.
We can identify aliases for cmdlets using the “Get-Alias” cmdlet. Rather than typing the full cmdlet, we can simply type: “dir”, “gci”, or “ls” to accomplish the same goal. This means that I can use any of those aliases to investigate my “C:\Temp” folder.
What about SQL Server?
The most popular modules for SQL Server include: SqlServer and SQLPS. Another popular, open-source module I’m a fan of is dbatools. This free module currently includes over 500 commands.
If you use Adam Mechanic’s popular sp_WhoIsActive stored procedure in your environment, dbatools has a cmdlet to streamline the updating and installation of this procedure. Here’s how I can use it to create this procedure on my local database instance, all without browsing the web to download it or opening SSMS to execute the CREATE statements.
Install-DbaWhoIsActive -SqlInstance localhost -Database master
Installing or updating it on one instance is quick and painless. This task can be quite daunting if you need to install it on multiple instances. Fortunately, this cmdlet works with multiple instances as well. If you’re using a Central Management Server (CMS) you can use the Get-DbaRegServer cmdlet to get the names of these instances. If you want to specify their names manually you can use a command like this:
Install-DbaWhoIsActive -SqlInstance @("localhost", "notlocalhost") -Database master
Sample Usage. Again.
Maybe installing these third-party scripts aren’t a pain point for you. Raise your hand if you’ve ever seen this before.
Sometimes we need to remove logins from server instances. This a common task associated with Database Administration. If there are still users associated with these deleted logins, we’ll be left with what’s known as “orphaned users.” We can use another dbatools cmdlet, Get-DbaDbOrphanUser, to identify these orphaned users.
This shows me that I have two users in my “Projects” database without associated logins. This means it’s time to start asking questions.
You may have noticed that “|” character in there, this is known as a “pipeline.” Pipelines work to connect commands together. In this instance, using “Format-Table” prettifies our output.
This was a very high-level overview of PowerShell and how it can be utilized in your environment. You can accomplish some powerful (no pun intended) automation tasks using this tool.
I also see the downsides to using PowerShell. Your machine may not have the same version or assemblies installed as another machine. The version of Powershell you have installed might not be high enough for certain cmdlets. Finally, this might be something brand new to you. Learning something new takes time. At the very least, I encourage you to try it.
Thanks for reading!