Replication Architecture

Replication is a set of technologies that allows you to keep copies of the same data on multiple sites, sometimes covering hundreds of sites.

Replication uses a publish-subscribe model for distributing data:

A Publisher is a server that is the source of data to be replicated. The Publisher defines an article for each table or other database object to be used as a replication source. One or more related articles from the same database are organized into a publication. Publications are convenient ways to group related data and objects that you want to replicate together.
A Subscriber is a server that receives the data replicated by the publisher. The Subscriber defines a subscription to a particular publication. The subscription specifies when the Subscriber receives the publication from the Publisher, and maps the articles to tables and other database objects in the Subscriber.
A Distributor is a server that performs various tasks when moving articles from Publishers to Subscribers. The actual tasks performed depend on the type of replication performed.

Microsoft® SQL Server™ 2000 also supports replication to and from heterogeneous data sources. OLE DB or ODBC data sources can subscribe to SQL Server publications. SQL Server can also receive data replicated from a number of data sources, including Microsoft Exchange, Microsoft Access, Oracle, and DB2.

Replication Types

SQL Server 2000 uses three types of replication:

Snapshot replication

Snapshot replication copies data or database objects exactly as they exist at any moment. Snapshot publications are typically defined to happen on a scheduled basis. The Subscribers contain copies of the published articles as they existed at the last snapshot. Snapshot replication is used where the source data is relatively static, the Subscribers can be slightly out of date, and the amount of data to replicate is small.

Transactional replication

In transactional replication, the Subscribers are first synchronized with the Publisher, typically using a snapshot, and then, as the publication data is modified, the transactions are captured and sent to the Subscribers. Transactional integrity is maintained across the Subscribers by having all modifications be made at the Publisher, and then replicated to the Subscribers. Transactional replication is used when data must be replicated as it is modified, you must preserve the transactions, and the Publishers and Subscribers are reliably and/or frequently connected through the network.

Merge replication

Merge replication lets multiple sites work autonomously with a set of Subscribers, and then later merge the combined work back to the Publisher. The Subscribers and Publisher are synchronized with a snapshot. Changes are tracked on both the Subscribers and Publishers. At some later point, the changes are merged to form a single version of the data. During the merge, some conflicts may be found where multiple Subscribers modified the same data. Merge replication supports the definition of conflict resolvers, which are sets of rules that define how to resolve such conflicts. Custom conflict resolver scripts can be written to handle any logic that may be needed to resolve complex conflict scenarios properly. Merge replication is used when it is important for the Subscriber computers to operate autonomously (such as a mobile disconnected user), or when multiple Subscribers must update the same data.

Configuring and Managing Replication

SQL Server 2000 provides several mechanisms for defining and administering replication:

SQL Server Enterprise Manager supports configuring and monitoring replication.
SQL-DMO interfaces for programmatically configuring and monitoring replication.
Programmatic interfaces for replicating data from heterogeneous data sources.
Microsoft ActiveX® controls for embedding replication functionality in custom applications.
Scripting replication using Transact-SQL system stored procedures.

Replication Architecture

Replication Types

Configuring and Managing Replication

See Also