Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

What is SQL Server Replication and How it Works?

Traditionally, applications were using standalone environment where a centralized server used to respond to multiple users and locations. The common issues reported by centralized approach include performance issues, availability issues, and maintenance issues. To overcome all these problems, we can use replication.

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 part 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.

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.

What are the Benefits?

  • 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 site 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. SQL Server Curriculum

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 1). Article An article is the basic unit of SQL Server consist of tables, views, and stored procedures. With a filter option, the article can be scaled either vertically or horizontally. It is possible creating 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.

Once the article has been created successfully and you want to change some properties, then 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: Difference Between Clustered and Non-Clustered Index in the SQL

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]

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


EXEC sp_helppublication;

3). Publisher This is the source database where 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

4). 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. SQL Server quiz Each distributor should have a distribution database consists 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 either a database is 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 snapshot or published transactions and stores or forwards these publications to Subscribers.
  • It includes a set of 6 system databases including Distribution Database.

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

Read: SQL Operators you Need to Know

EXEC sp_helpsubscriberinfo;

6). Subscriptions It is a request made by the subscriber to receive a publication. Here are two types of subscriptions – Pull Subscriptions and Push Subscriptions.

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

Replication Architecture – SQL Server Replication Agents

The replication process works in the background with the help of jibs, 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

a). 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.

b). Distribution Agent

  • It is majorly 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.

c). Log Reader Agent

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

d). 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.

e). 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.

Replication Architecture – SQL Server Replication Types

SQL Server Replication Types 1). 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 who 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 the copy of 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 share 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.

2). Transactional Replication

This 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 either periodically or regularly.

3). 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 majorly used by laptop users and others who are not continually connected to the publisher.

Read: SQL Fiddle: The Best Resource to Practice SQL online

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 SQL server replication process offer database administrator a powerful tool for managing or scaling databases in an enterprise environment. free SQL Server demo

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 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 in consolidating changes.

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

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

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

Upcoming Class

4 days 24 Nov 2019

DevOps

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

Upcoming Class

5 days 25 Nov 2019

Data Science

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

Upcoming Class

5 days 25 Nov 2019

Hadoop

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

Upcoming Class

6 days 26 Nov 2019

Salesforce

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

Upcoming Class

14 days 04 Dec 2019

Course for testing

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

Upcoming Class

34 days 24 Dec 2019

QA

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

Upcoming Class

13 days 03 Dec 2019

Business Analyst

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

Upcoming Class

5 days 25 Nov 2019

SQL Server

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

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews