Advanced Merge Replication Conflict Detection and Resolution
When a Publisher and a Subscriber are connected and synchronization occurs, the Merge Agent detects if there are any conflicts. If conflicts are detected, the Merge Agent uses a conflict resolver (which is specified when an article is added to a publication) to determine which data is accepted and propagated to other sites. For a complete list of conflict types, see the "Conflict Types" section of How Merge Replication Detects and Resolves Conflicts.
Although a Subscriber synchronizes with the Publisher, conflicts typically occur between updates that are made at different Subscribers, instead of updates being made at a Subscriber and at the Publisher.
The behavior of conflict detection and resolution depends on the following options, which are described in this topic:
- Whether you specify column-level tracking, row-level tracking, or logical record-level tracking.
- Whether you specify the default priority-based resolution mechanism or specify an article resolver. An article resolver can be:
- A business logic handler written in managed code
- A COM-based custom resolver
- A COM-based resolver supplied by Microsoft
If the default resolution mechanism is used, behavior is further determined by the type of subscription used: client or server.
Whether a data change qualifies as a conflict or not depends on the type of conflict tracking you set for an article:
- If you select column-level conflict tracking, it is considered a conflict if changes are made to the same column in the same row at more than one replication node.
- If you select row-level tracking, it is considered a conflict if changes are made to any columns in the same row at more than one replication node (the columns affected in the corresponding rows need not be the same).
- If you select logical record-level tracking, it is considered a conflict if changes are made to any row in the same logical record at more than one replication node (the columns affected in the corresponding rows need not be the same).
For more information about the tracking level, see the "Tracking Level" section of How Merge Replication Detects and Resolves Conflicts and Detecting and Resolving Conflicts in Logical Records.
To specify the conflict tracking and resolution level for an article
- Microsoft Management Studio: How to: Specify the Conflict Tracking and Resolution Level for Merge Articles (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Specify the Conflict Tracking and Resolution Level for Merge Articles (Replication Transact-SQL Programming)
After a conflict is detected, the Merge Agent launches the selected conflict resolver and uses the resolver to determine the conflict winner. The winning row is applied at the Publisher and Subscriber, and the data from the losing row is written to a conflict table. Conflicts are resolved immediately after the resolver executes, unless you select to resolve conflicts interactively.
In merge replication, conflict resolution takes place at the article level. For publications composed of several articles, you can have different conflict resolvers serving different articles, or the same conflict resolver serving one article, several articles, or all the articles comprising a publication.
If you plan to use the default priority-based conflict resolver, you do not have to set the resolver property of an article. If you want to use an article resolver instead of the default resolver, you must set the resolver property for the article that will use it by selecting an available resolver on the Publisher. Any specific information that needs to be passed to the resolver can also be specified in the resolver information property.
Merge replication offers four types of conflict resolvers:
- The default priority-based conflict resolver
The default resolution mechanism behaves differently, depending on whether a subscription is a client subscription or a server subscription. You assign priority values to individual Subscribers that use server subscriptions; changes made at the node with the highest priority win any conflicts. For client subscriptions, the first change written to the Publisher wins the conflict. For more information about subscription types, see the section "Subscription Types" in How Merge Replication Detects and Resolves Conflicts.
After a subscription is created, it cannot be changed from one type to another.
- A business logic handler
The business logic handler framework allows you to write a managed code assembly that is called during the merge synchronization process. The assembly includes business logic that can respond to conflicts and a number of other conditions during synchronization. For more information, see Executing Business Logic During Merge Synchronization.
- A COM-based custom resolver
Merge replication provides an API for writing resolvers as COM objects in languages such as Microsoft Visual C++ or Microsoft Visual Basic. For more information, see COM-Based Custom Resolvers.
- A COM-based resolver supplied by Microsoft
Microsoft SQL Server 2005 includes a number of COM-based resolvers. For more information, see Microsoft COM-Based Resolvers.
For information about how to select the appropriate type of resolver, see Choosing a Resolver.
Some article resolvers are written to handle conflicts only for certain operations. For example a resolver might handle updates, but not inserts or deletes. The default priority-based conflict resolver handles any conflicts not handled by the article resolver.
To specify a merge subscription type and conflict resolution priority
- SQL Server Management Studio: How to: Specify a Merge Subscription Type and Conflict Resolution Priority (SQL Server Management Studio)
- Replication Transact-SQL programming: How to: Create a Pull Subscription (Replication Transact-SQL Programming) and How to: Create a Push Subscription (Replication Transact-SQL Programming)
- Replication Management Objects (RMO) programming: How to: Create a Pull Subscription (RMO Programming) and How to: Create a Push Subscription (RMO Programming)
Replication supplies an Interactive Resolver user interface that can be used in conjunction with either the default priority-based conflict resolver or an article resolver. When performing on-demand synchronization through Microsoft Windows Synchronization Manager, the Interactive Resolver displays conflict data at run-time, and lets you choose how to resolve conflicts. For more information about how to enable interactive resolution and launch the Interactive Resolver, see Interactive Conflict Resolution.
The most straightforward way to view conflicts is to use the Replication Conflict Viewer, available from SQL Server Management Studio (SQL Server also provides stored procedures that allow the conflict tables to be queried.). The Conflict Viewer and Interactive Resolver are similar tools, but the Interactive Resolver allows you to resolve conflicts as synchronization occurs, whereas the Conflict Viewer is designed for viewing conflicts after they have been resolved. If the conflict metadata is still available in the system tables (conflict metadata is retained for 14 days by default), you can override conflict resolution outcomes in the Conflict Viewer, but if direct intervention is regularly required, consider using the Interactive Resolver.
Conflicts that involve logical records are not displayed in Conflict Viewer. To view information about these conflicts, use replication stored procedures. For more information, see How to: View Conflict Information for Merge Publications (Replication Transact-SQL Programming).
The Conflict Viewer displays information from three system tables:
- Replication creates a conflict table for each table in a merge article, with a name in the form MSmerge_conflict_<PublicationName>_<ArticleName>.
Conflict tables have the same structure as the tables on which they are based. A row in one of these tables consists of the losing version of a conflict row (the winning version of the row is in the actual user table).
- The MSmerge_conflicts_info table provides information about each conflict, including the conflict type.
- The sysmergearticles table identifies which user tables have conflict tables and provides information about the conflict tables.
By default, conflict information is stored:
- At the Publisher and Subscriber if the publication compatibility level is 90RTM or higher. For more information about compatibility level, see the "Compatibility Level for Merge Publications" section in the topic Using Multiple Versions of SQL Server in a Replication Topology.
- At the Publisher if the publication compatibility level is lower than 80RTM.
- At the Publisher if Subscribers are running SQL Server 2005 Compact Edition. Conflict data cannot be stored on SQL Server 2005 Compact Edition Subscribers.
To view conflicts
- SQL Server Management Studio: How to: View and Resolve Data Conflicts for Merge Publications (SQL Server Management Studio)
- Replication Transact-SQL Programming: How to: View Conflict Information for Merge Publications (Replication Transact-SQL Programming)