Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Are you getting any error messages, or other pertinent details? What is or isn't happening? What do you have setup so far? –  Max Vernon Mar 15 '13 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 '13 at 20:26
add comment

1 Answer

up vote 0 down vote accepted

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.
share|improve this answer
    
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 '13 at 17:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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