Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Doing healthcheck on 40+ SQL Servers centrally. Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 1:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Hi,

We have almost 40 SQL Servers and we need to send an healthcheck report on all the servers everyday.
Currently the details are captured into some of the tables by running a batch file from one of the central server.

What's the best way to do this health check on all these servers ? Any suggestions ?.


Thanks,
Smith
Post #1590209
Posted Tuesday, July 8, 2014 3:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 2,262, Visits: 2,725
I would prefer to execute the healthcheck locally by means of a SQL job and store the results in a table. The next step is to consolidate the data of all servers into a central management server. You have the choice to push it from the local servers to the central server or pull it on the central server from the local servers. You can use linked servers or SSIS to transfer the data.
From the central server you can send an email or use other alerting/information system.

If you search the internet you can find several custom-made/free/payed management scripts available that can give you a good starting point. Just a random example: http://www.sqlservercentral.com/articles/Monitoring/98106/


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1590245
Posted Tuesday, July 8, 2014 4:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Thanks. Will check.
Post #1590270
Posted Tuesday, July 8, 2014 4:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:39 AM
Points: 338, Visits: 3,289
Running te collection scripts and storing to a central monitoring server using powershell scripts is a pretty powerful option as well, and one I personally quite like.
SSIS is a good tool, I have used it a lot, but with having a DEV background, I find making changes a lot easier - if you have a fairly dynamic SQL Server estate - using PoSh scripts. Here you can just add / remove servers from a list (held in a table or file) as you comission / decomission instances. It's also easy to add additional monitoring simply by adding a module to your PoSh script


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1590277
Posted Tuesday, July 8, 2014 8:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Thanks.

Do you have any such sample powershell script by any chance ?
Kindly share if you have any handy.


Thanks,
San.
Post #1590580
Posted Wednesday, July 9, 2014 4:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 2,395, Visits: 967
Joy Smith San (7/8/2014)
Thanks.
Do you have any such sample powershell script by any chance ?
Kindly share if you have any handy.


If you do a simple google search Joy, I am sure you will find plenty of powershell/SQL Health check scripts however they are not a "one size fits all" and you will have to have at least some rudimentary knowledge of Powershell as no doubt you will have to tweak the scripts to run on your environments. I would also advise downloading the excellent SQLBlitz stored proc from BrentOzar.com

Rgds,

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1590652
Posted Wednesday, July 9, 2014 5:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:39 AM
Points: 338, Visits: 3,289
Joy Smith San (7/8/2014)
Thanks.

Do you have any such sample powershell script by any chance ?
Kindly share if you have any handy.


Thanks,
San.


I've got a home rolled one that's still a bit of a WIP, but it might give you an idea if you're interested.


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1590672
Posted Wednesday, July 9, 2014 7:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Thanks all.
Yes, I already wrote few codes.
Since these are for my production servers, I need ensure that I am doing it in the best possible way.
I will go through the link mentioned. Kindly send me whatever you have as well.

Thanks again.
Post #1590723
Posted Wednesday, July 9, 2014 12:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
Just a reminder... ask yourself what happens if the "central" system goes down.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1590904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse