What is SQL Server Replication and How it Works?

Introduction

Traditionally, applications were using a standalone  environment where a centralized server used to respond to multiple users and locations. The common issues reported by this centralized approach included  performance issues, availability issues, and maintenance issues. To overcome all these problems, the concept of replication is used. 

Replication allows using multiple copies of data at different locations together. Log shipping and mirroring usually result in database redundancy whereas replication allows maintaining selected parts of the database or a set of needed objects at different locations. Modifications made at different locations are synchronized later to the main server. In this way, it maintains high availability features when needed.

Read: Which SQL Server Role You Need to Practice for the Highest Salary in 2020?

According to experts, replication is a set of technologies to copy and distribute data and database objects from one database to another and maintain synchronization among databases to regulate the consistency. Like other replication techniques, SQL server replication does not distribute the entire database, but it distributes selected parts of the database only like tables or views, etc. SQL replication is quite useful and you must know how to do it right. 

Here in this blog, we shall discuss the basics of SQL Server Replication.

What are the Benefits of SQL Server Replication?

Here are some advantages of using SQL Server Replication-

• It improves performance and reduces locking conflicts among users while they are working from different locations.
• It improves the availability and easy to maintain as compared to other databases.
• It allows the site to work independently. Each site can set up its own rules and procedures to work with its own copy of the data.
• It helps to move data closer to the user.

In SQL Server 2005, replication was performed to introduce the custom business logic into the synchronization process. It allows web synchronization options to replicate data over the HTTP. In SQL Server 2005, replication had to be stopped to perform some actions like adding nodes, performing schema changes, etc. The SQL Server 2008 allows performing all these actions online. In SQL Server 2012, replication features were stronger and improved over time.

Read: MySQL vs SQL Server vs Oracle: A Detailed Comparison

SQL Server Replication Architecture – SQL Server Entities/Components

SQL Server Replication is based on the “Publish & Subscribe” metaphor. Let us discuss each SQL entity or SQL component in detail below.

Article

An article is the basic unit of SQL Server consisting of tables, views, and stored procedures. With a filter option, the article in the SQL replication process can be scaled either vertically or horizontally. It is possible to create multiple articles on the same object with certain limitations or restrictions.

With the help of the New Publication Wizard, you can navigate the properties of an article and set permissions when needed. You can set permissions at the time of publication as well, and these are read-only permissions only.

SELECT
     Pub.[publication]    [PublicationName]
    ,Art.[publisher_db]   [DatabaseName]
    ,Art.[article]        [Article Name]
    ,Art.[source_owner]   [Schema]
    ,Art.[source_object]  [Object]
FROM
    [distribution].[dbo].[MSarticles]  Art
    INNER JOIN [distribution].[dbo].[MSpublications] Pub
        ON Art.[publication_id] = Pub.[publication_id]
ORDER BY
    Pub.[publication], Art.[article]

Once the article has been created successfully and you want to change some properties, then a new replication snapshot should be generated. If the article has one or more subscriptions, then all of them should be reinitialized independently. To list all the articles in SQL Server publication, you can use the following commands.

Read: What Is Average Salary Of Database Admin In Philippine?

Publication

A publication is the logical collection of articles within a database. It allows us to define and configure articles’ properties at a higher level so that they can be inherited from to other articles in the group. An article cannot be distributed independently, but it needs publication.

EXEC sp_helppublication;

Publisher

This is the source database where SQL replication starts and makes data available for the replication. Publishers define what they publish through a publication. A publisher can have one or more publications where each publisher defines a data propagation mechanism by creating multiple replications stored procedures together.

USE Distribution 
GO 
select * from MSpublications

Distributor

A distributor is a storehouse for replication data associated with one or more publishers. In a few cases, it acts as the publisher and distributor both. In the case of SQL Server replication, it can be termed as the local distributor. If it is distributed on a different server, then it is termed as the remote distributor. Each publisher is associated with a distribution database and a distributor.

The distribution database identifies and stores the SQL replication status data, publication metadata and acts as a queue sometimes to move the data from Publisher to Subscribers. Based on the replication model, the distributor is responsible for notifying Subscriber that the user has subscribed to a publication and article properties are changed. Also, distribution databases help to maintain data integrity. Each distributor should have a distribution database consisting of article details, replication metadata, and the data. One distributor can have multiple distribution databases. However, all publications defined on a single Publisher should use the same distribution database. Here is the command to check whether a database is a distributor or not.

SELECT @@ServerName Servername, case when is_distributor=1 then 'Yes' else 'No' end status FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;

Here is the command to check either a distribution database is installed or not.

SELECT name FROM sys.databases WHERE is_distributor = 1

Here is the command to check either a publisher is using Distributor or not.

EXEC sp_get_distributor

Highlights

• The Distributor acts as the mediator between a Publisher and the Subscriber.
• It receives snapshots or published transactions and stores or forwards these publications to Subscribers.
• It includes a set of 6 system databases including Distribution Database.

Subscriber

This is the destination database where replication ends. A subscriber can subscribe to  multiple publications from multiple publishers. A subscriber can send back the data to the publisher and publish data to other subscribers based on the replication model and the design.

EXEC sp_helpsubscriberinfo;

Subscriptions

Pull Subscriptions	Push Subscriptions
This subscription is created at the Subscriber server.	This subscription is created at the Publisher server.
In this subscription, the subscriber initiates the replication instead of a publisher.	In this subscription, the publisher is responsible for updating all changes to the subscriber.

Take our Live Training on SQL Server and master the concepts of SQL with ease!

SQL Server Replication Architecture – SQL Server Replication Agents

The replication process works in the background with the help of jobs, and these jobs are termed as agents as well. The information of agents is generally present in the distribution database. The replication agents in SQL Server are divided into five major categories:

Snapshot Agent

• It is an executable file that helps in preparing snapshot files that contain schema and published table data and database objects.
• It usually stores data in the snapshot folder and records the synchronized jobs in a distributed database.

Distribution Agent

• It is mainly used with transactional and snapshot replication.
• It applies the snapshot to the subscriber and moves transactions from the distribution database to subscribers.
• It runs at the distributor to push subscriptions, or it runs at the Subscriber to pull subscriptions.

Log Reader Agent

• It is used with the transactional replication that moves transactions that you want to replicate from the transaction log on the publisher and the distributed database.
• Each database has its person Log Reader Agent that runs on the distributor and is able to connect with the Publisher.

Read: How to Ace Your Microsoft Power BI Certification Exam

Merge Agent

• It is used with the snapshot file at the beginner level and transfers incremental data changes that happen.
• Each merge subscription has a personal Merge Agent that could connect with both Publisher and the Subscriber.
• It can capture changes with the help of triggers.

Queue Reader Agent

• It is used with transactional replication along with the queued update option.
• It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
• In the case of Queue Reader Agent, only one instance exists to service all publications and publishers for an assigned distribution database.

Read: SQL Server Views - Everything You Should Know

SQL Server Replication Architecture – SQL Server Replication Types

Here is a list of the SQL Server Replication types-

Snapshot Replication

• The snapshot replication is used to provide an initial set of database objects for merge or transaction publications.
• It can copy and distribute database objects in the same manner as they appear at the current moment.
• It is used to give an initial set of data for merge and transactional replication.
• It is used when data scenarios and data refreshes are appropriate.
• It is used when data is not changing frequently.
• It is used to replicate a small amount of the data.
• It is used to replicate lookup tables that don’t change frequently.
• It keeps data copies of data for a certain time as mentioned by developers.

For example, if there is one product Company that is changing the price of its whole products frequently once or twice a year, replicating the complete snapshot of data, in this case, is highly recommended.

How does snapshot replication work?

• The snapshot agent maintains a connection from the distributor to the publisher and creates fresh snapshots into the snapshot folder by placing locks.
• In the next step, the snapshot agent writes a copy of the table schema to each of the articles.
• It copies data from the published table and writes data to the snapshot folder in the form of the .bcp file.
• Append rows and release locks on published tables.

In simple words, snapshot replication behaves the same way as the name suggests. The publisher simply takes the snapshot of the entire database and shares it with subscribers change. This is basically a resource-intensive process and doesn’t use by administrators frequently for databases that frequently. It is mostly used in two scenarios:

1. First, it is used for databases that change rarely.
2. Secondly, it acts as the baseline to establish replication among systems while future updates can be propagated using merge or transactional replication.

Read: Windows SQL Function-All you Need to Know

Transactional Replication

SQL Server Transactional  replication offers a more flexible solution for databases that change frequently. Here, the replication agent monitors the publisher for database changes and transmit those changes to the subscribers. You can schedule these transmissions in a transactional replication either periodically or regularly.

Merge Replication

This replication allows publishers and subscribers to make changes to the database individually. It is possible to work together for both entities without any network connection. When they have connected again, the merge replication agent checks both entities for changes and modifies the database accordingly.

If there are some conflicts in changes, then the agent uses one predefined conflict resolution algorithm to check on the appropriate data. This type of replication is mainly used by laptop users and others who are not continually connected to the publisher.

Read: Online SQL Queries for Practice Questions with Answers

Each of the replication types has its own benefits and needs. They are suitable for different database scenarios. You can choose any one of them based on your replication needs. It is clear at this point that the SQL Server replication process offers database administrator a powerful tool for managing or scaling databases in an enterprise environment.

Where SQL Server Replication goes Wrong?

Think for a while if there are five active subscribers who are allowed to make changes to database copies and send the same to the publisher. Subscriber A makes some changes and forwards the same to the publisher. Publisher will update changes to the database, and same changes are reflected by other subscribers as well B, C, D, and E. In the meantime, other subscribers are also making their own changes and sending transactions to the publisher.

For a complex or bust database application, it will create traffic immediately even if your network links are up to the date. You are exponentially increasing the workload on each SQL server, and each server system must keep changes and maintain the same on other servers too. At the same time, the publisher is also consolidating changes.

Why don’t you take a free demo of our online SQL Server Training to assess the high level of remote training that we provide? Sign up here and book your spot!

Final Words:

Replication is a magical process, but it does not work well everywhere. You have to figure out scenarios where SQL server replication can deliver the best performance. 

Once you have read this blog thoroughly, you will understand the much needed basics of SQL Server Replication.

When it is integrated with more powerful cloud platforms like Microsoft Azure or AWS, SQL Server Replication  becomes stronger and designs the best business solutions. If you are still confused, join our SQL certification program and understand practical aspects of the database and how to use them for tough business scenarios.

Read: How to Restore a Database Backup from SQL Server Management Studio

FaceBook Twitter LinkedIn Pinterest Email