Script For Monitoring State of other servers and alerting !!!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » Script For Monitoring State of other servers...

Script For Monitoring State of other servers and alerting !!!

Rate Topic

Display Mode

Topic Options

Author

Message

bobnysoftz

Posted Tuesday, December 20, 2011 7:42 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 03, 2013 7:13 AM
Points: 12, Visits: 277

Please can you help I need a script that will use a centralized server to monitor other sql servers state and if a server is offline it will send a email to alert the dba about which server has a database that is offline . Pleaase do great assist and all your help is greatly appreciated.

Post #1224435

benjamin.reyes

Posted Thursday, December 29, 2011 4:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:34 PM
Points: 198, Visits: 745

I'm sure there is a better way. This was just fast to throw together.

This assumes you have dbmail setup and a valid operator.

Run this on the server you want to monitor:

create database dbadmin
go
use dbadmin
go
create table serverstatus (
isawake char(1))
GO
insert into serverstatus
values(1)

create a least privelidged login and a link server connection from your monitor to the monitoree with that account.

Then throw this in a job step

Declare @isawake char(1)

set @isawake = (select isawake
from [SERVERIWANTTOMONITOR].dbadmin.dbo.serverstatus)

if @isawake <> '1'

begin
raiserror('Test Notify Server Failure', 16, 1, 1)
end

Set the notifications on the job to email the operator on failure. Also have a second server that points back to the monitor server to validate that is awake as well.

Post #1228117

« Prev Topic | Next Topic »

Permissions