Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    32

    Unanswered: combining sql server instances, need help

    I have two sql servers, each with several databases on them. Is there an automated, or way to script, combining these servers into one? I want to create a 3rd server, and put all the databases from both server 1 and 2 on it as a back up percaution. My theory is that on server 1 and 2, I will back those databases up nightly via script, and then on server 3, restore the databases nightly. If either server 1 or 2 dies, I simply put that IP address on server 3, and poof! The databases do not go down (however the backup may be some hours old). That gives me time to troubleshoot the down server, and customers do not suffer downtime. Server 3 would also need to be complete with jobs, full text catalogs, logins, etc...

    Has anyone developed a system like this? I'm not a wiz in qa yet, so this will take me some time to develop and refine. If you have any tips, I would appreciate it.

    Thanks
    Tom

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: combining sql server instances, need help

    It's called Log Shipping in SQL 2000. There are informations in BOL and Microsoft website regarding how to set it up and change primary or secondary role when disaster strike.

  3. #3
    Join Date
    Sep 2003
    Posts
    32
    Thanks for your answer.
    Keep in mind, I don't just want 1 primary, and 1 secondary server. Actually, I have several sql servers. I would like to have all these databases ready on one single spare sql server. Is this possible with log shipping?

    Thanks
    Tom

  4. #4
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Yes it is

    Remember , log shipping is available in SQl2000 only for SQl 7.0 Servers you hav to manually set it up . If you are moe intrested , let me know and I can send you some sql files . I have L/shipping setup on all of my production servers .. (17 Primary Servers use 9 Standby servers consolidating 2 Server to 1

  5. #5
    Join Date
    Sep 2003
    Posts
    32
    aashu:

    That would be great! For example, I have 10 sql servers, with 10 databases each. Now I want to set up one sql server as a backup, and put all 100 databases on it. so when one sql server goes down, I just put that IP address on my backup server, and delete the other 90 databases.

    Thanks Tom

  6. #6
    Join Date
    Sep 2003
    Posts
    32
    PS, they are all sql 2000 servers

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    CAUTION! Log shipping is only available in Enterprise Edition of SQL Server. This could be quite costly, depending on how many machines you have...

  8. #8
    Join Date
    Sep 2003
    Posts
    32
    Thanks, they are all sql 2000 Enterprise edition servers

  9. #9
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    This is what you do

    I am attaching files
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    another

    Save the file on the the PATH as mentioned in the previous file as synch_data.bat . The previous file creates a job (has to be run on SQl Qry Analyzer) . The step of the job is calling this batch file attached. You will have to check all the physical references according to your servers .
    If you need more detail Let me know
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2003
    Posts
    32
    thanks aashu, I will dig into this next week, I really appreciate it.

    Tommy

  12. #12
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Sure

    No Problm .. I will help you out and make it work for you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •