Setting Alerts for my SQL Server Services-Stop and Start

Question

I've a small Question on Creating Alerts. I know how to create alerts from SQL Server Agent if any Job fails. Could you please let me know how to set/create alerts when the Agent Service or SQL DB Engine Service itself stops. We have lot of Standalone and Clusterd SQL Servers. Can we create a alert through out all our servers(our Domain), so that it shoots an e-mail whenever a SQL Service is stopped without using any 3rd party tool.

Thanks in Advance! I really appreciate your help.

Thanks and Warm Regards, Sri Reddy.

Answer 1

In lieu of a proper monitoring system you can set up event actions on any Windows service via services manager. Get the properties of the service and set things up on the Recovery tab. You can have the service mgr attempt to automatically restart the service, run a program or reboot the server.

In your case you could create a script that would send your alert and set the service mgr to run your script when the SQL Server eh service stops unexpectedly.

Answer 2

You could setup a scripting box (running xp/win7/or some random server) that just runs a powershell/vbscript script that uses wmi queries to see if the service is running on the boxes.

something like...

1.  Get list of computer names (db servers) from file
2.  check via wmi if service mssql-agent is running
3.  If not, email xx@xx.local
4.  Sleep 2-5 minutes.

Otherwise you will have you get a third party tool. There are several available.

Here's some web page with a good starting point...

http://www.youdidwhatwithtsql.com/monitoring-starting-services-with-powershell/113