Replication

DBMS
Overview | Methods | Snapshot | Continuous methods

Overview

Users need quick access to changes made to operational data on which they rely on to make their daily business decisions.

Decision suport systems (DSS) are typicaly concerned with read-only subsets of operational data. Data warehousing projects are concerned with capturing and transforming mainframe data before downloading to a LAN based warehouse.

Replication definitions vary and can mean everything from a snapshot copy of the database to two phase commit (transactions are applied to two independant databases)

Methods

Replication can be divided into two classes;

Batch. Typically involves data movement at a specific, typically scheduled time. Traditionally "batch snapshots" were made, where upload programs or utilities extract large subsets of data then manually transport them to the target database. Batch incremental (or change accumulation) is where changes are stored then periodically applied to the target(s) databases, and is commonly used to provide "change data capture" ina data warehouse.

Continuous. Provides users with real-time or near to real-time data, and normally deals with incremental changes. This type of replication is effective for distributed operation systems, DSS, sales force automation, and warm stand-by applications. Methods include two phase commit as well as asynchronous transaction replication (applied to two databases at the with a delay).Problems with synchronous methods is that the target database may not be willing to commit the transaction to its database (eg. conflicts with existing data).

Snapshot

Making a copy of the database or a subset, at regular intervals (daily, weekly or monthly).

dadbr01.jpg (19115 bytes)

Problems:
Once applied data becomes stale as soon as a new transaction is applied to the source database.
Can be costly and introduces long latency periods (when data is out of sync).
Normally the utility does not descriminate between existing data in the database and the changed/added data.
Advantage:
Data may be customised for the target database.

Examples include: BMC Software's UNLOAD PLUS, Platinum's Fast Unload.

Continuous methods

Trigger based

Rely on primary database "after" triggers to capture updates made to primary tables. An update triggers an additional update to a :"staging table" (a staging table is required for each table to be replicated). To maintain transaction integrity a related transaction table mapping the transaction operations is also maintained. Both tables usually reside in the primary database.

dadbr02.jpg (22942 bytes)

Disadvantage:
Additional overhead required by triggers and log tables.
Not optimal for high through put application, but can provide a reasonable latency period for many DSS applications.

Log exit based

A semi-intrusive methd where the update is captured while en route to the primary database or other internal process.

dadbr03.jpg (23884 bytes)

This log provides a one time snapshot of the changed record and must be stored until the replication system can guarantee the data has been successfully replicated to all downstream subscribers. Typicaly captured log records are stored in the primary database.
Examples include; Sybase' replication agent for Lotus Notes, IBM Data Propogator Relational (DPropR) for DBG2/MVS.

Log based

dadbr04.jpg (22012 bytes)

Advantages:
Non-intrusive, high performance replication system.
Typically are transaction based, allowing the transaction to be applied to the target databases.
Are transparent to the application.
Are asynchronous so provide a neglible impact to the primary operational (OLTP) system.