Implementing Transactional Replication
Implementing Transactional Replication
In this section, you'll learn about
of implementing transactional replication. Transactional replication can be implemented in a one-to-many scheme or in a
scheme. Often, transactional replication is implemented over a wide area network (WAN).
of transactional replication involve a one-to-many replication scheme. In this type of implementation, one table is published to one or more subscribers.
In a many-to-one replication scheme, one database subscribes to more than one subscription. This is not the most common replication scheme, but it is nevertheless widely used. Because transactional replication works by reading the transaction log on the publisher and applying
, updates, and deletes to the subscriber, it is
suited for this replication scheme. The only potential drawback is the fact that the subscribing table must have a primary key defined on it. As long as your data sources do not
this primary key, the many-to-one replication scheme will function properly.
Replication over a WAN
Replication over a WAN is not only possible, but also quite common. If you replicate data over a WAN, you should continuously monitor the distribution history and look for excessive replication times caused by network bandwidth limitations. In addition, if possible, configure replication such that only the Distribution Agent connects over the WAN. That is, configure the system such that the distributor and publisher are on the same side of the WAN.
In this chapter, you have learned the basics of transactional replication. You have learned what transactional replication is, how it works, how to monitor it, and how to tune it. By understanding how transactional replication works and what it does, you can use it and configure it effectively. In the
chapter, you will learn about merge replication.
from transactional replication in that it is
multidirectional. With merge replication, publishers and subscribers can update the publication equally. Transactional replication also allows subscribers to update the publication, but the two replication types function quite differently. In this chapter, you will learn how merge replication works and how to configure, monitor, and tune merge replication.
Introduction to Merge Replication
multidirectional replication between the publisher and one or more subscribers. This allows multiple systems to have updatable copies of the publication and to modify their own copies. A modification on the publisher will be replicated to the subscribers. A modification on a subscriber will be replicated to the publisher and then replicated to the other subscribers.
Unlike transactional replication, merge replication works by installing triggers on the publisher and on the subscribers. Whenever a change is made to the publication or a copy of it, the appropriate trigger is
, which causes a replication command to be queued up to be sent to the distribution database. This command is eventually sent to the distribution database and then sent to participating systems. Because merge replication operates this way, it requires much more overhead,
on the publisher, than does transactional replication.
As you will learn in this chapter, the key
involved in the merge replication system are the Merge Agent and the distribution database. The Merge Agent reconciles (merges) incremental changes that have occurred since the last reconciliation. When you use merge replication, no Distribution Agent is used—the Merge Agent communicates with both the publisher and the distributor. The Snapshot Agent is used only to create the initial database. The Merge Agent performs the following
The Merge Agent uploads all changes from the subscriber.
All of the rows without a conflict (rows not modified on both the publisher and the subscriber) are uploaded immediately; those with a conflict (rows modified on both systems) are sent to the conflict resolver. The
is a module that is used to resolve conflicts in merge replication. You can configure this module to resolve conflicts based on your needs.
All changes are applied to the publisher.
The Merge Agent uploads all changes from the publisher.
All of the rows without a conflict are uploaded immediately; those with a conflict are sent to the conflict resolver.
All changes are applied to the subscriber.
This process will repeat as scheduled. With push subscriptions, the Merge Agent runs on the distributor. With pull subscriptions, the Merge Agent runs on the subscriber. Each merge publication has its own Merge Agent.