0

We currently have a SQL Server 2008 and SQL Server 2005 participating in one-way transactional replication of a single database.

If possible we would like to set this up as a Two-way replication. What would be the preferred method of replication to achieve this? There is a moderately high latency between these servers. However, the growth of the database to be replicated is minimal.

Improve this question
3
  • A few questions: - What editions are you using? - Is the amount of data updates minimal (aside from data file growth)? - How much sync lag time can you accept? - Will a given user session connect to and update only one of the replicas? - Is downtime for the changeover acceptable? - Can you freely modify the schema and code (or at least add NOT FOR REPLICATION settings and change Identity column properties)? - And finally not a question, but since you said "if possible we would like to..." you may want to read this and consider whether the initial and ongoing investment will be worth it: ...
    – T.H.
    Commented Feb 22, 2017 at 9:35
  • ... sqlblog.com/blogs/hilary_cotter/archive/2011/10/28/…
    – T.H.
    Commented Feb 22, 2017 at 9:35
  • Thanks TH will be sure to read over the reference, I would have to confirm the 2008 edition, 2005 is enterprise. Very minimal updates, very flexible on lag time, yes a given user session will update only one of the replicas, downtime is acceptable, schema and code changes are possible, only roadblock there will be my minimal experience and how much i can learn quickly. I suspect you correct in saying the investment may not be worth it for the end result, but will continue to research. Thanks again
    – Kye Davey
    Commented Feb 22, 2017 at 10:15

1 Answer 1

Reset to default
0

From your comment update, it sounds like Merge replication could work well for you, mostly due to the minimal updates. It's a lot simpler to set up and manage than two-way transactional (which is what the scary link I mentioned deals with--I should have left that for later).

In a nutshell: https://blogs.technet.microsoft.com/meamcs/2011/01/06/merge-replication-step-by-step/

Improve this answer
2
  • Thank you, does Merge replication require enterprise edition? Also, I can't find many cases discussed utilizing different versions (2005/2008) have you had any experience with this, any issues etc. Lastly, would there be any benefit from a replication perspective of upgrading the 2005 box to 2008?
    – Kye Davey
    Commented Feb 22, 2017 at 10:43
  • The publisher will need to be on 2008 Standard/Enterprise. The only cross-version issue I remember experiencing was that the 2008 UI would generate extra (and unneeded) 2008-only statements for schema modifications, that would stop replication to 2005 until dequeued. And of course trying to use actual new features can cause problems. If you have good monitoring and alerting in place you're probably fine, but consider the overview at msdn.microsoft.com/en-us/library/ms143241.aspx
    – T.H.
    Commented Feb 22, 2017 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.