Customers often ask "Can SharePlex do Bi-Directional Replication?", or, "Is SharePlex "Active-Active'?", or "Can SharePlex do Master-to-Master Replication?".
The simple answer to all of these questions is "yes"; but, we need to understand exactly what the customer needs.
As my colleague MIke Shurtz says, "SharePlex is all about minding your Ps and Qs" - Processes and Queues. SharePlex enables us to create a stream, or flow of data from a source to a target. Each SharePlex replication stream or instance, is controlled by a configuration file. Each configuration file has one and only one source, but can have multiple targets. Each SharePlex stream replicates in only one direction. One important thing to remember is that, except in some very special cases not applicable to this discussion. for each replication source, there is one and only one configuration file.
In some cases, when a customer asks "Does SharePlex support Active-Active replication?"; what they're really asking is, "Can I write to the target database or is it 'read-only'?". In this case, we're still really talking about one-way replication; the target is indeed fully open, and can be written to as any other database. Here, we'll have only one configuration file, on the source.
A customer might also ask, "Does SharePlex support "Bi-Directional replication?". Here, what they might really be asking is, "In a Disaster Recovery or High-Availability use case, after I've switched from the 'primary' database to the 'secondary', can I capture transactions on that database, and apply them back to the 'primary'?". Here again, we're still really talking about replication in only one direction at a time; so we'll still only have one configuration file, on whichever database is the source at the time. Part of the process to switch from primary to secondary would include deactivating the config file on the primary and activating one on the
In other cases, the questions above mean "Can I replicate from database A to database B and from database B back to database A; at the same time?". The answer here is a bit more complicated; but still, the basic answer is "yes". There are some configurations that need to be set, and specific circumstances that need to be considered. Also note that while we reference two databases in this blog, it's possible to replicate multiple ways between even more databases. One SharePlex customer, using SharePlex to ensure that master data is always available, actually has replication configured between 6 databases
First, we need to know if we need to replicate the same objects in both directions. In other words, do we need to replicate changes table One from database A to database B and at the same time, replicate any changes made to table One on database B back to database A; or, will the same table appear as a source table in the configuration files for database A and database B?
At the time the database setup is run (ora_setup or mss_setup) we need to tell SharePlex that the database being set up can be a master, or source database; by answering "Y" to the question "Will this database be used as a source?". If you answered "N" and want to change a database to be a source after the initial installation, you can always re-run the setup program, as long as SharePlex is not active. You'll want to deactivate any active config file and stop SP_COP before you re-run the setup.
Since we/re replicating in multiple directions, we'll need at least two configuration files, one for each direction. Then we need to consider exactly what sort of replication we need to accomplish, including what tables we want to replicate, and in which direction.
In the simplest case, we want to replicate one set of tables from A to B and a completely different set of tables from B to A. In this case, which will work for both Oracle and SQLSevrer sources and targets, all we need to do is set up and activate configuration files reflecting the desired replication on source (A to B) and target (B to A) and activate them. Since we're not replicating the same tables, there's no need to consider "circular" replication, where we need to prevent "endless loops" or "conflict resolution" where we need to decide "who wins" if the same same row is updated at the same time on both databases A and B.
In the more complicated use case, we want to replicate the same table from A to B and also from B to A. In this case, which is currently (as of SharePlex 9.1.3) only supported for Oracle to Oracle replication; there are two further considerations. First, we need to prevent "circular" replication, where change made to database A is replicated to database B, and then the same change is detected and replicated back to database A. If we don't prevent this, transactions would simply flow in an "endless loop" between the two databases. By default, SharePlex will ignore (not replicate) changes made to a database by the SharePlex Post Process. This is controlled by the SP_OCT_REPLICATE_POSTER parameter.
We also need to consider conflicts; that is, cases where a row is changed in database A, and then before that change is committed, the same row is changed on database B. This would normally result in an "out of sync" error. In some specific use cases, such as load balancing, where changes to specific rows can be controlled outside of SharePlex, conflict resolution may not be required. In cases where conflicts cannot be avoided tools outside SharePlex; SharePlex provides a set of PL/SQL functions that can be called to avoid the out of sync condition. The first function allows us to define a "trusted" database; so that in the event of a conflict, the change from the "trusted" database will be applied to both databases. The second function requires a column in the table that contains a timestamp that's updated at the time of the change. The timestamps from both databases are compared, and the change with the latest timestamp will be applied to both databases. When using timestamps, it's recommended that a time synchronization process, such as NTP (Network Time Protocol) be used to keep the database server clocks synchronized.
More information on Multi-Way replication can be found in the SharePlex Admiistration Guide and a set of demonstrations are outlined in the SharePlex Installation Guide. We'll also cover conflict resolution in more depth in a future blog.
Peer-to-Peer and Master-Master are synonyms for the Multi-Way replication use cases we defined above. We often find these terms used interchangeably.
I hope this blog helps clear up some of he mystery and confusion about Multi-Way replication and that you'll be able to try use them as required in your environment.