For merge publications, you can set a priority number for each subscription. The priority number is used to resolve conflicts among subscriptions during the merge process. The priority number is a real number from 0.00 (lowest priority) through 99.99 (highest priority). If two or more subscriptions are trying to update the same row at the same time, the conflict resolver chooses the subscription with the highest priority number as the winner of the conflict. The priority number of a subscription cannot be greater than the priority number assigned the Publisher where the publication was created.
When you create the subscription, you have a choice of assigning a specific priority number or letting the subscription use the priority number of the Publisher. A subscription with an assigned priority number is referred to as a global subscription, while a subscription that uses the priority number of the Publisher is referred to as a local subscription. If a change is made to a row in a global subscription, the subscription priority is immediately stored in the meta-data for that change and travels with the row as it merges with changes at other Subscribers. If a change is made to a row in a local subscription, no priority is assigned to the change until the row is merged with the other changes at a Publisher. During the merge process at the Publisher, the changes from the Subscriber are assigned the Publisher's priority and travel with that priority as it merges with changes at other Publishers and Subscribers.
The primary advantage of creating global subscriptions is the ability to assign a different priority to each subscription. The primary advantage of creating local subscriptions is the reduction in the meta-data that has to be stored about the subscription at the Publisher. Local subscriptions are most appropriate in applications where all Subscribers should have the same priority and the first Subscriber to merge with the Publisher wins. Local subscriptions are also appropriate if Subscriber is a leaf node in a topology with several levels.
To understand the priority scheme, consider the following topology.
The highest priority change wins. Suppose that all three sites agree initially that Site A created version one of the row, and no subsequent updates have occurred. If Sites A and B update the row simultaneously, then Site A’s update is the conflict winner, because it has the highest priority.
If multiple changes have occurred to the same row after the last synchronization, then the maximum of site priorities of changes made after the common version is used to determine the conflict winner. For example, suppose that Site A makes version two, sends it on to Site B, which makes version three, and then sends it back to Site A, where it is merged. Then suppose that Site C has also made a version two and reconciles with A. Choosing the maximum priority of changes that occurred after the common version gives 99.9 (Site A’s priority) for the version of the row which A has, and 95 for the version of the row that C has. A and B’s joint changes are the priority winner when compared to the change made by C. In this case, Site A is the conflict winner. One benefit of this decision rule is that no changes made at the highest priority replica ever get undone by the conflict resolution mechanism. However, this does not prevent lower priority replicas from making subsequent changes to the rows.
In the event that the maximal priority rule results in a tie, then the change already at the hub is chosen as the winner. For all synchronizations, one of the sites always acts as the hub. In simple topologies, there will be only one hub. Yet even when users set up a multihub topology, one of the replicas will have the hub role in hub-to-hub merges. This allows you to set up a first-to-hub conflict-decision rule by setting equal priorities.
All local and anonymous subscriptions are treated as having priority 0: they automatically lose if their changes conflict. However, if they convey a non-conflicting change to the hub, then the hub assumes authorship of the change. By having a single hub with all other replicas as local/anonymous subscriptions, you can set up a replica set with understandable conflict properties. All changes done at the hub are permanent; they will never be undone as a conflict loser. All changes at a nonhub are tentative until you reconcile with the hub, at which time they either become permanent or they conflict and are rejected.
| Using a Custom Resolver | Merge Replication |