Chapter 19. Replication

Replication copies and distributes data and database objects from one database to another and provides a mechanism to keep the data synchronized. Data can be replicated from server to server or from server to client.

Before you look at programming replication, there are some key terms with which you should be familiar:

Publisher

A SQL Server instance that makes its data available through replication, detects changes to the data, and maintains information about one or more publications and articles.

Distributor

A SQL Server instance that stores replication metadata for one or more publishers. Each distributor has a distribution database that stores history data, transactions, and metadata. When a single SQL Server instance acts as both publisher and distributor, the distributor is called a local distributor. When the publisher and distributor are on different SQL Server instances, the distributor is called a remote distributor.

Subscriber

A SQL Server instance that receives replicated data.

Publication

A collection of one or more articles. A publication can be either pushed to the subscriber or pulled by the subscriber.

Article

A database object included in a publication.

Subscription

A request for the publication that defines where and when the publication will be received.

SQL Server 2005 provides three types of replication :

Snapshot

Distributes data as it appears at a moment in time. The entire snapshot is generated and sent to subscribers when synchronization occurs and is not monitored for updates to the published data.

Transactional

Schema and data changes are sent to subscribers when they occur. Data changes are applied to the subscriber data in the same order and within the same transaction boundaries in which they occurred at the publisher, guaranteeing transactional consistency within each publication. Transactional replication usually starts with a snapshot of the schema and data, and is typically used in server-to-server replication scenarios.

Merge

Schema and data changes are tracked with triggers. Data is synchronized when the subscriber connects to the publisher; all changes made since the last synchronization occurred are included. Merge replication typically starts with a snapshot of the schema and data, and is normally used in server-to-client replication scenarios.

You can implement and administer replication by using SQL Server Management Studio, by using Windows Synchronization Manager, or programmatically by using replication APIs. The rest of this chapter discusses the third option, programmatic implementation and administration of replication. For more information about the other two methods, consult Microsoft SQL Server 2005 Books Online.

Replication uses a set of programs called agents to perform tasks associated with replication. The agents are described in the "Agents Supporting Replication" sidebar.

Implementing replication involves five steps: