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.
End of Article
