January 3, 2015 at 1:45 pm
Hello Experts,
I have a situation as explained below:
1. Backup a 500gb sql database.
2. Copy it over the network to a third party datacenter (in a different network).
3. Restore the copied backup to asql instance on new datacenter.
4. Run both instances of sql (in existing datacenter and new third party datacenter) and keep in sync for a day or two, until we fully cutover.
I would need help to get expert opinions on each of the above points, to get the best possible way to achieve these.
January 5, 2015 at 5:04 am
I don't know if I count as an 'expert opinion', but a few points to consider:
Depending on the bandwidth of your network and how far way the 3rd party datacenter is, you may find it quicker to copy the backup to an encrypted external USB drive and physically ship it to the 3rd party. If you don't have access to their network and can't get access you're pretty much restricted to that option anyway.
In order to keep the two databases in sync you are going to need to use a technology such as Log Shipping or Database Mirroring. (I've not included Always On since you've posted this in a SQL Server 2005 forum). Both of these technologies require the Primary/Principal database to be running in Full recovery mode and for you to be taking transaction log backups. (I'm assuming at this point that you DO have access to the 3rd party network).
After restoring the backup (with the NORECOVERY option) to an instance in the 3rd party datacenter - will they do that for you? - you will need to restore to that database all the transaction logs taken on the Primary/Principal server since the Full backup was taken. You can then configure Log Shipping or Database Mirroring. If you want both databases to be as in sync with each other as possible, i.e. no delay, you'll probably choose Database Mirroring and run it in High Performance (asynchronous) mode whereby the Principal does not wait for confirmation that a transaction has been applied to the Mirror database before hardening the transaction at the Principal.
Bear in mind that the Mirror database will not be readable unless a failover occurs or the mirroring session is broken and the database recovered.
There's too much to go into in a forum post, but this gives you an overview. For more details about Database Mirroring specfic to SQL Server 2005, see this link.
Regards
Lempster
January 5, 2015 at 8:05 am
Lempster (1/5/2015)
Depending on the bandwidth of your network and how far way the 3rd party datacenter is, you may find it quicker to copy the backup to an encrypted external USB drive and physically ship it to the 3rd party.
Our DR site is "only" a hundred miles away. For the initial sync (a few terabytes), our guys went down to the DR site, unbolted the SAN, brought it to the production site, did the copy over fiber, and then took it back down to the DR site and reinstalled it. Then they started the log file syncs. It wasn't fun but it worked. It only took a couple of days that way including the unbolt/rebolt and travel time.
For "only" 500GB, Federal Express works wonders. I did that for a different site several years ago.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2015 at 8:45 am
Jeff Moden (1/5/2015)
For the initial sync (a few terabytes), our guys went down to the DR site, unbolted the SAN, brought it to the production site, did the copy over fiber, and then took it back down to the DR site and reinstalled it.
I would LOVE to be a fly on the wall when the OP walks into a 3rd party datacenter and asks to unbolt their SAN!! 😉
For "only" 500GB, Federal Express works wonders. I did that for a different site several years ago.
Yep, I did that on a regular basis one place I worked.
January 5, 2015 at 8:52 am
Jeff Moden (1/5/2015)
For "only" 500GB, Federal Express works wonders. I did that for a different site several years ago.
Last time I set up mirroring, 75GB database, 25km away, we copied it to external and had one of the IT guys drive it over. Hell, when I set up replication on a 2.5GB database a few months ago I did the same (and I was courier)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2015 at 9:30 am
Lempster (1/5/2015)
Jeff Moden (1/5/2015)
For the initial sync (a few terabytes), our guys went down to the DR site, unbolted the SAN, brought it to the production site, did the copy over fiber, and then took it back down to the DR site and reinstalled it.I would LOVE to be a fly on the wall when the OP walks into a 3rd party datacenter and asks to unbolt their SAN!! 😉
For "only" 500GB, Federal Express works wonders. I did that for a different site several years ago.
Yep, I did that on a regular basis one place I worked.
Heh... fortunately, we owned the equipment. The big surprise we got the last time we had to do such a thing was the owner of the building informed us that we had to move our stuff ASAP because he just sold the building. We built out another data center in one of our remote offices in just a couple of weeks and then did a major DR test. It was not a fun exercise but it was worth it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2015 at 2:26 pm
with a decent pipe between sites i've copied over the network overnight, that was from UK to Arizona.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 8, 2015 at 4:16 am
Thank you all for your replies.
So i see that the option to Copy the database to the 3rd Party Center would be:
1. Copy the db to a disk and directly ship to them.
I am still exporing on the network connectivity, if that doesn't works then for sure this is the only option.
2. Copy it Over the Network (provided i have a connectivity between two datacenters).
With this, what options do we have with SQL Server to:
a). Split the back-up into multiple small files.
b). Compress these multiple files to as much possible.
c). Merge these split files at the 3rd party site and restore as a single DB.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply