Sql Server replication and its types

Sql Server replication and its types

Replication: means Copying data or replicating data or it reproduces duplicate data.

It could be copying from across multiple SQL Server instances.

You can set up replication to continuously synchronize the source data with the copied data or set it up to synchronize the data at scheduled intervals. Plus, replication supports both one-way and bi-directional synchronization, and lets you keep several datasets in sync with each other.

Component required for replication

  • Publisher
    •  Publication database
      • Publication
        • Articles
  • Distributor
    • Distribution database
  • Subscriber
    • subscription database
      • subscription
  • Replication agents

Articles :For each SQL Server object that should be replicated, an article needs to be defined. Each article corresponds to a single SQL Server object.

Publications:A publication is a collection of articles grouped together as one unit.

Publication database:Any database that contains objects designated as articles is called a publication database. A publication can contain articles from a single publication database only.

Publisher: The Publisher is the SQL Server instance that makes a publication available for replication.

Distributor: Each Publisher is linked to a single Distributor. The Distributor is a SQL Server instance that identifies changes to the articles on each of its Publishers.

Distribution databases:Each Distributor has at least one distribution database. The distribution database contains a number of objects that store replication metadata as well as replicated data.

A Distributor can hold more than one distribution database; however, all publications
defined on a single Publisher must use the same distribution database.

Subscriber: Each SQL Server instance that subscribes to a publication is called a Subscriber.

Subscriptions:A subscription is the counterpart of the publication. Each subscription creates a link, or contract, between one publication and one Subscriber. There are two types of subscription.

  1. Push subscriptions : the Distributor directly updates the data in the Subscriber database.
  2. Pull subscription :  the Subscriber asks the Distributor regularly if any new changes are available, and then updates the data in the subscription database itself.

Subscription databases: A database that is the target of a replication subscription is called a subscription database.

Replication agents: The replication processes are executed by a set of replication agents. Each agent is an independent Windows executable responsible for one piece of the process of moving the data.

Replication agents Types:

  • Snapshot Agent : provides the data required to perform the initial synchronization of the publication database with the subscription database.
  • Log Reader Agent : Reads the Transaction log of publication database and if find change on published objects record  that change to  distribution database.
    • Use only in Transaction log replication.
  • Distribution Agent : The Distribution Agent applies the changes recorded in the distribution database to the subscription database.
  • Merge Agent : The Merge Agent synchronizes changes between the publication database and the
    subscription database.
  • Queue Reader Agent : Used for bi-directional transactional replication.

SQL Server supports three types of replication:

  • Snapshot Replication
  • Transactional Replication
  • Merge Replication

Snapshot Replication:Snapshot replication creates an identical copy of the replicated objects and their data each time it runs.

  •  No synchronization capability available.
  • subsequent executions always copy the entire dataset again.
  • Require more bandwidth and storage.
  • Rarely used.

Transactional replication:Transactional replication copies data uni-directionally from the source database to the target database.

  • Uses the log files associated with the source database to keep data in sync.
  • Change made to the source database, can be immediately synced to the target database.
  • Synchronization can be scheduled.
  • Every table you want to publish must be configured with a primary key.

Merge replication:Merge replication allows two or more databases to be kept in sync. Any change applied to one database will automatically be applied to the other databases – and vice versa.

  • Designed to allow for data changes on the Publisher as well.
  • Allows for disconnected scenarios.
  • Merge Agent is responsible for synchronizing the changes between the Publisher and
    its Subscribers.
  • Rows identified across the server by uniqueidentifier column with the ROWGUIDCOL property set and a unique constraint defined on that column.