SQL Clone
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6154 Visits: 3200
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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8316 Visits: 3718
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6154 Visits: 3200
Thanks. Will check.
andrew gothard
andrew gothard
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2674 Visits: 5965
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6154 Visits: 3200
Thanks.

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


Thanks,
San.
quackhandle1975
quackhandle1975
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4164 Visits: 1242
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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2674 Visits: 5965
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6154 Visits: 3200
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 Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206629 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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