SQL Replication

SQL replication enables administrators to distribute their data on the network or various other servers within an organization. There are many benefits of SQL replication, and some of these are listed below:

Load balancing : As mentioned above, SQL replication enables administrators to distribute data over the network or servers within an organization. It also distributes query load among those servers.

Offline processing : This feature allows you to modify data from your database on a machine that might not always be connected to the network.

Redundancy : SQL Replication stores your data on a failover database server which is ready to run anytime your original database goes down.

The two important components of any SQL replication are:

Publishers : Publishers have data that they distribute to other servers. Any given SQL replication scheme may contain one or more publishers.

Subscribers : Subscribers are database servers that receive updates from the publisher when data is manipulated.

Microsoft SQL Server supports three types of SQL replication. Let's take a brief look on each of them.

Snapshot Replication : The publisher takes a snapshot of the entire replicated database, and shares it with the subscribers. This can be both a time- and resource-consuming process. System administrators prefer not to use snapshot replication because of its recurring basis for databases that change frequently. More commonly, snapshot replication is used when databases are not expected to change much. Another usage could occur when you need to set a baseline to create replication between systems where future updates are propagated using transactional or merge replication.

Transactional Replication : Transactional replication is used when you need a more flexible solution for databases that change quite frequently. Here, the replication agent monitors the publisher for any changes in their database, and triggers the same in the subscriber database. This transmission can take place immediately or on a periodic basis.

Merge Replication : Merge replication enables both the publisher and subscriber to make changes on their respective databases independently. An active network connection is not required. When the connection becomes active, the merge replication agent checks for changes on both sides, and updates each database accordingly. If the changes conflict with each other, there is a predefined algorithm that is used to determine the appropriate data. Merge replication is generally utilized by laptop users, since they cannot be constantly connected to the publisher.