Database Replication
Database replication creates and maintains multiple copies of data to increase its accessibility and availability. The process typically copies data from a source database to one or more other databases while ensuring that all of the data is identical in all locations.
Why is Database Replication Important?
Having a single data source means having a single point of failure, which can put a business at risk. If the database becomes inaccessible or damaged, stored data will be lost. Periodic full backups can recover data to the time of the last backup, but changes made after the backup can be lost. Database replication eliminates this potential issue by maintaining multiple database copies for data protection.
Database replication can be used to maintain regional copies of selected data, such as local price lists, so stores or facilities have autonomy if headquarters systems are down for any reason. Conversely, data created at a regional level, such as sales performance, can be replicated in a central database for a global view.
Database Replication Use Cases
These are ways data replication can benefit organizations:
Local Data Protection
If there is a requirement for a second copy of a database in the same data center to support reporting users, for example, and to provide data protection against storage failures, a synchronized copy can be maintained. Each commit will be applied to both copies of the database simultaneously.
Geographic Data Distribution
In a retail scenario, each store may need a copy of the master product database schema. As changes are made to the master copy at headquarters, the regional databases receive updates asynchronously moments after the master copy is updated.
Physical Backups
A physical backup copy of a database begins with a full backup at the primary site. The archive site should be at a remote site far enough away to prevent the same natural disaster or power company outage impacting both locations. This secondary site maintains the standby database in recovery mode, applying archived database log files to the copy as they fill it out and get shipped to the standby site.
Logical Backups
Unlike the physical backup case, a logical backup is open and accessible to users with read-only access for reporting functions. Additional indexes can be added to support faster queries. As changes appear in the master database log files, they are automatically propagated to the secondary sites.
Mutual Standby
Database replication can be configured to have two servers act as standbys for each other. In this case, both servers are configured to have the capacity to handle the entire production workload. If either server fails, the remaining one becomes the primary server for the combined workload.
Delayed Replication
This unusual configuration has a secondary replicate of a database that waits an hour or two before applying changes. The reason for this is to protect against human error. For example, an administrator may accidentally drop the wrong tables or apply a mass change intended for a test database into a production system. This is known as a logical corruption that can be guarded against by having a delayed copy.
Multi-Master Replication
In a multi-master scenario, each database is a peer of the others. As changes come into one database, its peers receive the exact change via the replication software. In the case of a circular update or multiple updates to the same record, a rules-based system is required to intercept changes and only apply the one with the newest timestamp, for example.
Benefits of Database Replication
Below are some main reasons to replicate a database:
- In the case of network failure to a central system, regional copies of a database allow for local autonomy.
- Multiple database copies can help protect against disasters and cyber threats that target a single site.
- If a disk drive fails, in addition to a redundant array of independent disk (RAID) systems, a database copy will have its own disk arrays.
- Delayed copies of a database can help protect against human error. Some database systems have implemented a rewind capability for this.
Actian Data Platform and Replication
Actian Data Platform provides a unified experience for ingesting, transforming, analyzing, and storing data. Database replication is supported using the Change Data Capture bot from Actian Ingres and Actian Vector databases.
FAQ
Database replication creates live copies of data across multiple databases to improve availability and read scalability. Backups are point-in-time copies used for recovery. Replication supports near-real-time continuity and local access; backups are for long-term retention and disaster recovery restore points.
Synchronous replication guarantees that commits are persisted on replicas before returning success (strong durability) but increases transaction latency. Asynchronous replication reduces write latency and network load but can lose recent transactions on primary failure (higher RPO). Select based on required RPO/RTO and network characteristics.
Replicas in different racks, data centers, or regions provide failover targets, allow rapid role promotion, and maintain local autonomy during network partitions. Replication reduces recovery time (RTO) and can lower potential data loss (RPO) when configured appropriately.
Account for replication bandwidth, latency, and peak transactional throughput. Use change‑data‑capture or incremental shipping to reduce load, filter or prune replicated datasets where possible, and monitor replication lag to ensure consumers receive timely data.
Use replication that preserves transactional atomicity (log‑based CDC, WAL shipping or transaction-aware streaming). Coordinate schema changes, apply transactions in order, and validate consistency with checksums or periodic reconciliation jobs.
Encrypt data in transit and at rest, apply role‑based access controls, audit replication activity, and ensure replica locations comply with data residency and regulatory requirements. Mask or exclude sensitive fields from replicas used for reporting or development.
