Sybase DC38133-01-0902-01 manual Limitations for Enterprise to Enterprise replication

Limitations for Enterprise to Enterprise replication

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” on page 424 .

♦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 triggered 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 Adaptive Server Enterprise databases is difficult to carry out.

The problem is due to the need for a quiet system for schema upgrades (see “Differences in functionality” on page 439 ). 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.

442