How Replication Works

SQL Replication

Replication

How Replication Works

There are several ways to implement and monitor replication, and the process of replication is different depending on the type of replication and the options you choose. In general, replication is composed of the following stages: configuring replication, generating and applying the initial snapshot, modifying replicated data, and synchronizing and propagating data.

Configuring Replication

Replication deployment begins when you configure a Publisher and Distributor. The Distributor can be a separate server from the Publisher, or it can be the same server. In general, replication is composed of the following stages: configuring replication, generating and applying the initial snapshot, modifying replicated data, and synchronizing and propagating data. The Distributor is a primary component during snapshot replication and transactional replication; however, the role of the Distributor is limited during merge replication. The Distributor is used only for agent history reporting and monitoring purposes. During merge replication, the Publisher and Distributor are usually the same server. This is called using a local Distributor.

After the Publisher and Distributor are configured, you can create publications based on data, subsets of data, and/or database objects. When you create the publication, you determine what type of replication you want to use, the type of databases that will be Subscribers to the publication, the data and database objects that will be published, where the snapshot files will be stored, when the initial snapshot synchronization will occur, and options that will be used with the publication.

After you create a publication, you can create push and/or pull subscriptions at either the Publisher or the Subscriber and configure your replication schedule and options.

Generating and Applying the Initial Snapshot

Whether you choose snapshot replication, transactional replication, or merge replication, SQL Server 2000 creates an initial snapshot of schema and data and saves it to the snapshot folder and location you chose when creating the publication. The two exceptions to this process are with dynamic filters in merge replication and subscriptions for which the snapshot will be applied manually. After the subscription is created, when the initial snapshot is applied is based on the schedule you indicated when creating the publication, or you can apply the snapshot manually.

The Snapshot Agent prepares snapshot files containing schema, data, and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor for snapshot replication or transactional replication, and in the publication database for merge replication. The Snapshot Agent does not prepare these files when the merge publication uses dynamic filters and does not use dynamic snapshots when the subscription specifies that the snapshot will be applied manually.

With snapshot replication and transactional replication, the Distribution Agent moves the snapshot from the distribution database to the destination tables at the Subscribers and applies the scripts, schema, and data necessary for replication. With merge replication, the Merge Agent moves the snapshot to Subscribers when it is run for the first time or when the subscription is set for reinitialization. It then applies the scripts, schema, and data necessary for replication.

Modifying Replicated Data

Depending on the type of replication and the options you chose when configuring the publication, the Subscriber may be able to modify data after the initial snapshot has been replicated and propagate changes to the Publisher, which can then propagate the changes to other Subscribers.

The following replication types and options allow Subscribers to modify replicated data:

  • Merge replication

  • Snapshot replication or transactional replication with immediate updating

  • Snapshot replication or transactional replication with queued updating

  • Any type of replication in which data is filtered so partitions of data can be modified at individual sites autonomously and without conflicts occurring between sites
Synchronizing and Propagating Data Changes

How data is synchronized and data modifications propagated to Publishers and other Subscribers depends on the type of replication and options you choose. Synchronizing data refers to the process of data being propagated between Publisher and Subscribers after the initial snapshot has been applied at the Subscriber.

For snapshot replication, synchronize means to reapply the snapshot at the Subscriber so that schema and data at the subscription database is consistent with the publication database. For transactional replication, synchronizing data means that data INSERTs, UPDATEs, and DELETEs, and other data modifications, are distributed between Publisher and Subscribers. For merge replication, synchronization means that data modifications made at multiple sites are merged, conflicts (if any) are detected and resolved, and data eventually converges to the same data values at all sites.

See Also

Applying the Initial Snapshot

Generating the Initial Snapshot

Implementing Replication

Replication Options

Synchronizing Data

Types of Replication