SQL Server – Monitor SQL Server Services using Powershell

Last month on 26th June I delivered a webcastHow to Monitor SQL Servers using Powershell – Part 1 under Microsoft Lunch Time Banner.  For those who don’t know about Lunch Time Series – it’s a new series started by Microsoft MVPs that happens every alternate Wednesday at lunch time generally for one hour. The webcast was very well received and I got multiple successive questions on the same.

In this blog I’ll show you how easy it is to use the powershell inbuilt cmdlets to start monitoring your SQL Server.

We’ll be using Get-Service cmdlet which provides a list of all Services on a server\system with the below mentioned details:

  1. Status
  2. Name (Service Name)
  3. Display Name

Now if you connect to your powershell console and execute this cmdlet you’ll get a complete list of services with their current status – you can refer the below screenshot.

1_Monitor_SQL_Server_Services_using_Powershell

Now that we have the information available for each and every service on our server, we have to find just SQL Server related services. This can be achieved with the help of placing a filter.

To place a filter in this cmdlet we’ll use where-object cmdlet, this provides a way for you to filter data returned by other cmdlets.

Take careful note of the syntax. The where clause is enclosed within curly braces; in addition, the $_ notation is used to represent the default object.

where-Object{$_.name -like ‘*sql*’}

Now you must be wondering, what’s the current object & how to put the filter on the output of get-service? Well the answer to this is PIPE.

Pipe | is a separator character which separates two commands. When we use the pipe that typically means that we use a cmdlet to retrieve a collection of objects, we hand that collection over to a second cmdlet, one that does some further processing (filtering, grouping, sorting, etc.). That’s what the pipeline is for.

   

So in this example we’ll handover the output of Get-Service cmdlet and hand it over to where-object to put the necessary filter. Let’s see how to create a command:

Get-Service | where-Object{$_.name -like ‘*sql*’}

This is how the output will look like after we issue this command on a powershell console.

2_Monitor_SQL_Server_Services_using_Powershell

Now let’s apply some formatting to the output so that it’s readable and ready to use. To make all columns readable we’ll use format-table cmdlet.  This is how the output will look like:

Get-Service | where-Object{$_.name -like ‘*sql*’} | Format-Table –AutoSize

3_Monitor_SQL_Server_Services_using_Powershell

Now that you’ve learned how to get the status of SQLServer Service using powershell, I leave it on you how you automate and use it in your environment. Do let me know how you are using it and what’s your experience.

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

One Comment on “SQL Server – Monitor SQL Server Services using Powershell”

Leave a Reply

Your email address will not be published.