How to run compare in a peer to peer (master-master) replication environment (4301605)

Compare command is designed to correct out of sync conditions on tables in a replication environment used for reporting. The reason being that when compare is run, it assumes that the target table will not be locked exclusively as it needs to lock target table during repair stage of compare. In peer to peer replication this may not work out as there is no such concept as source or target.

Compare will not work in a peer to peer replication environment.

One can use compare in a peer to peer replication environment so long as one node assumes the role of trusted source (primary) and the other one assumes the role of target (secondary). This needs to be done for the duration of compare. During this time the application need to point to primary and the secondary needs to be used for query purpose only. Also, one may want to ensure that all the messages from secondary to primary have successfully posted before issuing any compare. You can then issue compare from primary to secondary.  Once the compare finishes successfully, one can lift this restriction of using one as primary and the other as secondary and revert to peer to peer replication.

The following briefly describes what is meant by trusted source. In peer-to-peer replication, though the applications may simultaneously point to both nodes, in case of conflicts one node is treated as the true source for the resolution of conflict. So when running compare, one needs to issue the command from the trusted source to target.