SQL Server 2005 Replication

Distributing data between different locations in an organization is essential for scalability, availability, and distributed reporting. SQL Server 2005 uses a publishing model to describe its distribution process: The data being replicated is the article, the system that's the source of the data is the Publisher, and the systems that receive the replicated data are the Subscribers. The SQL Server system that transfers the data is the Distributor. One Publisher can have multiple Subscribers, and in many cases, the Publisher also acts as the Distributor. SQL Server 2005 supports several types of database replication to meet different needs.

Snapshot Replication

With snapshot replication, the most basic form of replication, the Publisher makes a point-in-time copy of the data for distribution to the Subscribers. Snapshot replication is best suited for distributed reporting scenarios in which Subscribers might have high-latency connections and don't need access to immediate data updates. In addition, SQL Server 2005 snapshot replication is supported for many ODBC– and OLE DB–compliant heterogeneous databases.

Transactional Replication

At the other end of the replication spectrum, transactional replication helps Subscribers stay current with the most recent changes from the Publisher. The Publisher sends a snapshot to the Subscribers to synchronize data. After the Publisher and Subscriber databases are in synch, data from the Publisher's transaction log is captured and distributed to the Subscribers. Transactional replication is suited for scenarios in which good connectivity exists between Publishers and Subscribers and in which Subscribers need low data latency.

Transactional Replication with Updating Subscriptions

Although transactional replication is great for keeping multiple Subscribers updated, it's also a one-way technology—Subscribers can't change the data that flows from the Publisher. To address this problem, Updating Subscriptions lets Subscribers update replicated data and push those updates back to the Publisher.

Merge Replication

Merge replication is designed for branch offices in which Publishers and Subscribers might not always be connected and data might be updated at more than one location. The Publisher sends a database snapshot to the Subscribers. A merge agent at the Publisher periodically captures data changes and forwards those changes to the Distributor, which sends the changes to the Subscribers. A built-in conflict-resolution engine arbitrates situations in which multiple servers are updating the same data.

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication was introduced with SQL Server 2005 and is designed to facilitate scalability by spreading data across multiple server systems. Peer-to-peer doesn't use the hierarchical publishing model. Instead, all SQL Server systems communicate directly. Each node acts as a Publisher and Subscriber, sharing the same schema and data.