Making and keeping copies of a database on other servers is known as database replication. It improves availability, provides redundancy, and can help distribute read workloads across multiple servers.
- Improves availability and resilience: Multiple copies of data allow the system to continue serving requests if a server fails and a replica can take over. However, replication is not a replacement for backups because data corruption or accidental deletion can also be replicated.
- Supports read scaling: Read requests can be distributed across replicas, reducing the load on the primary database and improving performance for read-heavy applications.
Example: An e-commerce website may use a primary database for write operations and multiple replica databases to handle read requests from users.
Working
These steps explaining how database replication works:
- Identify the Primary Database (Source): A primary (master) database is selected as the main source where all data changes originate.
- Set Up Replica Databases (Targets): One or more replica databases are configured to receive data from the primary database.
- Capture Data Changes: All inserts, updates, and deletes are recorded using logs or change data capture mechanisms.
- Transmit Changes to Replicas: Captured changes are sent to replica databases either in real-time or at scheduled intervals.
- Apply Changes on Replicas: Replica databases apply the received updates to stay synchronized with the primary database.
- Monitor Synchronization: The system continuously checks replication status and resolves delays or synchronization issues.
- Handle Read/Write Operations: Read operations may be distributed across replicas, while write operations typically go to the primary database in primary-replica configurations. The exact read/write behavior depends on the replication model and database configuration.
Types of Database Replication
Understand the different types of database replication:
1. Primary-Replica Replication
In this replication model, one database acts as the primary server while others maintain copies of its data.
- The master database handles all write operations such as insert, update, and delete.
- Replica databases replicate data from the primary and may handle read operations.
2. Master-Master Replication / Multi-Master Replication
In this setup, multiple databases act as masters and can accept both read and write operations.
- Changes made in one master database are replicated to the other master databases.
- Can support distributed write operations and improve availability, but requires mechanisms for handling conflicts and maintaining consistency.
3. Snapshot Replication
This method replicates the entire database by taking a snapshot at a specific point in time.
- A complete copy of the database is created and transferred to other servers.
- Suitable when periodic copies of data are sufficient and near-real-time replication is not required.
4. Transactional Replication
Transactional replication synchronizes databases by replicating changes as they occur.
- Changes made in the publisher database are quickly sent to subscriber databases.
- Replicates changes with low latency, but the exact consistency and replication delay depend on the implementation and configuration.
5. Merge Replication
Merge replication allows multiple databases to update data independently and later synchronize the changes.
- Both publisher and subscriber databases can make modifications to the data.
- Conflicts are detected and resolved during the synchronization process.
Strategies
Database replication strategies determine how to select data, copy and distribute it between databases to gain specific goals such as scalability, availability, and efficiency.
- Full Replication: Also referred to as full database replication, this is a technique in which the whole database is replicated to one or more destination servers. All the tables, rows, and columns in the database are copied to the destination servers. The replicas thus obtain an exact copy of the original database.
- Partial Replication: This method involves not replicating the entire database, but merely a subset of it, such as particular tables, rows, or columns. This method can be useful when only specific data has to be reproduced for reporting, analysis, or other reasons, and it enables a more effective use of resources.
- Selective Replication: It is a database replication strategy that involves replicating data based on predefined criteria or conditions. Unlike full replication, which replicates the entire database, or partial replication, which replicates a subset of the database, selective replication allows for more granular control over which data is replicated.
- Hybrid Replication: It is a database replication strategy that combines multiple replication techniques to achieve specific goals. This approach allows for the customization of replication methods based on the requirements of different parts of the database or application.
Configurations
To accomplish particular objectives related to data consistency, availability, and performance, database replication can be set up and run in a variety of ways:
1. Synchronous Replication Configuration
In synchronous replication, the transaction waits for the required replication acknowledgments before it is considered committed.
- The transaction is committed only after at least one replica confirms that it has received the update.
- Can provide strong consistency guarantees when configured to wait for the required replicas, although the exact behavior depends on the database and replication configuration.
2. Asynchronous Replication Configuration
In asynchronous replication, the primary database sends updates to replicas without waiting for confirmation.
- The primary database does not wait for replicas to acknowledge the update before completing the transaction, which can reduce write latency.
- Replicas may receive updates with a slight delay, which can lead to temporary data inconsistency.
3. Semi-synchronous Replication Configuration
Semi-synchronous replication is a hybrid approach that combines features of synchronous and asynchronous replication.
- The primary database waits for confirmation from at least one replica before committing the transaction.
- Other replicas receive updates asynchronously, allowing some replicas to acknowledge updates synchronously while other replicas may receive updates asynchronously.
Importance
Database replication is important for several reasons:
- High Availability: Replication can improve availability by providing additional copies of data that can be used after a server failure. Automatic failover, health checks, traffic routing, and recovery mechanisms are also required to achieve high availability.
- Disaster Recovery: Replication can support disaster recovery by maintaining copies of data on separate systems or locations. However, replication is not a replacement for backups because data corruption, accidental deletion, or incorrect updates can also be replicated.
- Load Balancing: Replication can allow read requests to be distributed across replica servers, reducing read load on the primary database when the application and database support read routing.
- Fault Tolerance: Replication can improve fault tolerance by providing additional database copies. Failover and traffic-routing mechanisms are required to shift requests to another replica when a server fails.
- Scalability: Replication can improve read scalability by distributing read traffic across multiple replicas, particularly for read-heavy workloads.
- Data Locality: Replicas can be placed closer to users or application regions to reduce read latency and improve performance for geographically distributed applications.
Challenges
Some of the challenges with Database Replication are:
- Data Consistency: It can be difficult to maintain consistency among replicas, particularly in asynchronous replication situations where data replication may be delayed.
- Complexity: System complexity is increased by database replication, which requires thorough setup and administration to guarantee accurate and effective data replication.
- Cost: Setting up and maintaining a replicated database environment can be costly, especially for large-scale deployments with multiple replicas.
- Conflict Resolution: When the same data is changed on multiple replicas at once in multi-master replication environments, conflicts might arise that require conflict resolution techniques.
- Latency: Synchronous replication can introduce additional latency because the transaction may need to wait for acknowledgments from the required replicas before committing.
