Sybase replication technology is log-based. Modifications performed on replicated tables are logged in the database transaction log. Adaptive Server generates a log record for each modification to each affected row; a single DML statement may result in Adaptive Server generating multiple log records. Depending on the type of DML statement, the Adaptive Server may log one “before” image and one “after” image for every affected row. The Sybase Replication Agent reads the log and forwards it to the Replication Server. The Replication Server identifies the DML operation (insert, delete, update, insert, select, or stored procedure execution) and generates the corresponding SQL statement for every operation.
Log-based replication has these inherent issues:
When a single DML statement affects multiple rows, Replication Server applies multiple DML statements on the replicate site, not just the single original DML statement. For instance, if table t is replicated:
1> delete tbl where c < 4 2> go (3 rows affected)
The delete statement logs three records in the transaction log, one for each of the rows deleted. These log records are used for database recovery and replication. Replication Agent sends the information pertaining to the three log records to the Replication Server, which converts the information back into three delete statements:
delete t where c = 1 delete t where c = 2 delete t where c = 3
Adaptive Server cannot perform optimizations on the replicate site that result in asymmetric loading of resources on the replicate database.
Processing large numbers of statements affecting multiple rows increases latency in the system.
Adaptive Server only partially logs information about select into; therefore, the replication system cannot successfully replicate the DML command.
There are two different approaches to address all of these issues:
Stored procedure replication
SQL statement replication
