SQL Replication - SQL Tutorial

SQL Replication term describes a group of technologies allowing information distribution and mirroring between different databases. SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few.

Why use SQL Replication?

There are many reasons why SQL replication exists. In many scenarios replication is done to ensure redundancy. For example if a business runs a mission-critical database, it may be wise to have a replication of this database on a separate physical database server, which can take over in case of failure in the primary database (software corruption, hardware failure, etc.). Another valid reason to use SQL replication is load balancing. By using replication you can share the access load between several database server having identical databases. There are many other reason to use SQL replication, however we won’t discuss them here.

Replication Publisher and Subscriber

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.

MS SQL Server Replication

MS SQL Server has 3 main replication models – Merge Replication, Transactional Replication and Snapshot Replication.

The Merge replication is used when both Publisher and Subscriber need to make changes to their respective databases. In this case both databases might have been changed between runs of the Merge SQL replication, and the replication merges the changes in both locations. Of course when using merge replication you should be aware that there might be conflicts, for example duplicated primary keys. If there is a conflict, then the merge replication follows predetermined conflict resolution plan to correct the issue.

The Transactional replication is usually used with databases where data changes frequently and there’s need for constant refreshing of the data. The replication process watches the publisher’s database for any changes, if there are changes, it distributes them over to the replication subscribers.

The Snapshot replication is relatively rarely used, and as its name implies it does a snapshot of the publisher database at certain point of time and distributes it to the subscribers. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other 2 SQL replication types after the initial copy.