Services and Sorts in PowerShell

Two weeks after my first steps in PowerShell, I got the time to have another play, and have (I think) answered some of the questions I asked myself in that earlier article.

As you recall (or have just read), I was playing around with a PS script to stop all SQL Server Services, and had worked out the following:

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

Given the way that the services are on this laptop, though, there are issues with that command:

Stop_Service Script errors

In other words, it’s trying to stop services that have dependent services still running. Now, there’s a couple of ways we can deal with this:

Service Dependencies

There are various posts out there talking about Service Dependencies in PowerShell, and I really don’t feel qualified to add anything (or, indeed, to tackle the problem head-on at the moment… I’m blogging / learning in real time here!)

To identify the dependencies of a single service, though, is relatively straightforward:

Get-Service "SQL Server (SQL2012Dev)" -dependentservices

That little snippet returns a collection of services that depend upon the SQL Server (SQL2012Dev) service. Nice, short list – it’s the SQLAgent service for that instance.

So, in theory, we should be able to manipulate those lists of dependent services into an appropriate order for shutting down the remaining services. That’s a bit of a scary step, so I’ll shelve that approach for now.

Sort-Object

In my previous blogpost on PowerShell, I asked the question if it would be possible to use the Sort-Object to write a more complex sort operator. And, indeed, it is.

My requirement was for a Sort-Object Sort Order along the lines of the following T-SQL-esque snippet:

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

And here, in PowerShell, is just that:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($_.name -like "MSSQL$*") {1} else {0}}}, displayname

So now we have a script that shuts down the services in an order that won’t cause dependency issues:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($_.name -like "MSSQL$*") {1} else {0}}}, displayname |
    Stop-Service

Stop_Service Script No Errors

No errors – only warnings, about slow-responding services.

Start-Service

And, in order to restart all the services, without getting errors, try changing the “0” to a “2” in the Sort-Object, and replace “Stop-Service” with “Start-Service”, thus:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($_.name -like "MSSQL$*") {1} else {2}}}, displayname |
    Start-Service

And that all looks good…

Start-Service Script starting

…except…

Start-Service Script errors

Bother.

Turns out that those are error messages from me trying to start services which are marked as “disabled”.

Identifying Disabled Services in PowerShell

At first thought, PowerShell’s Get-Service results object *should* provide a property to indicate the Startup Type of a service. Unfortunately, it doesn’t. There’s a MS Connect item to upvote if you want them to change this, but don’t hold your breath!

There are blogposts out there discussing how to do this using the WMI PowerShell objects, which is something to think about later.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged . Bookmark the permalink.

2 Responses to Services and Sorts in PowerShell

  1. Anthony Brown says:

    There is a parameter -force that you can pass to stop-service which can be used to stop a service even if it has dependents running. SQL server seems to be clever enough if you do this to stop the agent service before stopping the main sql service.
    So, if I want to stop an instance of sql called sql2008 and sql agent if its running, I just use

    get-service ‘mssql$sql2008’ |stop-service -force

    Note – this works for sql server and sql agent. I do not know if the same thing works for other services with dependent services

  2. thomasrushton says:

    Looking back, it’s a bit simpler than I thought…

    gsv *SQL* | Sort-Object dependencies -descending | Stop-Service

    Seems to be smart enough to do the job.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s