Bidirectional and Multi-Directional Replication

Bidirectional and Multi-Directional Replication

What is Bidirectional Replication?

Bidirectional or Multi-directional replication is basically 2 or more independent databases that hold data that is consistent amongst each other. In other words they contain the same data such that the same application can run in either one of these servers and expect to retrieve correct consistent data.

It is worthwhile noting that bidirectional replication is different from two phase commits or distributed transactions. Such transactions are not committed until all the databases are updated. This requires that all the databases are active and available or the transaction fails.

In bidirectional replication the transaction for that database completes with that database. This transaction is then captured and forward and delivered into another database and there will always be an inherent lag. If the server(s) for both (or more) databases have constant communication this is considered as real time replication.

What are the characteristics of a Bidirectional Replication setup?

- There are no master databases. Every update by the application on any of the databases is replicated across to all databases. Unlike a one way disaster recovery replication where the application only updates one database, a bidirectional setup can have the application updating either one of the databases. Therefore neither database holds the true copy. Both collectively combine holds the true state of the database. That is every database has to be correct.

- The same application can be serviced from any database and the results are expected to be consistent. The view from the user is as if there is only one database.

- Every database is usually on an independent server or platform and maybe geographically different.

Why have bidirectional replications?

The most common reason for bidirectional replication is the ability to fully utilize multiple servers and still provide disaster recovery or fallback. This departs from the traditional production and hot standby setups where a near identical production ready server is available and ready just in case the production fails or needs to be made inactive. A bidirectional setup thus reduces cost and has the advantage of both databases in constant production used.

Ok insert your reasons here and brain storm to see if bidirectional is really what you need. Cost should not be the main factor.

Some notes before starting a project for bidirectional replication

- The first and foremost is to determine the exact business requirements.

- Keep it simple. Bidirectional replication can get very complicated very quickly if there are many conflicts to resolve between the two (or more) databases. Identical rows can be updated at the same time. Columns containing dollar values can be subtracted or added concurrently.

For example if the brief is disaster recovery for 10 applications then a simple way to have bidirectional replication is have 5 applications running on server A and 5 running on server B with the data replicated across to the other server. The database has essentially a bidirectional replication with server B backing up applications for server A and vice versa. In the event of a catastrophic failure on server A, all applications can be switched to running on server B. This way the conflicts are few if any.

- It can’t be emphasized enough that conflict resolution is the most important step to get right. It is inherit that conflicts occur. In most setups there will be identical rows that are updated concurrently or within the lag time between data capture and data delivery. Understanding the nature of this and formulating a workable solution is crucial. No software or product can handle something that is simply impossible. Therefore your business team and development needs to get together and work out what should happen for each and every activity that can result in a conflict.

For example a human resource application adds the same employee on both databases. A conflict will occur when data is delivered to the corresponding databases. So what should be the outcome? Assuming that a replication is automatically going to handle this is incorrect. It will never know what to do; you need to tell it how. Does earlier transactions take precedence or should server A always the one to overrule. Your business team needs to decide.

Summary

- If physics can’t handle it, no product can handle it. Get your rules down.

- KISS (Keep It Simple Supervisor, ok this is a G rated blog!)

Surprise! I have not talk about Oracle GoldenGate. In most ways the product is the least of your worry although OGG will be able to interpret all physics friendly rules. Time travelling or the chicken before the egg is not supported.