Decentralised Backup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Decentralised Backup

Hi All, Need some help here. We are planning to have around 20 SQL Servers at various sites around the UK. At each site, there is no tape device for onsite backups. At the moment we replicate all files using Double-Take down to a central NAS server continually. We’re implementing MOSS2007 and WSS3.0 as a regional content server, so each WSS will have 20 content DB’s, presized to 10GB a piece, meaning we have 200GB Data (to start with – 99% WhiteSpace) to replicate and backup. Total all sites and you get to 3.5TB. This needs to be backed up daily, however 3.5TB daily SQL Data ontop of files is stupid. The links are 2mbps. As part of the project a WAN Upgrade was requested and the idea to centralise the lot, however this got thrown out (before my time). Other considerations are is that we are using versioning, so due to the nature of our business, we have have multiple revisions of one cad drawing, say 100mb, 10 versions = 1GB + SQL space to hold it in the database. What i’m looking for is suggestions on how to backup this data on a daily basis. Which ever way is suggested, it needs to be replicated and backed up centrally. Ordinarily I would prefer to do a Full on the weekend, then diff’s daily in the early hours and tran log’s every 2 hours or so. I cant do this, as it would take > 24 hours to replicate the backup files to the central NAS box for tape backup. I would consider using LiteSpeed, however at some point in the future, we will get backup files which are too big to replicate over the wire in the time given and also get onto tape. Have thought of DB Mirroring, however I cant backup the mirror, only a snapshot of a mirror which doesent give me a warm fuzzy feeling. Log Shipping I dont like because it’s not reliable. There is also no option of putting a tape drive back on each site due to the amount being spent on the centralised backup solution. So, suggestions please… I’ve already put forward an increase of WAN links to 10mbps (LES10) and centralise the lot, however that didnt bode well…HELP!
do i understand that the current backup method makes a copy of the SQL data and log files?
as opposed to a normal SQL backup? do a proper SQL backup, not a file copy,
by all means, compress the backup one way or another let me restart
strong preference is to backup to disk, compressed
then get the disk backup on to tape later,
ie, do not backup to tape directly
tape performance just has not kept up with the time to make this practical a weekly full + daily diffs is fine, especially considering the versioning you described
I do not think it makes sense to have a tape at each location if the data size is too large for wire transfer
why not just copy the BU to DVD, and mail it? (for you young guys,
in the old days, before the internet,
there was an entity called the post office,
which delivered mail, messages on paper
rumor has it they are still around, not fossilized)
Indeed. Double Take replicates the xxx_data.mdf and xxx_log.ldf files to the main data centre. Then the replication agent is shut down around 19:00 at which time BackupExec takes those mdf and ldf local copies and puts them to tape. Once the process is complete, the replication agent is started again, meaning any queued changed at the source are replicated to the target so you have a crash-consistent copy. I agree with what you are saying about disk backup’s totally, however we still have the issue of getting the files onto tape over the remote link. Because the link is 2mbps and the backup’s are likely to be around 20GB at the moment, allowing for a little overhead, on a 2mb line, using 1.5mb bandwidth, it will take 30 hours to replicate the backup files, which means you will consistently be out of date. Backing up locally and burning to DVD isnt an option, in the same way as a local tape isnt an option because you are reliant on the monkies in the local office to get the DVD, burn it, mail it and do that process every day. Add in the postage, DVD, Jiffy Bag costs for 17 locations 260 times a year gets unpractical and costly rise very quickly. Leaving the backup files on the local server isnt an option either, as if we have a server issue we loose all current data and backup’s, not to mention if there is a fire or any other environmental issue which takes the server out. I’m trying to get to a point where we have no option but to increase the links, however it’s a bit like an osterich and sand here. I need to say I’ve thought of this and this and this to be left with only one option. Cheers Steve
SQL Server DBA
Infrastructure Team.

If you are replication .mdf and .ldf files, sometimes you may end up with issues with attaching these files.
If your native backup size is 20 GB, try to use third party tools (LiteSpeed and Idera ect..) which compress upt 80%…
and instead of taking daily full backup you can do weekly/monthly full backups and daily differential backups and hourly tlog backup to replicate. Note: You can create multiple backup files make file size smaller and easy to copy…
Moderator All postings are provided “AS IS” with no warranties for accuracy.

one other option when you have a lot of static data in the db
is to use filegroups
all data belonging to a certain time period is placed a specific filegroup when the time period is done, that filegroup is marked read only then you only need to backup the current file group once the final backup is taken of each closed filegroup
Thanks Mohammed – I had thought of that, however with 10 x 20GB DB’s that is 200GB to start with. Am looking into LiteSpeed, however my concern is still with getting the backup be it full/diff/tran over the wire in a reasonable time frame. Joe – Trouble is the data is not static. As it’s SharePoint 2007 (MOSS) we have 10 content db’s per location and SharePoint when creating a site will do a round-robin and add one to each until it’s back where it started from, thus we have no way to work out which is old and which is new. Another option I’ve thought of… Setup DB Mirroring from say Manchester to London, at 7PM, have a MSX job which stops the MOSS Service via a NET STOP, Alter Partner Failover command, back the DB up in London using LiteSpeed, once complete, Another Alter Partner Failover and restart the MOSS Service…How does that sound? Cheers Steve
i think you install LiteSpeed, take backups at various compression levels, from 1 (default), to 10, until there is diminishing gain in compression to see just how compressible your data is how much would it cost to upgrade just the central office to 10mbps
and do multiple simultaneous transfers from the branch office?
Litespeed is very good at performing this kind of compression – I’ve found at level 1 it’s fast enough and compresses enough. We have a very full 100GB database that compresses to 20GB and for your currently empty db files these would compress down way further. That doesn’t really help when your databases are full however, although as stuff works quickly to start with and THEN slows down you might be able to wangle a faster connection out of them in the meantime. Have you thought about transactional replication? I guess it depends on the structure of your data if this would be feasible or not. Advantage to it is that it wouldn’t transfer big chunks at once so network speed shouldn’t be too much of an issue. There are some serious drawbacks to this approach though that you’re probably aware of. I think in your shoes I’d probably go with Litespeed – if you end up with completely full 10GB databases, you can expect Litespeed to compress to about 2GB which if I’m right in my calculations would transfer over a 2Mb line in < 3 hours. 2Mb/s ~= 200KB/s = 700MB/hour (250KB/s but allow for network congestion)
David Hilditch
MCSD, MCSE, MCDBA, MCITP, MCTS Add flight search to your website for free