Monitoring a remote server?

Okay, my company is being a tight wad and doesn't want to spend the money for a good SQL monitoring tool. As a result, I am destined to custom write what I need. Most is done, but here is my current dilemma...

I have 2 servers, the monitoring server and the activity server. My monitoring server is the central machine that handles all my monitoring tasks and error notifications. I need a way to monitor if another SQL Server "is alive" from the central monitoring server. If the remote server is dead, the monitoring server will post an error that will be handled per the existing error routines.

How can I monitor the "alive" status of one SQL Server from another? One though I have is to write a simple DTS job (SELECT @.@.SERVERNAME) that runs from the monitoring server connecting to the remote server. I would then schedule that DTS job on the monitoring server. Then I would add a step the scheduled job to handle the error processing. Is there a better way to do this without pulling in DTS?It could be just a job rather than a DTS package.

Or "AT" from a command prompt running an ISQL script.|||On your local machine register both the servers in EM and regularly refer to SQL error log for any information.

Also you can setup a job to check whether the Server is running by using OSQL -L option which returns the available on the network.|||bglass, In our shop we have, using your terms, about 23 activity servers providing various mission critical and non-critical data. We need to know a bit more about our servers, are there any failed jobs, is drive space getting low, any long running jobs, current user load, etc. Each activity server is required to gather and hold all this info on a schedule that best fits that servers role. At regular intervals our monitoring server talks to each activity server and copies the above info for historical analysis and purges the copied data. Our monitoring server makes all this data available to a web server and by next month will have the ability to send out warnings and alerts to the appropriate DBA.

Does this help or do you have more questions?|||I already have the activity server (I have numerous of them, but only used two for this representation) gathering lots of info, including internal errors, and transmitting them to the monitoring server. The monitoring server then sends out the proper notifications (since we use Lotus Notes, sending email is not that easy, so I have it consolidated).

The problem is proactive notification if a SQL Server is not responding. We are a 24/7 flight operations center. Right now we get notified after hours by the end users if the system has gone down. Not a good solution.

The only other alternative I have come up with so far is to use linked servers. That way I can have a job on the monitoring server that runs a simple query on the linked server. If that step fails, I have an issue. But I HATE LINKED SERVERS.

What about some sort of ActiveX/VBScript step in a job that could query an external server?|||Not sure what the problem is with linked servers...

How about using xp_cmdshell and OSQL? Each job step could call a differenet server and any unexpected results would result in a page.|||Hi,
try perhaps so:
1) develop a VB program, that runs on that server and pools for instance every 1 minute to check if SERVICE MSSQLSERVER is running.
If no - send a message to:
a) Lotus Notes
b) SMS to cell
c) a file.
d) sends a message into the DB (special notification table)

If c) then develop another VB PG that pools and looks into that PG for checking for the messages, which are writing into it. You can do then waht you want.

if d) then you can make an INSERT trigger, which do some thing, which are needed to you

Is it enough for you?

Zbig|||Please check out SQLCentric at http://www.pearlknows.com. SQLCentric is a fully functional web-based database network monitoring and alert system that is deployed on your company intranet.
We offer 5, 10 and 25 license starter kits. Please download a copy to evaluate for yourself.