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 pricelists, 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.
The Actian Data Platform and Replication
The 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.