How DBSynchronizer Works
You install DBSynchronizer as an AsyncEventListener on multiple data stores, preferably two. DBSynchronizer can be installed only on data stores (configured with host-data property set to "true").
Ensuring High Availability and Reliable Delivery
Configure the DBSynchronizer queue for both persistence and redundancy to ensure high availability and reliable delivery of events.
GemFire XD installs a DBSynchronizer instance on each datastore in the target server group you specify in the CREATE ASYNCEVENTLISTENER command. Installing DBSynchronizer on more than one data store provides high availability. At any given time only one member has a DBSynchronizer thread active for executing DML on the external database. (The first data store started in the DBSynchronizer server group starts the active instance.) The threads on other members are on standby (redundant) to guarantee execution of DML if the member with the active DBSynchronizer thread fails. As a best practice, install no more than two standby DBSynchronizer instances (redundancy of at most two) for performance and memory reasons.
By default, any pending DML operations residing in the internal queue of the DBSynchronizer are lost if the active member shuts down. You can avoid losing operations by configuring the internal queue of DBSynchronizer to be persistent.
How Failover and Upgrades Affect Synchronization
A DML operation may be reapplied to the RDBMS if the member with the active DBSynchronizer thread fails. If the member with the active DBSynchronizer fails while sending a batch of operations, some DML statements in the batch may already have been applied to the RDBMS. On failover, the new DBSynchronizer thread resends the failed batch and reapplies the initial DML operations. When this occurs, the RDBMS may get out-of-synch depending upon the nature of the DML operation, how it modifies table columns, and the presence or absence of column constraints.
A create operation that is reapplied to a table with a primary key. A primary key constraint violation occurs and a SQLException is thrown, but DBSynchronizer ignores the exception.
A create or update operation that causes a unique constraint violation. Reapplying a create or update operation causes a duplicate value, violating the unique constraint. DBSynchronizer ignores the SQLException that is thrown in this situation.
A create or update operation that causes a check constraint violation. Reapplying create or an update (for example, incrementing or decrementing a column value) may cause a check constraint to be violated. DBSynchronizer ignores the SQLException that is thrown in this situation.
In the preceding cases, the constraints prevent the tables from going out of synch with the GemFire XD data.
Reapplying certain update operations (such as update T1 set Col1 = 5 where col2 =7) and delete operations (such as delete from T1 where col1 = 5) do not cause out-of-synch problems.
Create operations on a table without a primary key constraint.
In this case, reapplying a create operation creates additional rows.
Update operations that modify the column value relative to its current value.
Reapplying update operations such as update T1 set col1 = col1 +? where col2 = ? cause the external database to go out-of-synch with the GemFire XD data.