My First Steps in PowerShell

I (finally) got round to spending a few minutes playing with PowerShell today.  It’s been on my “To Do” list for about 18 months now, which is rather embarrassing.  Still.  First steps, and all that.

My laptop was running slowly, and I wanted to stop all SQL Server services (I don’t use them very often these days – only for helping out on AskSSC). I couldn’t face running through the Services control panel killing them one by one, so I started poking around in PowerShell.

Get-Service

The Get-Service cmdlet seemed to be the ideal place to start. This returns a list of all services, by default providing their Status (Running, Stopped, etc), internal name, and DisplayName (the name you see in the Services list that you get when you do Start->Run->Services.msc). A useful starting point, but my laptop has way more than just SQL Server services on it (as does every Windows PC).

Where-Object

So we want to filter that list to only show SQL-related services. This is where the Where-Object comes in. This takes the output from the previous commandlet, and removes items that don’t match the parameters you provide. In my example, I’m doing:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*"}

Note that the wildcard used by “like” is the *, not the % to which I, a long-time SQLer, am accustomed.

Now, we want to pass that to a service-killer routine. But there’s no point in getting it to kill services that aren’t running, so we first need to add another filter:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and $_.status -eq "Running"}

Yes, indeed. Boolean and/or/not/equal operators are all there, but disguised. Use -eq rather than =, for example.

So, we have a list of running SQL-related services. Where’s that killer?

Stop-Service

The Stop-Service cmdlet does what it says on the tin. Given that we’re passing in a list of Services to be stopped, there’s no need for us to do anything more than:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and $_.status -eq "Running"} |
         Stop-Service

Yay!

Caveats

OK, the script as provided above does have a couple of minor issues, the worst of which is that it has no concept of the service dependencies – it’ll try to stop the SQL Agent service before stopping the SQL Server service, for example. I’ll work on this for v2. Error handling is also missing… I suppose I could alter the script to first kill any service that’s like “*SQL*” but the internal name isn’t like “MSSQL$*”:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and -not($_.name -like "MSSQL$*")}

Hmm. Is it possible to use the Sort-Object script to do this? Y’know, like you would with SQL Server:

...
ORDER BY CASE WHEN Name LIKE 'MSSQL$%' THEN 1 ELSE 0 END, DisplayName

$_.

What’s this $_. bit? That’s a short-hand. The $_ represents the current object being worked on by the command, and the . is an indicator to get the named property of that object.

It might help to remember that this is an object-oriented environment – cmdlets / commandlets / commands act on objects (or sets of objects), and so each object has properties associated with it (“members” in PowerShell-ese).

Get-Member

Last bit for today Get-Member cmdlet, which returns a list of properties and methods associated with the object piped into it. For example, for a service, we could do:

Get-Service "SQL Server Browser" |
         Get-Member

Disclaimer

Please note, this is my first time playing with PowerShell. I’m sure there are better ways of doing all of this, including using a more sensible development environment than just the command line (launch a command window, and then run “PowerShell” within it… The “PS” prompt tells you that you’re not in Kansas any more…)