4

My current setup requires me to use three separate SQL Server 2012 instances (installed on 3 separate machines, separated geographically) and have the data be replicated across all 3. Any changes made in the 7 tables within my database need to be replicated based on where the change was made, for example, if I add a record on Server A, it needs to be added on Server B and Server C. If a record is added on Server B, it needs to be added on Server A and Server C. Same thing with Server C.

From what I've read, Bidirectional Transactional Replication seems to be the best avenue for me to use in SQL Server 2012 Standard. The issue that I'm having is that no one has a very good guide to Bidirectional Transactional Replication that really explains much. I think I've poured over Hilary Cotter and Microsoft's information a million times and I still can't seem to get it to work. The fluff data that they provide in examples just isn't practical and no one explains how to do it between two physical machines; they assume you're using the same machine and just replicating between another instance.

Am I doing something wrong or am I going about this the wrong way?

Improve this question
2
  • 2
    Are you getting any error messages, or other pertinent details? What is or isn't happening? What do you have setup so far?
    – Hannah Vernon ♦
    Mar 15, 2013 at 20:14
  • So far I've only been trying to get Server A to talk to Server B and vice-versa. I've setup Server A as a distributor, setup a publisher, added articles, and done the same for Server B. I then added a subscription with @loopback_detection = true for Server A and did the same for Server B. Then I get a whole stream of "Replication-Replication Distribution Subsystem: agent SERVER\INSTANCE failed. Cannot drop the table 'dbo.table' because it is being used for replication." errors anytime I try to make a change on Server A to see if it makes the change on Server B.
    – t3hn4t3
    Mar 15, 2013 at 20:26

1 Answer 1

Reset to default
1

I had a long answer written on stackoverflow, but then you deleted the question about the same time I was ready to post it. I am not going to put the same effort in again, but in short:

  1. Check out the stairway to replication: http://www.sqlservercentral.com/stairway/72401/ It does not cover bidirectional transactional replication, but it covers the permissions involved in setting up transactional replication. That should allow you to figure out the bidirectional setup as well.
  2. You might want to check out merge replication instead. It was designed for multi-node bidirectional synchronization of data and offers nice conflict resolution handling. However it adds a guid column to each table.
Improve this answer
2
  • Sorry about that. Another user suggested that I posted in the wrong section so I quickly deleted it and moved my question over here. Thank you for the response!
    – t3hn4t3
    Mar 16, 2013 at 17:44
  • 1
    @Sebastian, it would be really nice if you have posted your answer completely since it is a common question. Also, thanks for the current answer
    – Ashkan S
    Aug 2, 2016 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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