Oracle and DB2, Comparison and Compatibility/Database Scaling/Replication

One of the easiest ways of spreading data processing loads and providing a degree of fault tolerance is to simply copy the data in the database somewhere else and start another instance running against this copy. This strategy is called replication. It’s one of the simplest and most often implemented strategies. Both IBM and Oracle have a number of replication solutions. We are defining replication here as Master-Slave replication – where there is only one place that the data is updated (the master), and these changes are propagated to other instances (slaves). Master-Master replication (where any instance is capable of updating data) is covered later on under the topic of Shared Architectures, since the synchronization of multiple updates is critical in these systems, it is the processing functionality that is replicated, the data typically does not get copied elsewhere. In Master-Slave replication systems the replication can be either synchronous or asynchronous. The difference is simply the timing of propagation of changes. If the changes are made to the Master and Slave at the same time, it is said to be synchronous. If changes are queued up and written later, it is asynchronous.

There are a number of ways of propagating changes to the slave systems. In Trigger-Based replication, triggers on columns in the Master system will write changes to the Slave system. Trigger based replication can be synchronous or asynchronous.

Trigger Based Replication

Log Based Replication

In Log Based replication, the transaction log files are copied to another instance and replayed there. The log files are intended to reproduce a working version of the database in the event of a failure, so they represent an ideal mechanism for replicating data. In this instance the Log Files are streamed to a database instance that continuously recovers this data. Log based replication is only asynchronous.

Replication Uses

Data Replication is an easy way of providing data and system backups. Since the data being replicated will function as a database, replication is effectively a data backup. In Master-Slave systems, the Slave databases are read only. If the data is being replicated to a running database instance, it can provide a system backup in the event that the master becomes unavailable. This is called warm standby, and the slave system can be turned into a master by making it read/write. This is a simplistic description of warm standby, if your master becomes unavailable, there are probably other things that you will need to do to promote the slave to master besides just making it read/write.

Replicated databases are a good way of offloading database processing as well. The read only slaves can be used for reporting and DSS applications. In this way read only operations are offloaded to the slaves, freeing up processing cycles on the master.