Categories
High Availability

Replication Components and Agents

Replication Components

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

1. Publisher – It is the system that provides the data to be replicated and acts as a source database. It consists of a Windows server running SQL Server 2005 database. The Publisher maintains information about which data is configured for replication.

2. Publication – The data to be replicated is grouped in an object called Publication. It is made up of one or more articles. Publication is created in order to publish data to other systems.

3. Article – An Article is individual component of data that is to be replicated. It could be an entire table or specific columns in a table or specific rows in a table or a stored procedure. In short an Article is a subset of a table that is being replicated. Subsets are created by using filters. A filter that is used to create a subset of rows is called Horizontal filter. A filter that is used to create a subset of columns is called Vertical filter.

4. Distributor – They are the servers that distribute the replicated data. They store the distribution database, meta data, historical data, transactions.

5. Subscriber -They are the destination servers for replication. They store the replicated data and receive updates. Subscribers can also make changes to the data. You can publish data to multiple subscribers.

6. Subscription – It is a request by a subscriber to receive a publication. It also defines what published data will be sent to the subscriber.

7. Push Subscriptions – The publisher is responsible for providing updates to the subscribers. Updates are initiated without any request from the subscriber. It can be configured to keep the replication close to real time or to perform updates on a regular schedule.

8. Pull Subscriptions – It allows subscribers to specify when the changes are propagated. It is useful if there are large number of subscribers and when the subscribers are not connected to the network. The initiation of the replication is done on the subscriber side.

Replication Agents

The following are the replication agents and let us discuss each one of those in detail.

* Snapshot Agent – It is used for creating and propagating the snapshots used in snapshot replication. Each Published database has its own Snapshot Agent that runs on the distributor and connects to the publisher and takes a snapshot of the objects. It then updates the information in the distribution database. Snapshot Agents are used with all types of replication.

* Distribution Agent – It applies the data from snapshot replication or transactions from transactional replication to subscribers. It can run on the distributor or on subscribers. It runs on the distributor for push subscriptions and on subscriber for pull subscriptions. This agent is not used with merge replication.

* Merge Agent – The Merge agent is used in Merge replication and it merges the incremental changes that have occurred since the last reconciliation. In Merge replication there is no distribution or snapshot agent involved instead the Merge agent communicates with both the publisher and distributor. There is one Merge agent for each merge subscription.

* Log Reader – The Log reader is used for transactional replication. It moves the information from transaction log in the publisher to the distribution database. Each database that is using transaction replication has its own Log reader agent on the publisher.

* Queue Reader Agent – This is used in Transaction replication with queued updating option. It takes the changes that has occurred in the subscriber and applies them to the publisher. There is only one Queue reader agent per database.

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*