1.3.4.4 Replication

Replication is a set of technologies used for copying and distributing data and database objects from one database to another and synchronising data between two databases to maintain consistency. The data can be distributed via local and wide area networks, dial-up connections, wireless connections, and the Internet. 

Three types of Replication are used to synchronise data across databases:
1. Snapshot replication: Snapshot replication copies data from one database to another or another database on the same server.
2. Merging Replication: Data from two or more databases are combined into a single database.
3. Transactional Replication: Users obtain a complete initial copy of the database and obtain periodic updates as changes are made.

Snapshot Replication
The following diagram illustrates a Snapshot Replication.

The Snapshot Replication is perhaps the simplest replication method to set up and is easy to understand.

•Snapshot Replication works by reading data in the Publisher database and creating files in the working folders on the Distributor, called the snapshot files.

•The Publisher periodically sends data in a bulk format to the Distributor.

•The Subscriber function in a “Read-only” environment can function without updates for a substantial period of time.

The Snapshot Replication is useful when handling static data in an environment that does not require data updates for a specific period. It is an ideal solution for replicating small volumes of data. 

Merging Replication

Merging Replication works by distributing data from Publisher to Subscribers, allowing both to update when connected. It will enable various sites to work autonomously and merge all updates into a single dataset.

The benefits of using Merging Replication are:

•Multiple Subscribers can update dates at various times and propagate the updates to the Publisher and other Subscribers.

•Subscribers can change data “Offline” and synchronise changed data to Publisher and other Subscribers.

•It prevents data conflicts when data is updated to multiple sites. 

Transactional Replication

Transactional Replication works opposite from Snapshot Replication:

•The Publisher sends the data (transaction) to Subscribers only when updates occur. The updates can be configured to accumulate the transactions and send the transactions at time intervals or send the marketing when updates occur.

•This Replication suits environments with low latency, high bandwidth and reliable network connections.

•Once the initial snapshot is copied, transactional Replication uses the Log Reader agent to read the Transaction Log of the Publisher database and store the transactions in the Distributor database. The Distributor agent would then transfer the transactions from Publisher to the Subscribers.