Data Replication with SQL Remote
PART 5. Appendix
APPENDIX A. Enterprise and Anywhere: Differences
If you wish to use SQL Remote for replication between Adaptive Server Enterprise databases, rather than with Adaptive Server Anywhere remote databases, you should be aware of the following limitations:
Database extraction The extraction utility creates RELOAD.SQL scripts and data files for building Adaptive Server Anywhere remote databases. Setting up remote ASE databases requires an extraction process created by the customer.
For more information about how to create an extraction process, see sp_remote procedure.
Referential integrity errors Referential integrity is always checked immediately in Adaptive Server Enterprise, while Adaptive Server Anywhere provides the WAIT_FOR_COMMIT option to control when integrity is checked. This presents difficulties when rows move between remote databases, as in territory realignment.
For example, suppose an Order table has a foreign key to a Customer table which has a foreign key to a SalesRep table. The Customer table is subscribed by sales rep. The Order table is also subscribed by sales rep (it has a redundant column maintained by a trigger).
When a row in Customer is updated to point to a new sales rep, a trigger fires to update the sales rep column in Order. The update on Customer is replicated as a delete to the old rep and an insert to the new rep. Similarly, the triggerred update on Order is replicated as a delete to the old rep and an insert to the new rep.
The problem occurs because SQL Remote replicates the operations in the order they occur, which means the Customer row is deleted before the Order rows. This causes a referential integrity error.
Schema upgrades Schema upgrades are difficult to manage when both consolidated and remote databases are Adaptive Server Enterprise databases. Passthrough to remote ASE databases is difficult to carry out.
The problem is due to the need for a quiet system for schema upgrades (see Differences in functionality). Passthrough puts schema upgrade statements into the normal message stream. The operations that precede the schema upgrade (in the same message or a previous message) cannot possibly have been scanned from the transaction log into the stable queue before the schema change takes place.
Synchronize subscription This is not implemented for Adaptive Server Enterprise remote databases.