SQL Agent. Run T-SQL in scheduled job against a DB in a different SQL instance.

  • Well, that's what I'd like to do! This is so I can keep the scheduling config and T-SQL on one admin SQL Server, whilst the databases reside on different servers. This'd mean I can email using SQL Mail from my admin instance too.

    I'm seeing a few things that might be of interest, in Linked Servers and Multi-Server Administration, but I'm not yet sure which (if any) route would take me where I want to go.

    I guess I could use a third-party scheduler too.

    Any help would be appreciated.

    Regards, Greg.

  • Using multiserver administration vs linked servers are really two different things. You can accomplish some what you are looking at but they are very different and implemented differently. You push out jobs in multiserver administration. And it takes a bit more work and troubleshooting than it may seem. Probably why not a lot of folks have enjoyed the pain with MSX/TSX. And if your linked servers have an issue would that mean nothing gets backed up on the other servers?

    What kind of procedures do you have in place for when your main Admin server is down? If that's where all the emails are going to be sent from you wouldn't be notified. How are you planning to mitigate any issues with downtime of the Admin server so that it does not affect any processes, jobs on the other servers? Is the admin server clustered, mirrored, alwayson? It needs to be if it's going to manage everything for all the other servers.

    Sue

  • greg.bull wrote:

    Well, that's what I'd like to do! This is so I can keep the scheduling config and T-SQL on one admin SQL Server, whilst the databases reside on different servers. This'd mean I can email using SQL Mail from my admin instance too. I'm seeing a few things that might be of interest, in Linked Servers and Multi-Server Administration, but I'm not yet sure which (if any) route would take me where I want to go. I guess I could use a third-party scheduler too. Any help would be appreciated. Regards, Greg.

    What would this job be measuring?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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