Long Distance Backups

  • Looking for some suggestions.

    We have about 25 servers scattered at branch offices arround the country (everywhere from California to South Carolina, Texas to Chicago, etc.). They each house 1 to 3 databases, anywhere from 0.10 to 5.00GB in size (full backups can be zipped up to be 6MB to 850MB). Those databases are used by a desktop application used at those 25 offices. Everything is connected via a giant WAN.

    At our corporate office we need failrly recent copies of the data from the offices to run reports, feed our website, etc.

    Currently we do a nightly full backup at each office, zip that backup, and send it across the network to our corporate office, where we unzip and restore them at our corp. office. We also have a DoubleTake setup that we can use also, but we are getting ready to quit using that for a variety of reasons. Our zip file transfers have proven historically to be very reliable, but the files are getting to the point where they take several hours to transfer arround the network.

    Wonder if anyone else has a similar situation and what solution they use for things like this.

    We have the following things we have to keep in mind.

    We occasionally have issues with connectivity to the branch offices. We can use utilities like RoboCopy to get data here, but things like DoubleTake tend to have trouble being reliable due to our network.

    We occasionally have network admins that make various backups without telling us (i.e. we can't really use transaction log backups reliably since they mess up our chains).

    Most of the branch servers are SQL2000, but we are gradually upgrading to SQL 2005. We don't have the bandwith (or network reliablity) to support mirroring across our network, even asynch. mirrong).

    The Redneck DBA

  • not the best solution, but you can replicate the data to a single db or to a central location and backup from there

  • This might be heretical to some, but here goes anyway... Is there a critical reason to do every office every day? I mean - I see what you're doing, and it certainly seems fine, but has the analysis been done to figure out what redundancy you really need? Would it be better to have more localized redundancy (like - on-site mirroring), possibly backed up with regular but less frequent send to the local office? How painful would it be to do some of that "off-wire" (say - ship a backup to Corporate via FedEx, for example), or is that overly disconnected? Perhaps just enough to bring your transmit window back in line? Or would it make sense to offload this onto another set of connections?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • We need each office every day because our website and some corporate reports need fresh data. Having it for disaster recovery/redundancy is only part of the need.

    The Redneck DBA

  • Have you looked into having a centralize subscriber with each location publishing? That way, if you only have to transmit the changes, your volume across the WAN can be reduced. 'Centralized' backups could be executed off of the subscriber database, no?

    It's a thought. Has anyone tried this? Did I miss something stupid and obvious (very likely - I have no experience in this area).

  • Hadn't thought of that. Will that work well when we have occassional network outages here and there?

    The Redneck DBA

  • - Did you take a look at log-shipping ?

    - or work with full backup once a week and the other days restore the db and apply (the smaller) log backups ??

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Or full backups once per week and nightly differentials.

    Are you staggering the times that they each get copied / applied?

  • You mentioned:

    At our corporate office we need failrly recent copies of the data from the offices to run reports, feed our website, etc

    That means you just need to retrieve latest data. Hence, LogShipping is good solution for you.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Ummm... I don't get it... if you have a WAN amongst all of the offices and you're required to report on all the offices, why don't you have a centralized database to serve the WAN? Or, maybe an East Coast Server and a West Coast Server with the necessary sharing of transactions (replication, log shipping, etc). That would make everything a whole lot easier...

    --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)

  • Because the WAN isn't reliable enough.

    The Redneck DBA

  • Heh... understood. Been there and done that. Not fun no matter which way you do it.

    I agree with most of the others... log shipping is probably the best way with a month end "resync" just to make sure.

    --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 12 posts - 1 through 11 (of 11 total)

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