freshidea - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

How does three-way bidirectional transactional replication work?

Bidirectional replication of SQL servers is a business continuity tool for users with multiple remote sites and a central data center.

Bidirectional transactional replication in a Microsoft environment refers to a multimaster replication topology that is sometimes used with SQL Server.

Large organizations may occasionally need to create scale-out databases so duplicate copies of data reside in multiple places for performance or business continuity purposes.

While this goal could seemingly be accomplished with traditional unidirectional replication, such an architecture would be unlikely to meet the needs of a multi-facility organization. In such a situation, an employee in a branch office would be able to read data from a local database server, but write operations would have to be redirected to a central database server in the main office.

Three-way bidirectional transactional replication eliminates this problem by creating a multimaster architecture that allows write operations to be performed against any of the database copies.

For this type of replication to work, the server at the main office is designated as a central subscriber. The branch office database servers subscribe to the central subscriber. If an update to a database record is made at a branch office, the update is sent to the central subscriber, which then replicates the change to the remaining database server.

Although bidirectional transactional replication generally works well, conflicts occasionally occur. For instance, a conflict can arise if a record is inserted into a key table on one server, but another server already has a record that uses the same key.

Another way in which a conflict can occur is if a record is updated at the same time on two different servers. This holds true even if the update was made to different columns within the record. Fortunately, these conflicts can be prevented programmatically. Microsoft provides a list of suggested actions.

Next Steps

When to consider bidirectional replication for disaster recovery

Replication as part of a backup/DR strategy

Dig Deeper on Disaster recovery planning - management

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

What conflicts have you experienced with bidirectional transactional replication and how did you work around them?
Because of the nature of our data, and the relatively high rate of updates that come in to our database environment, we were experiencing a relatively large number of instances where records were being updates almost simultaneously. Some of that was solved by modifying the stored update procedures in the member servers, and the rest was more or less taken care of once we implemented XCALL to evaluate each record before it was updated.
Yes, before using a system like this I'd certainly want to know how it handles conflicts and backs out changes. Similarly, what happens if there's some sort of failure when one system is written to and the other one isn't? How are the multiple systems coordinated?