database replication

Contributor(s): Jack Vaughan

Database replication is the frequent electronic copying of data from a database in one computer or server to a database in another so that all users share the same level of information. The result is a distributed database in which users can quickly access data relevant to their tasks without interfering with the work of others. Numerous elements contribute to the overall process of creating and managing database replication.

Database replication techniques

The classic case of database replication is found in applications that connect a primary storage location and a secondary location that is often off site. While fast transactions and queries are a major reason to employ database replication, having multiple copies of databases is also valuable for testing and operational reporting.

Overall, distributed database management systems (DDBMS) work to ensure that changes, additions and deletions performed on the data at any given location are automatically reflected in the data stored at all the other locations.

In this video, Scott Golightly walks through a
transactional replication setup for SQL Server.
Such methods help ensure up-to-date data is
available in multiple locations.

Early instances of database replication were typically described as master-slave configurations, but comparable descriptions today tend to have database replication described using master-replica, leader-follower, primary-secondary, server-client and other terminology.

While remote office database replication may have been the canonical example of replication for many years, fail-safe and fault-tolerant database backup schemes have also arisen as drivers of replication activity, as have horizontally scaling distributed database configurations on premises and on cloud computing platforms. Replication details vary between such relational systems as IBM DB2, Microsoft SQL Server, Sybase, MySQL and PostgreSQL.

Evolution of database replication

Replication techniques centered on relational database management systems have expanded with the advent of the virtual machine and distributed cloud computing to include non-relational database types. Again, replication methods vary among such non-relational databases as Redis, MongoDB and the like.

In all cases, data replication design becomes a balancing act between system performance and data consistency. Database replication can be done in at least three different ways. In snapshot replication, data on one server is simply copied to another server or to another database on the same server; in merging replication, data from two or more databases is combined into a single database; and, in transactional replication, user systems receive full initial copies of the database and then receive periodic updates as data changes.

Database replication vs. mirroring

In relational database mirroring, complete backups of databases are maintained for use in the case that the primary database fails. Mirrors, in effect, serve as hot standby databases. While data mirroring is sometimes positioned as an alternative approach to data replication, it is actually a form of data replication. Data mirroring has found considerable use within the Microsoft SQL Server community.

database replication diagram
Early database architectures became stressed as systems were accessed by remote user sites. Performance and availability issues were often acute. In response, data replication schemes arose, supporting multiple database copies in multiple locations. Variations on this architecture have found use for disaster recovery and cloud computing, as well.

With database replication, the focus is usually on database scale out for queries, while database mirroring, in which log extracts form the basis for incremental database updates from the principal server, is typically implemented to provide hot standby or disaster recovery capabilities.

Database replication tools

Companies can either use the database replication tool available offered by their database software provider or invest in third-party replication tools to execute and manage database replication processes. The latter option allows flexibility in that third-party tools are typically vendor-agnostic and can be used to create data replicas across multiple types of databases in an organization.

Third-party database replication tools that work with various databases include Attunity Replicate, Informatica Data Replication, Talend Open Studio for Data Integration, Quest SharePlex and others.

Examples of database vendor replication tools include Microsoft's SQL integration features, Oracle GoldenGate and IBM'S DB2 SQL replication tool.

This was last updated in June 2018

Continue Reading About database replication

Dig Deeper on Database management system (DBMS) software and technology

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

How do you anticipate replication approaches will change as distributed data applications proliferate in your organization?
Thank you, Margaret! 
Very nicely comprehended about Database Replication


File Extensions and File Formats