Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between those databases to help maintain consistency of the data. Using replication, you can distribute data to different locations and even to remote or mobile users over local and wide area networks, dial-up connections, wireless connections,or even the internet. 

There are three basic types of replication.

The first is transactional replication, and this is typically used in a server to server scenario that requires high throughput, including improving scalability and availability, and is potentially used for data warehousing and reporting scenarios as well. It’s great for integrating data from multiple sites or integrating heterogeneous data and offloading batch processing. 

The second type of replication is merge replication. This is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include exchanging data with mobile users, such as consumer point of sale applications and integration of data from multiple sites.

And finally there’s snapshot replication. It’s used to provide an initial set of data for your transactional or merge replication. It can also be used when complete refreshes of data are appropriate. 

Publication Types

Snapshot publication:
The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

Transactional publication:
The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Peer-to-Peer publication:
Peer-Peer publication enables multi-master replication. The publisher streams transactions to all the peers in the topology. All peer nodes can read and write changes and the changes are propagated to all the nodes in the topology.

Merge publication:
The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

Publisher and Subscriber

The publisher creates the initial data publication of which subscribers can subscribe to and either pull or receive push data. 

Sources:

https://www.linkedin.com/learning/microsoft-sql-server-2016-installation-and-administration/database-replication

Last modified: March 18, 2019

Author

Comments

Write a Reply or Comment