Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implementing DR solution using Merge Replication Expand / Collapse
Author
Message
Posted Tuesday, February 3, 2009 7:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Hello Gurus,

As a DBA in my organization i have been asked to design and implement a second data center from a database perspective.
Here's what the management wants to achieve:

1) We currently have a Data Center A in West Coast.
2) Build a second data center "Data Center B" at the East Coast. This will act as the DR Site.
3) Users in East Coast will hit the East Coast database server (Data Center B) and similarly users at the west coast will hit the Data Center A.
3) The data between the first and second data center needs to be synced on a continous manner and hence we are planning to use Merge Replication. Our database tables already has GUID's as PK's.

So they want to implement DR solution as well as DB Server Load Balancing. Is this the right way to do this? Or is there an alternative to what my company wants to achieve, like using Log Shipping etc.

Any inputs, suggestions or ideas will be greatly appreciated.

Thanks in advance,
Amol


Amol Naik
Post #649481
Posted Tuesday, February 3, 2009 8:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:09 AM
Points: 2,049, Visits: 3,585
You seem to be on the right track and merge replication would certainly cover what you are trying to do. You might also look at peer-to-peer transactional replication but you will need to run your requirements through the recommended topology to see if there is a fit there.

A couple of thoughts, in no order of importance to consider;

1. Due to distance you are likely going to see some latency on the replicated transactions. You will have to determine if that is acceptable. It sounds like it will be based on what you are discussing doing.
2. Being that this is DR / Active, you will need to determine how much data loss is acceptable AND how long you have to get a site back online to service all customers.
3. You will need to consider what objects need to stay in sync. If there is general web site traffic information that may be used for analytics and reporting but is not used for actual business matters then you may not want to replicate that and take up potential bandwidth for other replicated transactions.

These are just some cursory thoughts and I'm sure there are other things to consider. I will try to think on this a bit more and post some more as time permits.

Oh yeah, log shipping will not work in the situation that you describe. If you were speaking of a strictly passive DR site then that would work but you are looking for both your main and your DR site to be servicing requests so, that won't fly.

Hope this helps.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #649499
Posted Tuesday, February 3, 2009 9:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Thanks David for the response. Now with that i am going to go back to the management and let them know about the potential data integrity issues that we might forsee with the Merge replication approach.

Meanwhile i'll wait for more of your valuable advice :)

Thanks again.
Amol


Amol Naik
Post #649523
Posted Wednesday, June 3, 2009 12:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Experts,

Reviving this discussion after quite sometime. Per manangement we want to go for a pure DR solution now. So i am considering the option of either going for Log Shipping or Database Mirroring solution.

Which of the above is better, reliable and easier to configure in case the primary data center fails?

Thanks,
Amol


Amol Naik
Post #728430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse