How to drop a replicating sequence on source and target? (4301343)

Users may want to drop a replicating sequence. Since the DROP SEQUENCE operation is not a supported DDL for replicating sequences so it does not get replicated.  This solution delves on a fool-proof way to manually drop a replicating sequence on target after dropping it on source.

 General information.

The DROP SEQUENCE operation is not a supported DDL for replicating sequences so it does not get replicated.

As a workaround, carry out the following steps to drop the target sequence:

1. Ensure that the sequence is not being modified by users currently on source. This can be done by restricting the user session.

2. Issue a flush from source sp_ctrl:

sp_ctrl>flush o.SID  (Where SID represents the SID of the source database)

3. Drop sequence on source

4. Once Post stops due to flush, drop the sequence on the target.

If you do not want to go through the routine of quieting the source sequence, then you can simply drop the source sequence and give enough time for the messages from source to post to the target before manually dropping the target sequence.

For a list of supported and non-supported DDL, please refer to Shareplex Release Notes section titled "Supported and non-supported operations"