Database replication techniques

What is data replication in DBMS?

Data replication is the process of making multiple copies of the same data across different servers and databases to make the data storage system more available and reliable.

Copying data from one database on the primary server to a second (or more) database(s) on a secondary server introduces multiple copies of the same data or data redundancy. That in turn causes higher storage costs. But the benefits of data replications outweigh the disadvantages.

What are the advantages of data replication?

Data replication brings several benefits to data-driven businesses: 

1. Disaster recovery. From storms, fires, and floods whipping out your data centers to hackers locking you out of your data, multiple disasters can cause data loss. Creating backups allows you to recover from a multitude of worst-case scenarios. 
2. Improve data availability. System glitches and hardware malfunction can disrupt access to a specific database. Data replication enhances the availability of data by making resources accessible from multiple databases in case of malware or system breakdowns. 
3. Increase speed of data access. Companies operate around the globe and users access the company’s data and services. However, if your data is hosted on a server in North America, while your users are logging in from anywhere between Shanghai to Iceland, the users can experience service latency and delays due to the different locations of storage vs access. Keeping data replicated in multiple locations lowers the latency and improves the user experience.
4. Enhance server performance. Network performance deteriorates, when multiple users try to access the same database resources simultaneously. To remove the bandwidth bottleneck and improve the network load, companies deploy load balancing approaches, spitting and redirecting traffic to multiple servers. Data replication guarantees each server has a copy of the sought-after data, keeping your server performance high while also servicing the data needs. 
   

How does data replication work?

The specifics of how data replication works will depend heavily on the database or data warehouse you are trying to replicate.

For example, PostgreSQL implements a master-replica server-to-server architecture for replication with write-ahead logs (WALs), while MySQL is known for its binary log file position-based replication. The details might differ from database to database, but the general process is the same.

The data replication process can be split into a series of steps:

1. Identify what is the data source and where you want it replicated (server and location wise).
2. Determine which files, folders, and applications you want to be replicated.
3. Pick the type of replication needed for your business needs (more below).
4. Determine the replication schemes: how often you want the data to be replicated (schedule vs real-time) and in what way (in batches or bulk).
5. Program or better use a tool to set up data replication.
6. Monitor the backup process to ensure data is replicated consistently. 

To better understand the high-level decisions needed, let us take a look at the different types of data replication. 

What are the types of data replication?

Depending on your business and data objectives, you might implement different types of data replication: 

1. Full-table replication. Full-table replication copies everything from the source storage to the destination storage: every new row, updated row, and already existing row. The obvious disadvantage is that copying everything puts a higher burden on your network when transferring data and can cause delays in replication if the data volume is high. But unlike key-based replication (below), full-table replication can also replicate hard-deletes. This is important if your app implements hard delete operations (viz-a-viz soft delete operations, such as noting a field as “archived” or moving soft-deleted data to a different table). 
2. Snapshot replication. Snapshot replication takes a “snapshot” of the source database at the time of replication and replicates that data in all destination databases. It does not concern itself with changes to the data (new, updated, deleted), so it is faster than full-table replication, but does not keep records of hard deleted data.
3. Merge replication. Merge replication is one of the most complex types of replication techniques. It merges two or more databases into a single database. This architectural pattern is useful when your data producers are distributed across multiple sources, such as apps on users' mobile phones. But in merge replication both changes to local and merged data can be committed and synchronized, so every user has access to the data. 
4. Key-based incremental replication. Key-based incremental replication is one of the most favored types. Before replication begins, the replication process scans keys (or indexes in a DBMS) and checks which ones have changed (deleted, new, updated). The process then replicates only the relevant replication keys, making the entire backup much faster. But remember, hard deletes cannot be replicated from source to destination due to the architectural design. 
5. Transactional replication. Transactional replication first copies all existing data from source to replicas. Then, with each new transaction in the source, the same transaction is executed in the replicas, guaranteeing transactional consistency. A shortcoming of this approach is that given the locking mechanisms of RDBMs, the replicas can be mostly used for read operations, and not create, update, or delete operations. 
6. Log-based incremental replication. Sometimes transactional operations are implemented as log-based incremental replication, other times log-based replication is a standalone solution. In the log-based incremental implementation, the log files of the source database are scanned, the change data capture approach identifies, which rows have changed, then the same changes are implemented in the destination database. The advantage of this approach is similar to transactional replication: faster (copy only changes), consistent, and reliable (change transactions are atomic, even in the event of a short circuit, the systems will be synchronized). The shortcoming is that log-based replication can be technically extremely challenging if you implement it on your own.
   

The different types of replications depend on your business use cases (read vs write queries on replicas, speed of replication, need for eventual consistency vs immediate consistency, etc.). But there are other considerations you need to keep in mind when designing your data replication system. 

Data replication schemes

There are three separate questions you need to answer when setting up your data replication schemes:

1. What is the extent of the data replication? Sometimes all the data needs to be replicated, in which case you need full replication. Other times there are just some aspects of the database you need to be replicated, so you pick partial replication, which just takes fragments of the data and replicates those.
2. How frequently does the replication need to be? When scheduling data replication it is important to determine if replication needs to be synchronous (updated in near real-time, but with higher costs) or asynchronous (updated on a schedule - cheaper - but with the tradeoff of partial inconsistencies between updates).
3. Do you want incremental or bulk replication? Do you need to move all your data in bulk (consistency consideration, which raises transference costs) or can it be done incrementally in batches? 

Understanding your data replication scheme will help you to better design the replication process or even better, pick the right software tool to help you replicate data.

How can Keboola help you with data replication?

Keboola is an end-to-end data operations platform that has built-in tools to automate data replication without overhead:

1. Back up your tables by simply clicking the “Snapshot” button. 
2. Schedule backups regularly. 
3. Use the time-travel feature to look at the changes on your table and restore a previous version with a simple click.
4. Use database extractors to collect data from your source and save it to the replica destination of your choice with a couple of clicks. The extractors already offer out-of-the-box log-based replication for MySQL or key-based replication, simply pick the type that best serves your purpose.
   

Keboola also comes with many out-of-the-box tools for data operatives automating and easing work along data pipelines end-to-end:

1. Automatically extract data from multiple data sources - from 3rd Party Apps like Facebook ads to data warehouses like Amazon Redshift. 
2. Automate your data cleaning and transformations to save precious time while keeping data clean and validated.
3. Use machine learning tools to construct state-of-the-art AI models.
4. Integrate Keboola with dashboarding software to always have the latest reports as soon as new data becomes available.
5. Schedule end-to-end pipelines to run on their own with Keboola’s Orchestrators.
   

Try it for yourself. Keboola offers a no-questions-asked, always-free tier, so you can play around and tap into the potential of automating your data replication and operations.