SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implementing DR solution using Merge Replication


Implementing DR solution using Merge Replication

Author
Message
AmolNaik
AmolNaik
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1234
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
David Benoit
David Benoit
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12742 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
AmolNaik
AmolNaik
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1234
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 Smile

Thanks again.
Amol

Amol Naik
AmolNaik
AmolNaik
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1234
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search