Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Doing healthcheck on 40+ SQL Servers centrally.


Doing healthcheck on 40+ SQL Servers centrally.

Author
Message
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 3197
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 3625
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’! **
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 3197
Thanks. Will check.
andrew gothard
andrew gothard
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 5504
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.
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 3197
Thanks.

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


Thanks,
San.
quackhandle1975
quackhandle1975
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 1227
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

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
andrew gothard
andrew gothard
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 5504
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.
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 3197
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search