Introducing Replication

Root Folder > SQL Server
Microsoft® SQL Server™ 2000 replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency.

Using replication, you can distribute data to different locations, to remote or mobile users over a local area network, using a dial-up connection, and over the Internet. Replication also allows you to enhance application performance, physically separate data based on how it is used (for example, to separate online transaction processing (OLTP) and decision support systems), or distribute database processing across multiple servers. For more information on Replication visit:
Replication Model
Microsoft® SQL Server™ 2000 replication uses a publishing industry metaphor to represent the components and processes in a replication topology. The model is composed of the following: Publisher, Distributor, Subscribers, Publications, articles, and subscriptions.

There are also several replication processes that are responsible for copying and moving data between the Publisher and Subscriber. These are the Snapshot Agent, Distribution Agent, Log Reader Agent, Queue Reader Agent, and Merge Agent. For more information about the agent processes, see Agents and Monitors.

The Publisher is a server that makes data available for replication to other servers. The Publisher can have one or more publications, each representing a logically related set of data. In addition to being the server where you specify which data is to be replicated, the Publisher also detects which data has changed during transactional replication and maintains information about all publications at that site.

The Distributor is a server that hosts the distribution database and stores history data, and/or transactions and meta data. The role of the Distributor varies depending on which type of replication you implement. For more information, see Types of Replication.

A remote Distributor is a server that is separate from the Publisher and is configured as a Distributor of replication. A local Distributor is a server that is configured to be both a Publisher and a Distributor of replication.

Subscribers are servers that receive replicated data. Subscribers subscribe to publications, not to individual articles within a publication, and they subscribe only to the publications that they need, not all of the publications available on a Publisher. Depending on the type of replication and replication options you choose, the Subscriber could also propagate data changes back to the Publisher or republish the data to other Subscribers.

A publication is a collection of one or more articles from one database. This grouping of multiple articles makes it easier to specify a logically related set of data and database objects that you want to replicate together.

An article is a table of data, a partition of data, or a database object that is specified for replication. An article can be an entire table, certain columns (using a vertical filter), certain rows (using a horizontal filter), a stored procedure or view definition, the execution of a stored procedure, a view, an indexed view, or a user-defined function.

A subscription is a request for a copy of data or database objects to be replicated. A subscription defines what publication will be received, where, and when. Synchronization or data distribution of a subscription can be requested either by the Publisher (a push subscription) or by the Subscriber (a pull subscription). A publication can support a mixture of push and pull subscriptions.
Introducing the Types of Replication
There are three types of replication available with Microsoft® SQL Server™ 2000: snapshot replication, transactional replication and merge replication.

Snapshot Replication (For Step-by-Step guide Click Here!)
Snapshot replication is the process of copying and distributing data and database objects exactly as they appear at a moment in time. Snapshot replication does not require continuous monitoring of changes because changes made to published data are not propagated to the Subscriber incrementally. Subscribers are updated with a complete refresh of the data set and not individual transactions. Because snapshot replication replicates an entire data set at one time, it may take longer to propagate data modifications to Subscribers. Snapshot publications are typically replicated less frequently than other types of publications.

Options available with snapshot replication allow you to filter published data, allow Subscribers to make modifications to replicated data and propagate those changes to the Publisher and then to other Subscribers, and allow you to transform data as it is published.

Snapshot replication can be helpful in situations when:
  • Data is mostly static and does not change often.
  • It is acceptable to have copies of data that are out of date for a period of time.
  • Replicating small volumes of data.
  • Sites are often disconnected and high latency (the amount of time between when data is updated at one site and when it is updated at another) is acceptable.

Transactional Replication
With transactional replication, an initial snapshot of data is propagated to Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

SQL Server 2000 monitors INSERT, UPDATE, and DELETE statements, and changes to stored procedure executions and indexed views. SQL Server 2000 stores the transactions affecting replicated objects and then it propagates those changes to Subscribers continuously or at scheduled intervals. Transaction boundaries are preserved. If, for example, 100 rows are updated in a transaction, either the entire transaction with all 100 data modifications are accepted and propagated to Subscribers or none of them are. When all changes are propagated, all Subscribers will have the same values as the Publisher.

  • Options available with transactional replication allow you to filter published data, allow users at the Subscriber to make modifications to replicated data and propagate those changes to the Publisher and to other Subscribers, and allow you to transform data as it is published.

Transactional replication is typically used when:

  • You want data modifications to be propagated to Subscribers, often within seconds of when they occur.
  • You need transactions to be atomic (either all or none applied at the Subscriber).
  • Subscribers are mostly connected to the Publisher.
  • Your application will not tolerate high latency for Subscribers receiving changes.

Merge Replication
Merge replication allows various sites to work autonomously (online or offline) and merge data modifications made at multiple sites into a single, uniform result at a later time. The initial snapshot is applied to Subscribers and then SQL Server 2000 tracks changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers either at a scheduled time or on demand. Updates are made independently (no commit protocol) at more than one server, so the same data may have been updated by the Publisher or by more than one Subscriber. Therefore, conflicts can occur when data modifications are merged.

Merge replication includes default and custom choices for conflict resolution that you can define when you configure a merge publication. When a conflict occurs, a resolver is invoked by the Merge Agent to determine which data will be accepted and propagated to other sites.

Options available with merge replication include filtering published data horizontally and vertically, including join filters and dynamic filters, using alternate synchronization partners, optimizing synchronization to improve merge performance, validating replicated data to ensure synchronization, and using attachable subscription databases.

Merge replication is helpful when:

  • Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
  • Subscribers need to receive data, make changes offline, and synchronize changes later with the Publisher and other Subscribers.
  • The application latency requirement is either high or low.
  • Site autonomy is critical.
Agents and Monitors

Agents used with Microsoft® SQL Server™ 2000 replication carry out the tasks associated with copying and distributing data. SQL Server 2000 replication uses SQL Server Agent as well as agents that are specific to replication.

SQL Server Agent

SQL Server Agent hosts and schedules the agents used in replication, and provides an easy way to run replication agents. SQL Server Agent also controls and monitors several other operations outside of replication, including monitoring the SQL Server Agent service, maintaining error logs, running jobs, and starting other processes.

Snapshot Agent

The Snapshot Agent is used with all types of replication. It prepares schema and initial data files of published tables and stored procedures, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent typically runs under SQL Server Agent at the Distributor and can be administered using SQL Server Enterprise Manager.

Log Reader Agent

The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Distribution Agent

The Distribution Agent is used with snapshot replication and transactional replication. It moves the snapshot jobs and transactions held in the distribution database to Subscribers. The Distribution Agent typically runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Merge Agent

The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber, and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent typically runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions. The Merge Agent typically uploads changes from the Subscriber to the Publisher and then downloads changes from the Publisher to the Subscriber during a typical bidirectional merge. Changes can also be moved in one direction by configuring the exchange type of the agent.

Queue Reader Agent

The Queue Reader Agent is used with snapshot replication or transactional replication with the queued updating option, or if the immediate updating with queued updating as a failover option is enabled.

The Queue Reader Agent is a multithreaded agent that runs on the Distributor. It is responsible for taking messages from a queue and applying them to the appropriate publication.

Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given Distributor.

Miscellaneous Agents

Clean up agents listed under the Miscellaneous Agents folder in Replication Monitor complete scheduled and on-demand maintenance of replication.

Clean up agent Description Default schedule
Agent History Clean Up: Distribution Removes replication agent history from the distribution database. Runs every 10 minutes
Distribution Clean Up: Distribution Removes replicated transactions from the distribution database. Runs every 10 minutes
Expired Subscription Clean Up Detects and removes expired subscriptions from publication databases. Runs every day at 1:00 A.M.
Reinitialize Subscriptions Having Data Validation Failures Reinitializes all subscriptions that have data validation failures. No default schedule (not enabled by default).
Replication Agents Checkup Detects replication agents that are not actively logging history. Runs every 10 minutes


Replication Monitor

Through Replication Monitor in SQL Server Enterprise Manager, you can view and manage replication agents responsible for various replication tasks. For example, you can set up transactional replication so that the log on the Publisher is read continuously, transactions are distributed to Subscribers every ten minutes (although this is often also continuously), and initial snapshots are generated every night at midnight. You can also execute replication agents on demand.

Replication Monitor provides a way to set alerts on replication events. When the event occurs, Replication Monitor responds automatically, either by executing a task that you have defined or by sending an e-mail or a pager message to a specified individual.

Add Feedback