heartbeat monitoring of sql server

Question

Hi ,

Please help me out in scripting the below scenario.

I have list of 100 SQL servers(there are few nameed instance as well) . My goal is to

1)Read each server from the table/textpad .

2)Try to establish connection with Username and password provided . (Its a high previleged windows account.Common for all servers.)

NOTE : SQLCMD ,T-SQL or POWERSHELL would do.

3)Check if  server is online .if Yes,

   *) Skip it and go to next server or instance in the list.

4)IF no,

  *) need to throw a mail that server/instance is down.

I would really appreciate very much if someone culd head up and get me a script as i need it very urgently.

Thanks ,

Aravind kumar.H

Answer 1

If you search around you will find several scripts and approaches that people use.  Some of these are a little old now and others probably do not quite fit.  Examples:

http://www.databasejournal.com/features/mssql/article.php/3347241

http://www.sqlsolutions.com/products/Heartbeat/index.html

However, have you considered a tool such as: http://www.nagios.org/

We do use nagios, although for some things I also use linked servers from a utility server to check the status of things that we care about. 

RLF

Answer 2

Hi ,

Please help me out in scripting the below scenario.

I have list of 100 SQL servers(there are few nameed instance as well) . My goal is to

1)Read each server from the table/textpad .

2)Try to establish connection with Username and password provided . (Its a high previleged windows account.Common for all servers.)

NOTE : SQLCMD ,T-SQL or POWERSHELL would do.

3)Check if  server is online .if Yes,

   *) Skip it and go to next server or instance in the list.

4)IF no,

  *) need to throw a mail that server/instance is down.

 

I would really appreciate very much if someone culd head up and get me a script as i need it very urgently.

Thanks ,

Aravind kumar.H

Answer 3

Try below script for the same.  This script will check for one server. You can put it into cursor or loop to check for multiple server.

Also You need to have regualr network access for all your servers from you monitoring box.

DECLARE @A INT
DECLARE @CMD VARCHAR(500)
SET @CMD = 'XP_CMDSHELL ''SQLCMD -S 1.1.1.1 -E'''

BEGIN TRY
 EXEC @CMD
END TRY

BEGIN CATCH
 IF(@@ERROR >0)
 BEGIN
   SELECT 'WRITE  STATEMENT TO SEND ALERT FOR MAIL'
 END
END CATCH

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Answer 4

Hi Russell ,

Thanks for the reply. We prefer scripts rather tool. Would be glad if you could help out on that.

Note : Pls avoid Link server in the script. To be more clear of the requirement,

*) select server name from a table. Check if its up. Gimme an alert if its down.

*) else skip and run again.

Answer 5

Hi Rohit,

Thanks for your time . I substituted the Ip Address with my server name. But I am getting the result as "wirte statement to send alert " ,which we shud not. I am sure this server is up and runing. It should skip actually.

Pls help on this.

Answer 6

I think your requirement is a big project, not some simple script can do! May be it is acceptable that, to create a job on each instance to send email to you every one hour(any time frequent). If no email received, then check the instance.

Answer 7

Try below script, a small mistake in previous script. It will work or you, i guess :-

DECLARE @A INT
DECLARE @CMD VARCHAR(500)
SET @CMD =  'SQLCMD -S 127.0.0.1 -E'
Exec xp_cmdshell @cmd

IF(@@ERROR >0)
BEGIN
SELECT 'WRITE  STATEMENT TO SEND ALERT FOR MAIL'
END

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Answer 8

I think your requirement is a big project, not some simple script can do! May be it is acceptable that, to create a job on each instance to send email to you every one hour(any time frequent). If no email received, then check the instance.

I would just:

1. Log the result of the heartbeat check into a (dedicated) logging table

2. Send email alert when server is down

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 9

Hi Kalman ,

Thanks for the reply.

Obsolutely yes.It is big.But i somewat made it.. with the help of powershell ... 

This is the key line

"$a= invoke-sqlcmd -serverinstance "srv_name" -Database master -Q Query "select name,state_desc from sys.databases where  state<>0"  -Errorvariable err silentlycontinue "

Now what i need is.. 

How to manipulate or count the result of this query.. Like for example.. if two database is down... $a shud have some data as result. 

so i will print ,if $a.count is not zero,  there are databases down in particular server....

if $a.count is zero , we will skip the loop and go for another server.

Note : for creating sql server connection and to check its (alive/not alive) status i used the same invoke-sqlcmd statement with lil logic behind..

I would be happy if someone helps me in teaching me how to find the count of the table being return from the query statement.