March 25, 2008 at 12:51 pm
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
March 27, 2008 at 11:49 am
not the best solution, but you can replicate the data to a single db or to a central location and backup from there
March 27, 2008 at 12:24 pm
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?
March 27, 2008 at 3:25 pm
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
March 28, 2008 at 1:31 pm
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).
March 28, 2008 at 1:52 pm
Hadn't thought of that. Will that work well when we have occassional network outages here and there?
The Redneck DBA
March 28, 2008 at 2:03 pm
- 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
March 28, 2008 at 10:30 pm
Or full backups once per week and nightly differentials.
Are you staggering the times that they each get copied / applied?
March 29, 2008 at 12:14 pm
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]
March 29, 2008 at 6:59 pm
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
Change is inevitable... Change for the better is not.
March 31, 2008 at 3:31 pm
Because the WAN isn't reliable enough.
The Redneck DBA
March 31, 2008 at 4:58 pm
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
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply