Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

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: SQL Having Clause Functions

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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: Difference Between SQLite and MySQL

SQL Server Replication Architecture – SQL Server Entities/Components

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.

SQL Server Replication components

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: How to Restore a Database Backup from SQL Server Management Studio

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.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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 [email protected]@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:

SQL Server Replication Agents

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: Top 50 Informatica Interview Questions and Answers

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: What is the Difference Between Control Flow & Data Flow in SSIS?

SQL Server Replication Architecture – SQL Server Replication Types

Here is a list of the SQL Server Replication types-

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.

SQL Server Replication Types

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: SQL Intersect Operator With Example

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: What are Data Types and Their Usage in SQL Server Tables?

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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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: SQL Server Analysis Services – All You Need to Know

SQL Tutorial Overview


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

2 days 05 Aug 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-0 day 03 Aug 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

14 days 17 Aug 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 07 Aug 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 04 Aug 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

4 days 07 Aug 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

2 days 05 Aug 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

11 days 14 Aug 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

4 days 07 Aug 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

2 days 05 Aug 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

13 days 16 Aug 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

4 days 07 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews