What is Peer-to-Peer (or active to active) replication?
Peer-to-Peer replication (P2P) allows users on multiple databases to make changes concurrently to the same data, while SharePlex replication keeps all of the databases synchronized. Peer-to-Peer replication is not appropriate for all replication environments as it requires a major commitment on the application and database design that may not be practical when packaged applications are in use. Before the latest SharePlex 8.6.4 version, P2P required custom PL/SQL conflict resolution procedures to resolve the conflict when more than one user changed the same record at, or near, the same time. In SharePlex 8.6.4, pre-built conflict resolutions with host based priority and time-based priority are available for customers to use in P2P environment.
Challenges in Peer-to-Peer replication
Peer-to-peer replication represents unique challenges not found in single-direction replication such as replication for reporting purposes. In those configurations, users are not allowed on the secondary system to perform DML changes on replicated objects. In peer-to-peer replication, however, different users all make DML changes to copies of the same tables in different databases, usually on different systems, while SharePlex keeps them all current through replication. Conflict can occur for many reasons in a Peer-to-Peer environment.
A conflict is defined as an out-of-sync condition and conflict resolution routines are called upon by SharePlex post process to resolve the conflict.
Conflict can occur for following reasons:
- Users on both servers inserting a row with same key values which can result in unique constraint violation.
- Conflict occurs when an UPDATE is performed on the source system, but the pre-image of the changed column from source does not match that of the target row as it has been modified by users on the target system.
- Users on both servers deleting a row with the same key value, out of sync is detected when post is trying to delete a row which no longer exists.
Peer-to-Peer Requirements
To successfully deploy SharePlex in a peer-to-peer configuration, you must be able to:
- Isolate Keys - All tables that require conflict resolution must have Primary Keys and they must be unique among all databases
SharePlex depends on unique, not NULL keys in the SQL statement in order to locate the correct row in the target table for UPDATE and DELETE statements, and to ensure that a row being inserted is not already there.
The primary key must be unique among all the databases in the peer-to-peer replication network, meaning
- It must use the same column(s) in each corresponding table in all databases
- Key columns for corresponding rows must have the same values.
- Prevent changes to keys
Because SharePlex locates rows on the target system by using the key, it can not provide conflict resolution when one or more columns of a key are changed. Peer-to-peer replication would not be practical in environments where key values are changed.
Primary Key value also can not be deleted and inserted again with the same value in a peer to peer environment.
It gets more complicated when you have a table with primary key and unique key on different columns. Conflict may occur on either primary key or unique key columns. Custom conflict resolution may need to be used to handle conflict on unique key columns.
- Use of sequences
Sequences help ensure the uniqueness in the primary key. However, SharePlex does not support peer-to-peer replication of sequences. If you are using sequences to generate all or part of a key, then there must be no chance for the same range of values to be generated on any other system in a peer-to-peer configuration. For example, you can use odd number on one system and even number on the other system. - Control trigger usage
DML changes resulting from triggers firing on the source are replicated and posted to the target database by SharePlex. Consequently if the same triggers fire on the target system, they return out-of-sync errors. If there are row-level triggers on replicated tables, you need to run sp_add_triggers.sql under $SP_SYS_PRODDIR/util to add wrapper ‘when user !=’SPLEX’’ so that triggers will not fire for SharePlex post process. If there are statement level triggers, you need to manually modify those triggers so that they don’t fire if user is SPLEX. - Delete Cascade
When delete cascade FK constraints fire from the original location, SharePlex replicates both the original delete on the primary table and the delete cascade on the child table. If the primary delete activates the FK delete cascade on the target, those child records will not be there when SharePlex expects to perform the delete on the child table thus returning out-of-sync error. In SharePlex 8.6.3 and later, you can set SP_OPO_DEPENDENCY_CHECK to 2 which will not report out of sync when delete cascades are involved. - Supplemental logging
Supplemental logging must be enabled on primary key, unique key, and foreign keys on all databases involved in replication.
Things to consider in Peer-to-Peer environment
- Does application maintain inventory quantities or account balances?
Application that uses UPDATE statements to record changes in quantity, such as inventory or account balances, poses a challenge for peer-to-peer replication. This requires custom conflict resolution procedure to be written to handle such scenario. - Deciding conflict resolution priority method – Host based vs Time based
For High Availability set up, host based priority makes more sense and source server where users connected to will be considered the trusted host (preferred host). When conflict occurs, changes from the trusted host will take priority and overwrite changes from the secondary server.
For peer to peer replication, most customers use time-based priority method. All tables that need time-based conflict resolution must have a timestamp column (ideally the same column name used for all tables), such timestamp column should always be populated during insert or update so it can be used for comparison to determine priority. It should not be part of the key columns. You can choose to use earliest timestamp as winner for insert, update, and delete or you can choose latest timestamp as winner. You can even use different timestamp method for different operation type such as earliest for insert but latest for update. - Minimize conflict through application design
For performance reason and ease of maintenance, your application should be designed in a way that will generate the least amount of conflict. Common methods would be to direct users in one geographic location to one server and users in another location to another server. Use odd or even sequence number on each server to avoid insert unique constraint violations. - Synchronize the system clock with same time zone format such as GMT across all servers involved in replication.
- Custom pl/sql conflict resolution does not support tables with LONG, LONG RAW, RAW or LOB columns due to PL/SQL limitations of 32K size. Default pre-built conflict resolution has no such limitation.
- Resync method – As peer to peer may not have a trusted host if using time-based, you can run compare with option ‘log split’ which will generate sql files with insert, update or delete in separate files. Evaluate data to see if you want to run the insert sql file on the target. You probably do not want to run the delete sql file as those maybe the records that you are missing on this source master server.
- Ensure network bandwidth and speed are sufficient among the replication servers as any network glitches may affect replication.
Setting up Conflict Resolution with default pre-built CR routines
For simple conflict resolution requirement
- Check out the following Knowledge Based Article on how to set up Host Based conflict resolution
https://support.quest.com/shareplex/kb/205846 - Check out the following Knowledge Based Article on how to set up Time Based conflict resolution
https://support.quest.com/shareplex/kb/208803
Logging for details on default conflict resolution is now available in SharePlex 8.6.6. If you have more complex conflict resolution requirement, please contact your account representative to engage professional services consulting services to create customized conflict resolution procedures.
