Post reply

Doing healthcheck on 40+ SQL Servers centrally.

  • July 8, 2014 at 1:29 am

    #287587

    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

  • July 8, 2014 at 3:18 am

    #1727685

    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’! **

  • July 8, 2014 at 4:03 am

    #1727691

    Thanks. Will check.

  • July 8, 2014 at 4:23 am

    #1727698

    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.

  • July 8, 2014 at 8:09 pm

    #1727932

    Thanks.

    Do you have any such sample powershell script by any chance ?

    Kindly share if you have any handy.

    Thanks,

    San.

  • July 9, 2014 at 4:01 am

    #1727973

    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[/url] stored proc from BrentOzar.com

    Rgds,

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • July 9, 2014 at 5:18 am

    #1727986

    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.

  • July 9, 2014 at 7:13 am

    #1728029

    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.

  • July 9, 2014 at 12:20 pm

    #1728187

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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