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

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.

• Articles are the actual database objects included in replication like tables, views, indexes, etc.
• An article can be filtered when sent to the subscriber.

• A group of articles is called publication.
• An article can’t be distributed individually. Hence publication is required.

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

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

• It is a request by a subscriber to receive a publication.
• We have two types of subscriptions - push and pull.

• 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

• With this subscription the subscriber initiates the replication instead of the publisher.
• The subscriptions are created at the Subscriber server.