Mirror Replication Agent uses log-based replication. Modifications performed on replicated tables are logged in the database transaction log. ASE generates a log record for each modification to each affected row; a single DML statement may result in ASE generating multiple log records. Depending on the type of DML statement, the ASE may log one “before” image and one “after” image for every affected row. The Sybase Mirror 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 standby 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. Mirror 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
ASE cannot perform optimizations on the standby site that result in asymmetric loading of resources on the standby database.
Processing large numbers of statements affecting multiple rows increases latency in the system.
ASE 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
