Hey PowerShell Eventing, can you notify me … when SQL Server Service stop ?


 

SINAL_~1

How many times you do not look for a way to be notified when the SQL Server service stopped, whether or not to 1 or 100  server, local or remote . There are some ways
Do you know any solution that uses a ONE command line?

I’ll show you:

It all starts working with WMI events.
For an understanding of what is WMI and WQL, I suggest see these posts from Jedi and friend :

Ravikanth Chaganti – WMI Query Language (WQL) – Event Queries: Introduction

First we download the fireworks, or the function module that will display a balloon notification in windows task bar :

Robertro Belo´s Blog – Sly PowerShell (Balloon tip notifications)

and put into your user profile.

Now lets go the examples, code below :

I build a function that inputs :

  • ComputerName : Computer Name
  • ServiceName : Service Name

and output : Nothing, justa a message that the event was created successfully or an error if not.

And how to use ?

I want to create a notification if SQL Server service stop in my local machine :

   1: Show-BalloonNotification -computername Vader -ServiceName MSSQLSERVER

image

Now if I stop de MSSQLSERVER service..the magic happens

image

Take a look at this balloon Alegre

and it works remotely , you will get a balloon message in you local machine if SQL Server Service in server Server1  stop too Alegre. Just change the input parameters.

Now, you must be thinking, but I would have to go service toservice, server to server?. NO, you can use my function Get-SQLServerServiceInfo in the previous post and pipe for Showballon. The full module is at the end of the post.

The Get-SQLServerServiceInfo was updated to send correct values to ShowBallon :

  • Input Parameters :
  • ComPuterName : Computer Name
  • SQLServerInstance : SQL Server Instance Name or “ALL”
  • ServiceType : “Engine” = SQL Server services “Agent” = SQL Agent Services

and the ouptut is :

  • ComputerName : Computer Name
  • SQLEngineInstanceName : Instance Name
  • ServiceAccount : Service Account Name
  • IsLocalAdmin : if ServiceAccount  is Local Admin
  • ServiceName : Service Name

At this point I want to Thanks Jeffery Hicks to solve my ValuefromPipeline problem. Tkx Sir Alegre

So, lets start to play :

1 – balloon Notification in all SQL Server services from Server 1

   1: Get-SQLServerServiceInfo -computername Server1 -SQLServerInstance all -ServiceType engine | select ComputerName,ServiceName | Show-BalloonNotification

2 – Now, only from SQL Agent (just pass Agent in -servicetype parameter)

   1: Get-SQLServerServiceInfo -computername Server1 -SQLServerInstance all -ServiceType engine | select ComputerName,ServiceName | Show-BalloonNotification

3 – Only in Inst1 Instance, SQL Agent service from Server 2

   1: Get-SQLServerServiceInfo -ComputerName Server2 -SQLServerInstance Inst1 -ServiceType Agent | select ComputerName,ServiceName | Show-BalloonNotification

4 – Scaling to my server list

   1: "Server1", "Server2", "Server3" | Get-SQLServerServiceInfo  -SQLServerInstance all -ServiceType Agent | select ComputerName,ServiceName | Show-BalloonNotification

or

   1: 1: Get-Content c:\temp\servers.txt | Get-SQLServerServiceInfo  -SQLServerInstance Inst1 -ServiceType Agent | select ComputerName,ServiceName | Show-BalloonNotification

In Brazil we say : A picture is better tha 1000 words so :

image_thumb2

Unfortunately when you turn off the server, the event is off. this is a problem?
No.. In the next post I show how to make it permanent Alegre

PS – And without FOREACH Alegre

Download Full Module

Can you see this ? Can you feel this ? Can you fell the Power ?

POWERSHELL ROCKS !!!

Why drink the water
from my hand?
Contagious as you think I am
Just tilt my sun towards
your domain.
Your cup runneth over again.

Don’t scream about
Don’t think aloud
Turn your head now baby
Just spit me out
Don’t worry about
Don’t speak of doubt
Turn your head now baby
Just spit me out

Why follow me to higher ground?
Lost as you swear I am.
Don’t throw away your basic needs,
Ambiance and vanity.

December promise you gave unto me
December whispers of treachery
December clouds are now covering me
December songs no longer I sing

December

Collective Soul

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL. Bookmark the permalink.

7 Responses to Hey PowerShell Eventing, can you notify me … when SQL Server Service stop ?

  1. adatapost says:

    Interesting! Thanks for sharing.

  2. Tkx Guys, Glad you liked it 🙂

  3. Maha says:

    I m unable to set up this event.

    I downloaded Show-BalloonTip.ps1 kept in my user profile. I have default instance(MSSQLSERVER)

    I did the below things

    1 ) Show-BalloonTip -computername xxxx-ServiceName MSSQLSERVER

    2 ) .\Show-BalloonTip -computername xxxx-ServiceName MSSQLSERVER

    3 ) changed policy to unrestricted and executed the above ones

    4) Show-BalloonTip.ps1 -computername xxxx-ServiceName MSSQLSERVER

    5) .\Show-BalloonTip.ps1 -computername xxxx-ServiceName MSSQLSERVER

    All above ways are not working. Kinldy help me to set up this event

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s