If the performance of sequence replication is a concern, there are other alternatives to replication that support primary and standby use of the same sequence. The following alternatives are currently suggested by Oracle and others who are interested in providing sequence coordination between multiple sites:
Assuming the sequence is being used to generate primary key values, the sequence at each site can be concatenated with something unique to the site. For example, use a sequence number concatenated with the database name, site name, or something similar. This technique allows each site to maintain a unique “range” of sequence of numbers; as a result, there would be no value in sending (replicating) changes of one site's range to another site.
Similar to concatenating, each site can obtain a different range of numbers by having different starting points, or increment values, for the same sequence. For example, the sequence at one site can start at 1 and increment by 2 to generate odd numbers (1, 3, 5), while the other site starts at 2 and generates even numbers (2, 4, 6). Again, each site would have a unique range and avoid any need for replication.
A third option is available to standby solutions, where the standby site is for read-only and does not access the sequence value until failover. Rather than continually being replicated, the value of the sequence at the standby site can be updated as part of the failover tasks. After failover and before the standby allows connection to client applications, a script or procedure can query the last-used sequence value (based on the last table to use it for a primary key) and update or redefine the sequence once, based on that calculated value
For additional information for sequence replication, see the Sybase Replication Agent Primary Database Guide.
