Transaction replication systems read the transaction log of the primary database, convert the log records into equivalent SQL commands, and then apply the SQL to a standby database.
The main benefits of transaction replication are:
Data integrity (transactional consistency) between primary and standby databases is guaranteed.
Transaction replication is logical replication. It is based on the transaction log of the primary database, so it follows all of the data integrity “rules” of the database.
Standby databases are always available for decision support and reporting applications, with no downtime required by the transaction replication system.
Transaction replication requires the standby database to be continuously online, so it can apply replicated transactions as they arrive in the primary database log. Transaction replication does not require control of any database devices or log devices.
Availability of standby system resources increases the
ROI on both hardware and the DBMS software required to support disaster
recovery.
Standby system resources are hardware-independent because transaction replication is logical and not device-specific.
The standby site is protected from disk corruption because transaction replication is based on the primary database transaction log, which is managed at the device level by the primary DBMS.
Network bandwidth requirements are reduced because:
Transactions rolled back in the primary database do not need to be replicated.
Transaction replication can encode the replicated transactions and data in a more compact form than the actual log records or raw SQL. This results in more efficient network communication.
High network bandwidth can be a substantial operating
cost, so reducing the bandwidth requirement lowers the TCO of a
disaster recovery solution.
There are also some disadvantages of transaction replication when used alone for disaster recovery:
Disaster recovery is provided only for databases, and not for applications or other data stores at the primary site.
Transaction replication is asynchronous, so:
Network congestion can increase latency, which can increase the recovery time.
Latency is the time elapsed between committing a transaction at the primary database and committing that transaction at the standby database.
Some data loss can occur in certain situations.
If the connection to the primary site goes down after a transaction is committed, but before the replication system reads the primary log record, the transaction cannot be replicated.
