Shareplex can replicate sequences. Usually replication of sequences will be done if the target is for High-Availability. If target is used for reporting, you don't need to replicate sequences. Replication of sequences is not supported in peer to peer replication.
Shareplex replicates changes to sequences that are made during transactions and with the ALTER SEQUENCE command.
Replicating sequences adds overhead to overall replication. If you replicate sequences, use caching and set cache size to at least 20 or higher if sequences are frequently changed on source. When sequences are cached, Shareplex only replicates 'alter sequence' commands when cache value reached on source. When sequences are not cached, Shareplex must replicate 'alter sequence' command each time a sequence is obtained on source, which slows replication of more critical data. You can create a separate post queue to replicate sequences, this will help reduce performance impact on DML replication due to heavy sequence replication.
To illustrate how Shareplex replicates sequences, we create a sequence on source and target with defaults resulting in a cache value of 20 and increment by 1. Then we activate the config file.
The first time you select nexval from source after activation, Shareplex will replicate alter sequence on target and automatically increase target sequence by cache size. So on source you see 1 and on target you see 21. And next time you select nextval from 2 to 19, nothing gets changed on target (you will still see 21 on target). Until you select 21 on source, then it will again issue alter sequence on target by cache size to increase it to 41.
Therefore, anytime after failover the secondary is guaranteed to have a value higher than the one that was last selected on primary. There will be some gaps between the source and target sequences but the uniqueness is guaranteed.
If you plan on fail over from source to target and then from target back to source multiple times, it will be a good idea to activate config again after each failover so that the new target will have a higher sequence# than the source server.
