October 21, 2011 at 1:49 pm
Hi,
Background Information:
SQL Server 2008 R2 64-bit Enterprise Edition
One TB Database
Around 165 GB Compressed Full Backup
Dell EqualLogic SANs
Right now on our 100 Mbps WAN connection, it takes about six hours to transfer the full SQL backup to the backup site.
Problem:
If our WAN connection becomes 15 Mbps to 45 Mbps, I will not be able to transfer the full SQL backup to the backup site without saturating the entire WAN network so I was wondering if anyone has any suggestions.
I am also using "Smart Copy Snapshots" that the EqualLogic SANs offer, but the snapshots reside on a file share that the primary and backup SANs can read to restore the databases. As this method takes minimal bandwidth (but utilizes a lot of storage on the SANs), I would still like to have the SQL backups on-hand just in case.
Does anyone have any suggestions?
Thank you in advance!
October 21, 2011 at 2:00 pm
can you start sending differentials or transaction logs to the other site instead of full backups, and start doing incrementals restores there instead?
Lowell
October 21, 2011 at 2:00 pm
If you need a full backup, and you need to transfer it off site like this, then there isn't much you can do.
How about log backups instead? They would be much smaller. Also, if you have an off site server, you could do log shipping to restore the backups into SQL server and verify that they are valid.
As the saying goes, "If you haven't restored your backup, then you don't have a backup."
October 21, 2011 at 2:15 pm
I'm sorry. I forgot to mention that we also have an asynchronous database mirror to the backup site. The reason I would like to have full backups on-hand is for DBCCs, archive certain data, and to "reset" the differential backups (since database maintenance entails on having large transaction log backups).
"Normal" transaction log backups seems to be good over the WAN. Incremental restores is pretty much like database mirroring so I apologize for not mentioning that in my first post.
Log shipping does sound like an interesting idea that I haven't thought about. I'm not sure if that will satisfy my requirements though.
If you don't mind me asking (and you don't have to answer), how is your environment set up?
Thank you for your help!
October 21, 2011 at 2:30 pm
How is your environment set up?
We use a solution from EMC which mirrors our SAN to an offsite duplicate SAN (which is attached to an offsite duplicate of our physical infrastructure). It's an expensive solution, but very powerful.
If I understand you correctly, you need full backups of your production database (all 1,024 GB) in order to validate your asynchronous mirror. Do I have that right?
Perhaps it would be easier to switch it around. Create an asynchronous mirror of your offsite secondary (or use replication). Put that in your production site, and then compare it to your original production database. If the copy of the copy is good, then you know that the copy is good. :hehe:
October 21, 2011 at 2:44 pm
I believe that Dell has something similar called SAN Replication. Do you happen to know if it utilizes a lot of bandwidth? I guess that's kind of hard to answer since it all depends on our interpretation of "a lot". 😛 I'll have to look up the EMC solution and see how that compares.
The reason to have the full backup isn't exactly to validate the mirror. It's just to have to run CheckDB, archive data, "reset" the differential backups, and do tests on. It's also nice to have just in case both sites go down (the chances of that happening is rare, I know...).
October 24, 2011 at 7:19 am
This might be a silly idea but there are some new detachable HD, kind of like a USB stick but it is for Servers. You could attach it, copy the file over to your USB HD and then just unplug and plug it into the server you want to restore to do your DBCC.
Just my 2 cents
-Roy
October 24, 2011 at 7:29 am
I did not know that you had a long way to go to get the Back up. I thought it was just another office right beside. Sorry, My bad.
Other than that the only option is to use the compressed back up and use a split application and send it as smaller chunk.
-Roy
October 24, 2011 at 7:53 am
Your compressed back up, Just Zip it up using WinRAR or another app like that and split it into smaller files and then put it together at the destination. It is no big deal but you will be doing FTP or Robocopy of smaller files
-Roy
October 24, 2011 at 8:09 am
dajonx (10/21/2011)
we also have an asynchronous database mirror to the backup site. The reason I would like to have full backups on-hand is for DBCCs, archive certain data, and to "reset" the differential backups (since database maintenance entails on having large transaction log backups).
why not create database snapshots of the mirror database to use for DBCC,etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 8:37 am
For splitting the full backup, I could do it during the full backup and perhaps "trickle" it over the WAN during nights. Just hard to gauge and stuff...
As for the mirror snapshots, I haven't thought about that. I'll definitely be reading more about that.
So due to the bandwidth limitation, shouldn't I still take full backups?
DR Plan:
1) Database Mirror
2) SAN Replication - still needs to be implemented
3) SAN Snapshot with transaction log backups
4) Full Backup with differential/transaction log backups
Edit: Now that I think about it, I might as well do DBCCs on the SAN Snapshot. But I would like to offload the database to my local computer so I won't consume all of the backup server's resources... I guess that's why I'm pushing for the full backup... :crazy:
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply