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:


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.


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.


A SQL Server instance that receives replicated data.


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


A database object included in a publication.


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

SQL Server 2005 provides three types of replication :


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.


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.


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:

  1. Configure the publisher and distributor.

  2. Define the publicationdatabase objects, type of replication, and filteringand the type of replication.

  3. Identify a location for storing snapshot files and define when initial synchronization will occur.

  4. Create subscriptions.

  5. Synchronize the data, including the initial synchronization to the snapshot.

Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton © 2008-2017.
If you may any questions please contact us: