Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: SQL 2008 High Availability options

    Hi,

    we currently have a SQL 2008 data warehouse with about a dozen different databases. We would like to have about 5 of the databases replicated or mirrored to another database server.

    Since it's a data warehouse, the data only gets loaded during a 3 hour period each morning and the rest of the day it remains static. I'm trying to come up with the best option to copy these databases to a different database server on a scheduled basis in case the main data warehouse is unavailable.

    I looked at snapshot replication which seems like it could be a possibility. I'm just worried about how long the process will take. Are there any other options that would make sense given my situation?

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm a big fan of KISS. Have you considered either backup/restore or log shipping?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's because he likes to party ev...eryday

    http://en.wikipedia.org/wiki/Kiss_(band)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2009
    Posts
    62
    The issue with Log Shipping is that these database are set to simple recovery. We do have symantec backups running each night on the server.

    I'm sure the backup/restore can be automated maybe through SSIS or something. Maybe that's the way to go...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He said KISS

    How big are the backups?

    I would create a batch file that

    Zips the back up
    ROBOCOPY the file to the destination Server
    Use sqlcmd to do a restore

    Done

    Next
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2009
    Posts
    62
    the biggest database is about 60GB. the others range from 500MB to about 4GB.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you are using enterprise edition of SQL Server, you can compress the backup on the way to disk.

    How much data is changing on an average day? I am relatively sure that transactional replication can be used here, but if the entire volume changes each day, the backup/restore route may be better. If you go with transactional replication, you will need to size your transaction logs appropriately.

  8. #8
    Join Date
    Sep 2009
    Posts
    62
    most of the databases are loaded fresh daily (tables are truncated and reloaded with new data). We do have enterprise, so i'll have to check out the compression option to see how much space we will need for the backups.

Posting Permissions

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