SQL Server Replication

SQL Server Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations. Unlike other methods of high availability, it doesn’t distribute entire database, but only distributes some part of database like tables, views or stored procedures

There are four types of replication

  • Transactional replication
  • Peer to peer replication
  • Snapshot replication
  • Merge replication
  1. Transactional replication
    • It replicates each transaction for the article being published
    • When a transaction is written to the transaction log, the Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber
    • Only committed transactions are replicated to ensure data consistency
    • Transactional replication is widely applied where high latency is not allowed, such as an OLTP system for a bank or a stock trading firm, because you always need real-time updates of cash or stocks

Log Reader Agent

  • The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database.
  • Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor
  • Push or Pull
  • Push – a push subscription pushes data from publisher to the subscriber
  • Pull – a pull subscription requests changes from the Publisher.  This allows the subscriber to pull data as needed.  This is useful for disconnected machines such as notebook computers that are not always connected

Distribution Agent

  • The Distribution Agent is used with snapshot replication and transactional replication.
  • It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers.
  • The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions

2. Peer-to-Peer Replication

  • Peer to Peer replication is the process of having multiple servers that act as both publisher and subscriber with its own distributor. As such, when you update, insert or delete records, they are synchronized in real time with each other.  The topology (architecture) of the design involves a master server from which other servers connect to the master database. 
  • These server are referred to as nodes.
  • If any one of the locations is down, the other locations can still stay synchronized, because each node acts as a publisher and a subscriber.
  • Backup and restore database to each server in replication is needed
  • Enable distributor in each server that is in replication
--sp_removedbreplication
use master
go

create database PTP
go


use ptp
go

create table Computers
(Computerid int primary key,
Name varchar (20))

use PTP
go

insert into Computers
values (1,'Sony'),(2,'HP'),(3,'Dell'),(4,'Apple')

select * from Computers

use PTP
go

insert into Computers
values (6,'Lenova')
  1. Snapshot replication:
  • As the name suggests, a snapshot replication takes a snapshot of the published objects and applies it to a subscriber
  • Primarily used for fairly static data such as data warehouse or when it’s acceptable to have data that does not need to by constantly synchronized
  • A subscriber does not always need to be connected, so data marked for replication can be applied the next time the subscriber is connected
  • list of items that only changes periodically as in data warehousing Replication agents

Snapshot Agent

  • Used with all types of replication
  • It prepares the schema of published tables and objects
  • Stores the snapshot files
  • Records information about synchronization in the distribution database
  • Snapshot Agent runs at the Distributor

Push or Pull

  • Push – a push subscription pushes data from publisher to the subscriber
  • Pull – a pull subscription requests changes from the Publisher.  This allows the subscriber to pull data as needed.  This is useful for disconnected machines such as notebook computers that are not always connected

Distribution Agent

  • The Distribution Agent is used with snapshot replication and transactional replication.
  • It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers.
  • The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions

4. Merge replication

  • This is the most complex types of replication which allows changes to happen at both the publisher and subscriber. 
  • As the name implies, changes are merged to keep data consistency and a uniform set of data.
  • Just like transactional replication, an initial synchronization is done by applying snapshot.
  • When a transaction occurs at the Publisher or Subscriber, the change is written to change tracking tables. The Merge Agent checks these tracking tables and sends the transaction to the distribution database where it gets propagated. 
  • The merge agent has the capability of resolving conflicts that occur during data synchronization. 
  • An example of using merge replication can be a store with many branches where products may be centrally stored in inventory.
  • As the overall inventory is reduced it is propagated to the other stores to keep the databases synchronized.

Some applications also require that changes flow from the Subscriber back to the Publisher. merge replication provide options for these types of applications.

  • Merge Agent – The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.
  • Queue Reader Agent – The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

DMV’s and their use?

  • sys.dm_repl_articles – Contains information about each article being published. It returns data from the database being published and returns a row for each object being published in each article.
  • sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published
  • sys.dm_repl_traninfo – Contains information about each transaction in a transactional replication

This guide will cover

  1. Initial SQL replication setup
  2. Pre-requisites
  3. How to prepare the replication snapshot folder
  4. How to configure a SQL replication distributor
  5. How to create a SQL replication publisher
  6. How to create a SQL replication subscriber

Pre-requisites

  1. The account must at minimum be a member of the db_owner fixed database role in the SQL replication Publisher, Distributor and Subscriber databases
  2. For securing the replication snapshot folder using a Snapshot Agent, the account must have read and write or modify permission on the replication snapshot share
  3. At least one database should have an article and must possess Primary Key; a basic rule that every article should have a Primary Key is considered as best candidate for Transactional SQL Replication. The primary key is used to maintain uniqueness of records.
  4. Scheduling the agent and jobs
  5. Sufficient network bandwidth
  6. Enough disk space for the databases being published; we need to make sure that we have enough space available for the SQL transaction log for the published database

Initial setup

The exercises demonstrate how to configure SQL Replication to copy a few tables, stored procedures and views from AdventureWorks2016 sample database to another database. To keep this setup simple, we use SQL Server Management Studio. The transactional replication setup has a single SQL Server instance to play the roles of SQL replication Publisher, Distributor, and another SQL Server instance play the role of the SQL replication Subscriber.

Getting Started

To first set up transaction SQL replication, you must configure the SQL replication Distributor and create a SQL replication Publication, replication snapshot folder and a SQL replication Subscription.

Configure Distributor

The following steps walk you through the process of creating the SQL replication Distributor:

Open SSMS and connect to the SQL Server instance

In Object Explorer, browse to the replication folder, right-click the Replication folder, and click Configure Distribution

The first page of the Distribution Configuration Wizard appears. The pages outline the general details about configuration distributor aka Configure Distribution Wizard

On the Distributor page, you’ve an option to choose to set up the current instance to be a Distributor or select another instance that’s already been configured as a Distributor. In this case, the distributor is local so leave the default setting “‘ServerName’ will act as its own Distributor;SQL Server will create a distribution database .

and log” and Click Next

Next, in the configure Snapshot Folder, type in the path of the SQL replication snapshot folder or leave the default path of the SQL replication snapshot folder. The snapshot folder is used for initial data synchronization of transactional replication and make sure it is large enough to hold all the replicated data. In this case, I’ll leave the default values and Click Next.

Now, configure the SQL replication distribution database. Specify the name of the distribution database and the folders where the data and log files should be located. Again, no change in the default values is required. Click Next.

In the Publishers page, specify the SQL replication Publishers that are going to access the Distributor and Click Next

In Wizard Actions, You’ve an option to run immediately or create a script that can be executed at a later time and Click Next

In the complete the wizard page, Review the settings and configuration options, and then click Finish to enable

Now, for Configuring… the following page appears and it shows the progress of the distributor setup.

Configure Publisher

Once you’ve configured the Distributor, you can create a publication. Let’s follow the steps:

  1. In Object Explorer, locate the Replication folder, right-click Local Publication, and then click New Publication
  2. Next, the New Publication Wizard appears and outlines the general information about creating Publication.
  3. In the Publication Database page, select the Adventureworks2016 database and click Next.
  4. On the Publication Type page, select Transactional publication, and click Next.
  5. Now, on the Articles page, choose the articles should be part of this publication
  6. Once you’re done with the object selection, Check the Show only checked articles in the list option to list candidates of Publication. In this case, 2 tables, 1 Stored Procedures are selected. default values.
  7. Next, in the Filter Table Rows page, define filters that should be applied to your articles. Let us go with the
  8. In the Snapshot Agent page specify when to run the Snapshot Agent. It can be run immediately or it can schedule to run at a later time. In this case, Create a snapshot immediately is used.
  9. Now, in the Agent Security page, specify the account to use to run the Snapshot Agent using Security settings…
  10. Click Ok
  11. In the complete Wizard Actions page, you’ve two options. You can create the Publication immediately or save the configuration in the script file to run at a later time.
  12. Type in the publication name and Click Finish.
  13. In the Creating Publication page, you’ll find information about the wizard’s progress as it works through each step of the process.
  14. Now, you can see that Publication is created under the local publication folderConfigure SubscriberThe final step in setting up replication is to create the subscription. Let us walk-through the steps:
    1. In Object Explorer, expand the Replication folder, right-click Local Subscriptions, and then click New Subscriptions
    2. The New Subscription Wizard appears outlines the general information about the wizard.
    3. On the Publication page, select Publication and then click Next
    4. On the Distribution Agent location page, select “Run all agents at the Distributor” and Click Next
    5. On the Subscribers page, select Add Subscriber, and then select Add SQL Server Subscriber from the drop-down. This step opens the Connect to Server dialog box. Enter the subscriber instance name and then select Connect.
    6. After the subscriber SQL instance has been added, select the drop-down next to the instance name of your subscriber. Then select New Database under Subscription Database and type in the database name and Click Ok.
    7. The subscription database is created and registered to the subscriber. Now, click Next. Make sure that the account has db_owner permission on the newly created database.
    8. On the Distribution Agent Security page, select the ellipsis (…) button. Type in the process account details and Click Ok.
    9. Select Finish accepting the default values on the remaining pages and completing the wizard.
    10. On the complete the wizard page, you can see the detailed summary of the newly created subscription. Click Finish
    11. In the Creating Subscription(s)…page should show that the process has been successful or not.
    12. Connect to the publisher in SQL Server Management Studio. In the Object explore, right-click the Replication folder, and then select Launch Replication Monitor to verify the status of the newly created transactional replication setup.

Also See:

SQL Server