Monitoring Scripts - SQL

Question

Monitoring Scripts - SQL

narayanamoorthy.a

Ten Centuries

Points: 1256
More actions
March 23, 2015 at 5:40 am

#316346

Hi,
I have 350 SQL Servers that I support at my end. I need a script that monitors these 350 instances whether the server and database is up or down. I should run this script from any client machine where it should throw the results in sql query pane. A little help needed on this so that I can schedule this query in stored procedure and monitor every 1 hour.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's applicaitons specifically designed for monitoring servers and services; a search of network monitoring sql server will get you a zillion apps. in a shop with 350 sql servers, your network team probably already has an application that does that, and you just need access to it, or to turn on SQL specific monitoring. get with your network team.

Here's a few other ideas, that i've tried over time.

if you absolutely, positively want to do this via TSQL, you might want to look at using registered servers or a Central Manangemnet server in SSMS(View...Registered Servers....Central Management Server.

once you set up all 350 servers there, you can run a query in SSMS that will hit all the servers there. I do this ewith my modest colleciton of ~50 servers.

you could do this with a powershellscript that has a text file or other source that has all the names of the 350 servers. I do this, but for other purposes... not for monitoring

you could add 350 linked servers, and add some loop/cursor construct to execute a command on each linked server on a specific machine.

I tried this, but it's a little slow and clunky sometimes, but certainly works. I've pulled a list of "last nights" failed jobs from msdb doing this for a centralized report.,

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sestell1 SSChampion Points: 10230 More actions · Answer 2

I agree with Lowell.

I use T-SQL to shell out some Powershell scripting that checks our servers to make sure all the associated SQL Server services are running on each one. It works fine for the dozen or so boxes I manage, but would be super slow for 350 servers. I think you would benefit greatly from a dashboard from a commercial monitoring product that help you monitor that many servers at a glance.

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 3

Yes I do have monitoring SCOM. But still from my side if I have some scripts it would be easy to extract the reports. Can you kindly share the scripts that you got.