Replicating LARGE database over WAN
-
Hi everyone,
I'm very new to SQL Server replication. I have 3 databases with total size of 150GB. I need to replicate these databases from LA to SF. What is the best way of doing this if time is of concern? Thanks in advance for your help.
-
Would this scenario work?
Replicate from Server A to Server B locally. Detach the replicated database and send it to the remote office and set up replication there?
Sorry if my ideas are wacky, I'm very new to this stuff.
-
What exactly do you need to do? Replication may not be the best solution if you want to keep an entire database synchronised across locations, or if you just want an occasional copy of it.
Also, does the SF copy need to be writable? If so, do those updates need to be applied back to the LA database?
-
Hi Matt,
What we want to do is have a copy of our databases in SF in case there is some emergency in LA. We have a window of 1 week to bring up the databases in SF in case of an emergency. The SF databases don't need to be writable.
I am not sure, but am I mistaken in what I have studied about replication so far? I am thinking that the only way to replicate the databases to SF is by replicating the entire 150GB+ data across our WAN. Is there some other way of getting the initial snapshot to SF and then start the replication of the interim transactions once that's done?
Again, sorry for my questions sound dumb. I'm studying replication as fast as I can (as much as work would allow me...unfortunately this week is SUPER busy and this replication thing needs to be done by next week at the very latest).
Thanks again!
-
If it's purely for backup purposes, you may want to look at log shipping instead - I suspect it would be a much simpler solution for you to implement. I'd recommend reading about it in BOL (http://msdn2.microsoft.com/en-us/library/ms190016.aspx) to ensure that it meets your requirements.
-
Thanks Matt.
I just looked into log shipping, but I read that it is being phased out in the future releases of SQL Server. I'm currently looking into mirroring, which will replace log shipping. Have you had any experience with mirroring. Would you recommend it for what we want to do (vs. replications)?
-
bottom line is you are going to have a very fast network in between. if something happens and you have to recreate replication or log shipping or whatever you will need a lot of bandwidth
you are better looking at a SAN solution and doing hardware replication between the two sites
-
I would like know where you read that log shipping is being phased out, because either somebody needs to be corrected in a big way, or you missed the point of the article. "Log shipping" is just another way of saying "Backing up a database and then continuously restoring it somewhere else".
Backup and restore will be with us for a long time. Some log-shipping wizards or built-in stuff may not have more development going against them in the future, but:
1. Those tools are all pretty mature, work well, and need little help, and
2. If you're doing anything important (such as log-shipping an important database offsite for DR), you should dump the GUI and script it yourself anyway, so have have total control and can make the scripts meet you explicit requirements in your environment. We log-ship several high-transaction multi-terabyte databases for DR, and hand-wrote the scripts and jobs ourselves.
-Eddie
Eddie Wuerch
MCM: SQL -
I pretty much agree with Eddie, except that I'd add that the SSMS log shipping wizard is a good starting point - it will create basic scripts for you to modify according to your needs.
Asynchronous mirroring is possibly an option but I've only used synchronous and I'm pretty certain that latency would prevent it from working well in your situation.
-
Thanks for your inputs everyone. I'm currently testing mirroring to see how it will work for us.
As far as where I read that log shipping will be phased out...I read it in the book "SQL Server 2005 Unleashed". It says "Log shipping is still a feature for SQL Sever 2005, but it will be deprecated by the enxt release. In other words, it might be easy to use and easy to manage, but it is being phased out as a feature of SQL Sever.... For those who have current log shipping configurations, it is time to move to databse mirroring. This will be an easy transition because the two capabilities are so much alike."
-
If you have a whole week to get if from LA to SF, back it up on tape in LA, ship the tape to SF overnight express, and restore it in SF. It should take a lot less than one week.
The data transfer rate of a FedEx package is very high.
-
Michael Valentine Jones (3/26/2008)
If you have a whole week to get if from LA to SF, back it up on tape in LA, ship the tape to SF overnight express, and restore it in SF. It should take a lot less than one week.The data transfer rate of a FedEx package is very high.
Thanks!? 😀
-
1) Log shipping will die a horrible death the first time you do an index maintenance on a fragmented database.
2) If you DO go with log shipping, roll your own and use a backup compression engine to backup/restore - or zip/unzip native backups yourself before sending them over the WAN.
3) Best mechanism for doing WAN disaster recovery site is probably a third party package, which is compliant with the special requirements of SQL Server, that does block level synchronization. Can get costly though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
What about Mirroring (If you have SQL2005)?
-
Mirroring suffers from the same crippling amount of a deltas that index maintenance can cause and in addition cannot be compressed either automatically or manually. This is rectified in SQL 2008, with built-in compression of the data flow between primary and secondary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply