Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

What are basic things to keep in mind while writing a Data Replication procedure?

What is exact format to be followed when Article a SQL Replication Procedure? What are component to be used in Procedure and also list components to avoid?

user-image
Question added by NareshKumar Khatri , Systems Engineer , Smart Vision for Information Systems
Date Posted: 2017/08/12
Akwin Dhas
by Akwin Dhas

REPLICATION ENTITIES

SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.

  1. Publisher
  • It is a source database where replication starts. It makes data available for replication.
  • Publishers define what they publish through a publication.
Article
  • Articles are the actual database objects included in replication like tables, views, indexes, etc.
  • An article can be filtered when sent to the subscriber.
Publication
  • A group of articles is called publication.
  • An article can’t be distributed individually. Hence publication is required.
Distributor
  • It is intermediary between publisher and subscriber.
  • It receives published transactions or snapshots and then stores and forwards these publications to the subscriber.
  • It has 6 system databases including distribution.
Subscriber
  • It is the destination database where replication ends.
  • It can subscribe to multiple publications from multiple publishers.
  • It can send data back to publisher or publish data to other subscribers.
Subscription
  • It is a request by a subscriber to receive a publication.
  • We have two types of subscriptions - push and pull.
Push Subscriptions
  • With this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes.
  • Push subscriptions are created at the Publisher server
Pull Subscriptions -
  • With this subscription the subscriber initiates the replication instead of the publisher.
  • The subscriptions are created at the Subscriber server.

More Questions Like This