Setting Up Transactional Replication: A Step-by-step Guide

General concepts
Checking necessary conditions
Checking connect
Example
Literature

 

General concepts

Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet. Microsoft SQL Server replication uses a publish, distributor and subscribe metaphor.

Publisher is the server or database that sends its data to another server or database.

Subscriber is the server or database that receives data from another server or database.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

Publisher contain publication/publications. Publication is a collection of one or more articles that is sent to a subscriber server or database. Article is the basic unit of replication and can be a table or a subset of a table.

Subscription is the group of data that a server or database will receive. This can be one or more publications. There are push and pull subscriptions. Push subscription is subscription when the publishing server will periodically push transactions out to the subscribing server or database. Pull subscription is subscription when the subscribing server will periodically connect to the distribution database and pull information.

Microsoft SQL Server support the following replication topologies:

  • Central publisher
  • Central subscriber
  • Central publisher with remote distributor
  • Publishing subscriber

Central publisher

This is one of the most common replication topologies. One server is configured as Publisher and Distributor and another server/servers is configured as Subscriber/Subscribers.

Central subscriber

This is a common topology in data warehousing. Many servers or databases replicate their data to a single central server or database.

Central publisher with remote distributor

In this topology distribution database resides on the another server than publisher. This topology uses for performance reasons when the level of replication activity increases or the server or network resources become constrained.

This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.

Publishing subscriber

This is a dual role topology. In this topology, the publishing server or database also subscribes to another server or database. In this scenario two servers publish the same data. This is useful when a Publisher must send data to Subscribers over a slow or expensive communications link.

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles