Export (0) Print
Expand All

Subscriber Types and Conflicts

SQL Server 2000

Subscriber Types and Conflicts

When you create a subscription, you can either assign it a priority value or use the priority value of the Publisher.

A subscription with an assigned priority value is called a global subscription; a subscription using the priority value of the Publisher is called a local subscription. This table summarizes the main differences and uses of each type.

Type Priority Value Used
Global Assigned by user When you want different Subscribers to have different priorities.
Local (includes anonymous) 0.00, but change assumes priority value of Publisher after synchronization When you want all Subscribers to have the same priority, and the first Subscriber to merge with the Publisher to win the conflict.

Anonymous subscriptions are helpful when you expect to have a large number of Subscribers and you do not want to keep track of them at the Publisher/Distributor.


When you change a row in a global subscription, the subscription priority is stored in the meta data for the change. This priority value travels with the changed row as it merges with changes at other Subscribers. This assures that a change made by a higher priority subscription does not lose to a change made by a subscription with a lower priority.

If a row is changed in a local subscription, no priority is assigned to the change until the row merges with the other changes at a Publisher. During the merge process at the Publisher, the changes from the Subscriber are assigned the priority of the Publisher and travel with that priority as it merges with changes at other Publishers and Subscribers. In a sense, the Publisher assumes authorship of the change.

Global subscriptions provide a greater number of options and allow for greater sophistication to a conflict resolution scheme than local subscriptions. Using global subscriptions ensures that priority values are preserved throughout the enterprise.

Local subscriptions are also appropriate (and usually required) in a topology with several levels, where Subscribers are leaf nodes. In these topologies, any nodes that republish data must be global Subscribers; local Subscribers can be used only at the leaf nodes.

Example of Merge Conflict Resolution Based on Subscriber Type and Assigned Priorities

To understand how conflicts resolve according to assigned priority values and whether a subscription is global or local, consider the following example, which describes a series of updates to a row over several merge synchronizations.

Here are the initial priority values for four sites in a basic merge replication topology (one Publisher, two global Subscribers, and one local Subscriber).

Site Type Priority Value
A Publisher 100.00
B Global Subscriber 75.00 (assigned)
C Global Subscriber 50.00 (assigned)
D Local Subscriber 0.00 (default)

Phase 1: Initial Values

Initially, Site A (the Publisher) creates version one of the row containing value='Nebraska', which is replicated to Sites B, C, and D during the next merge synchronization. After synchronization, here are the values for the row.

Site Priority Value Row Value
A (Publisher) 100.00 Nebraska
B (Global Subscriber) 75.00 Nebraska
C (Global Subscriber) 50.00 Nebraska
D (Local Subscriber) 0.00 Nebraska

Phase 2: Publisher and Global Subscriber Both Update Row

Site A updates the row value to Texas and site B updates the row value to New Jersey. When the next merge synchronization occurs, there is a conflict between sites A and B. Site A wins the conflict (the Publisher always wins an update conflict, even if the priority values are the same by default, but there is also the option that Subscriber wins the conflict). The conflict winner value from site A is propagated to sites B, C, and D.

Site Priority Value Row Value
A (Publisher) 100.00 Texas
B (Global Subscriber) 75.00 Texas
C (Global Subscriber) 50.00 Texas
D (Local Subscriber) 0.00 Texas

Phase 3: Multiple Changes Made to the Same Row

Suppose site C updates the row (changes it to North Carolina) and synchronizes with the Publisher. This is not a conflict because C already successfully merged the last update from A (with the row value='Texas' successfully merged). Then suppose Site B updates the row (changes it to Idaho).

Site Priority Value Row Value
A (Publisher) 100.00 North Carolina
B (Global Subscriber) 75.00 Idaho
C (Global Subscriber) 50.00 North Carolina
D (Local Subscriber) 0.00 Texas

When site B synchronizes with the Publisher, there is an update conflict. Because both B and C are global subscriptions and the priority of B is greater than that of C, site B wins the conflict. After the other two sites are also merged, the value of B is propagated to the other Subscribers.

Site Priority Value Row Value
A (Publisher) 100.00 Idaho
B (Global Subscriber) 75.00 Idaho
C (Global Subscriber) 50.00 Idaho
D (Local Subscriber) 0.00 Idaho

Phase 4: Local and Global Subscribers Both Update Row

Suppose site D updates the row (changes it to New Mexico) and synchronizes with the Publisher. Then suppose Site B updates the row (changes it to California).

Site Priority Value Row Value
A (Publisher) 100.00 New Mexico
B (Global Subscriber) 75.00 California
C (Global Subscriber) 50.00 Idaho
D (Local Subscriber) 0.00 New Mexico

When site B synchronizes with the Publisher, there is an update conflict. Unlike the previous example, because D is a local Subscriber, it assumes the priority value of the Publisher (site A) upon synchronization. Because the priority of A is greater than B, B loses the conflict; the value initially entered into D wins. (Had the global Subscriber B synchronized with A before the local Subscriber D did, site B would have won the conflict.) Site D winning the conflict relies on the Publisher not having made a change or received another change since the version of the row updated at Site D was last synchronized. If any global Subscriber or any other local Subscriber synchronizes first, the rule of highest priority or first in to the Publisher wins is followed.)

The final values after all the sites are synchronized are shown here.

Site Priority Value Row Value
A (Publisher) 100.00 New Mexico
B (Global Subscriber) 75.00 New Mexico
C (Global Subscriber) 50.00 New Mexico
D (Local Subscriber) 0.00 New Mexico

Synchronization order and priority value determine the outcome of conflicts when mixing global and local Subscribers at the same level in your topology. This last set of updates illustrates why caution must be exercised. Although the local Subscriber had the lowest priority value of the three Subscribers, it won the conflict because it synchronized with the Publisher (thus assuming the Publisher priority value of 100.00) first. Had site C (global Subscriber with a priority value of 50.00) entered New Mexico instead of site D, site B (global Subscriber with a priority value of 75.00) would have won the conflict, and the result would have been California.

Show:
© 2014 Microsoft