locked
Merge Conflict Resolution without using SSMS RRS feed

  • Question

  • So, 

    We have a scenario that requires a level of "conflict resolution" that is not readily explained in all the Replication Documentation on MSDN (or at the very least is not easily found).

    So far I have found the EnumMergeConflictCounts() and EnumConflictTables() on the ReplicationDatabase class in the SMO/RMO libraries.  (I know this I primarily RMO but it is dependent on SMO).  These are great in telling me what articles had conflicts, and an initial entry point to discovering what the conflicts are should I wish to proceed further.  However, this seems pretty much only that amount of information (no more) and if I want more I have to go to the specific MSMerge_conflict* tables for the indicated articles to get what the conflicts where.  (I maybe wrong, so please feel free to correct).

    The problem we have is such:

    Much of our system is based on Audit tracking, and thus we utilize 1-1 style Junction Tables.  To elucidate further:
    Cabinets, Televisions, Cabinet_Television.  Televisions is only the list of the types of Televisions, and Cabinets is the list of the physical Cabinets.  Cabinet_Television retains a link to both tables with an Installed and Removed date columns respectively.  The Cabinet_Television record that is Installed but with a REmoved of NULL is the current Television in the cabinet.  If they install a different Television, the current Cabinet_Television record has it's "Removed" column assigned and a New Cabinet_Television record is created pointing with a REmoved of NULL. 

    Generally straight forward system (and we have this scenario in Multiple places throughout our database schema). 

    However, as you can guess, if two "subscriber" systems, which are not currently connected to the Publisher's network, will be able to "install" the same Television in the same Cabinet. (or perhaps different Televisions in the same Cabinet) and on their independent database they will generate unique Cabinet_Television records.  Upon synchronization, they end up with two televisions installed in the same Cabinet at the same time, which is invalid.

    Now, granted, this is a business logic decision that we have to come up with our own solution, which may or may not include an interactive "resolver" for the User of our application.  However, it is the nature of how to do this resolution that has got me baffled.  I can use the EnumMergeConflictCounts() to detect conflicts, and/or using the EnumConflictTables() to get where the conflicts exist, but I have not seen any RMO (or SMO, though unlikely for this scenario)  methods or classes that would allow for the following:

    • Retrieve per ArticleConflict object the list of actual Conflicts.
    • Once the User selects the "Winner" to instruct the PUblisher/Publication with the "correct" value.
    • Remove the Conflict record and "mark as resolved".

    I realize, that for our system with these junction tables, we will need to handle the business logic ourselves in adjust our data with respect to the duplicate or additional "invalid" junction record and remove it or update it as necessary, but the resolution on the side of SQL Replication with changing the winner, and marking the conflict resolved is currently a mystery to me.

    Thanks

    Jaeden "Sifo Dyas" al'Reac Ruiner





    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

    Thursday, May 1, 2014 11:02 PM

Answers

  • For your question:

    it was as I thought, though one minor clarification:  what does SSMS do when you select the "winner/loser" which is opposite what it original determined?  Does it simply use SQL to take the values from the conflict table and replace the ones for the same record in the real table?

    ------------------------------------------------

    Let me explain how the merge replication deal with the conflict.

    1. When the merge replication detects the conflict, firstly it will check the conflict resolving rule. By default, it is the pub WIN.

    2. the merge agent gets the win data and lost data, it follows the rule to apply the win data on the real table on the publication site. This win data will be applied to each subsriptions on the next merge agent sync.

    3. the merge agent saves the lose data into the article mapped conlict table with rowguid on the publication database.

    When you click the view confict detail, the SSMS uses the rowguid to get the win data from the real table on the publication database, and get the lose data from the conflit table on the publication, and return.

    regards,

    Ling

    • Proposed as answer by tracycai Monday, May 12, 2014 2:59 AM
    • Marked as answer by tracycai Friday, June 6, 2014 2:50 AM
    Thursday, May 8, 2014 8:02 AM

All replies

  • Hi,

    This is a quick reply to let you know that I'm trying to involve someone familiar with this topic to further look at this issue.

    There might be some time delay.

    Best Regards,
    Tracy Cai


    Tracy Cai
    TechNet Community Support

    Monday, May 5, 2014 7:26 AM
  • You have three options here. 1) business logic resolver, 2) stored procedure resolver, 3) user triggers.

    If you choose to use the interactive resolver, this means that while the sync is going on, it will stop and a dialog will pop up on the screen and the user will be prompted to resolve the conflict. Unless you have someone at the desktop watching for this dialog - it is not a good idea.

    The way a conflict works is a change occurs on the same row or PK value between synchronizations. During the sync the merge engine picks one row as the winner, and this row is persisted on the publisher and subscriber (and possibly other subscribers depending on if the row goes to other subscribers).

    You have the option then of choosing after the conflict has been persisted, to change what row wins or not. By doing so, the winning change you choose is persisted on the publisher and subscriber the conflict entry is removed from the conflict tables.

    It sounds like you have two conditions here.

    1) on the publisher TV case 1 has TV 1. On a Subscriber TV Case 1 has TV 2. In essence your parent table has a different child.  You want only one parent to have 1 child. This is likely best done with a trigger on the subscriber which will be fired upon an insert in replication. The trigger would ensure that the publisher child row would persist, by deleting the offending child row (the subscriber side row). You can set the order that the articles are processed in so that the child table is processed before the parent table, so that all child rows make it to the publisher and the subscriber so your delete will delete a row which exists. A user trigger fired during merge operations will not flag the row to be processed again during subsequent merge synchronizations.

    2) on the Publisher TV case 1 has TV 1, On a Subscriber TV Case 1 has TV 1.  This should be resolved by a fk constraint which should be logged as a conflict.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, May 5, 2014 11:48 AM
    Answerer
  • Hrm.

    Though appropriately detailed, these all seem to revolve around "pre-resolving" conflicts (though 'pre' may be slightly inaccurate).

    The idea:  Publisher, Subscriber, and Merge Synchronization.  Every resolver (except one) seems to describe "resolve conflict during merge/synchronization".  This is NOT what we want at this point.

    Our system is too complex at the moment to maintain database triggers, and SQL based resolvers that it would be impractical to implement such things.

    However, if you go to SSMS, on the publisher side, expand the Replication | Local Publications | [Publication] node, right click, there is a "View Conflicts" item.  This first pops up a list of the tables and the number of conflicts per table, but then it shows a screen with the database, publication, and table selection, with a list of the Conflict loser type, etc.  This then allows a user to interactively navigate the tables with changes, what those changes were, the Conflict Winner/Loser and even seems to allow for a "Submit Winner"/"Submit Loser" that I expect allows the user to change the default resolution by the SQL engine.  As well in the top section there is a "remove" that allows you to remove the conflict with the winner/loser as is (or after changing them) so the conflict is longer retained as a "conflict".

    That is what I want to create, just within our application using SMO/RMO/SQL to achieve the same end as the "View Conflicts" action on a publication does is SSMS. 

    However, So far all I have found is the way to list the conflict counts and the conflict 'articles'.  So, to expand on that I would need to know:

    1. How to get the conflicted rows (which may require SQL on the MSMerge_conflict_Publication_TableName tables) or some other system.
    2. How to mimic or replicate via RMO/SQL the "Submit Winner" / "Submit Loser" behavior of the default SSMS conflict resolver that would allow me to change after the fact which row/submission should be retained and which one dropped.
    3. How to 'remove' the conflict the same as the "remove" button in the SSMS resolver.

    If these are only done through SQL, not a problem (though I would prefer RMO) yet, either way, I know I could manually map out in our system how to make the changes.  With the Results of the EnumConflictTables() I can easily construct the MSMerge_Conflict_* tables to get the actual rows, would be able to parse out which one Won and which one Lost, and then upon "resolving" these conflicts in our publisher database they would cascade down to the subscribers, and finally manually "deleting" the rows from the MSmerge_conflict_* tables that were processed.  I am just wondering how the "View Conflicts" action does these things so I may more efficiently write my own (tailored to our system of course) while using the correct SQL Replication approach to this process.

    (If post resolving were not the "correct approach" then why did SSMS include a post synchronization resolver????)

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.



    Monday, May 5, 2014 5:50 PM
  • Hi,

    I checked the issue, we haven't such kind of RMO or SMO to retrive per object conflict and remove the conflict record after the user choose the winner.

    The SSMS also only calls the enumerateconflictcount,  and then, follow the rowguid to retrieved the detail data from the tables, after the user select the winner and want to remove the conflict, the SSMS calls the sp_deletemergeconflictrow to remove. Therefore, you may follow the same way to do that.

    On the other side, if you want to design the your own logic to deal with the conflict automatically, we support customized stored procedure for conflict resolving. So you also can put your logic on the stored procedure and configure it into the merge replication confict resolving rule.

    Wednesday, May 7, 2014 2:52 AM
  • Okay,

    it was as I thought, though one minor clarification:  what does SSMS do when you select the "winner/loser" which is opposite what it original determined?  Does it simply use SQL to take the values from the conflict table and replace the ones for the same record in the real table?

    Thanks

    J"SD"a'RR


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.

    Wednesday, May 7, 2014 11:05 PM
  • For your question:

    it was as I thought, though one minor clarification:  what does SSMS do when you select the "winner/loser" which is opposite what it original determined?  Does it simply use SQL to take the values from the conflict table and replace the ones for the same record in the real table?

    ------------------------------------------------

    Let me explain how the merge replication deal with the conflict.

    1. When the merge replication detects the conflict, firstly it will check the conflict resolving rule. By default, it is the pub WIN.

    2. the merge agent gets the win data and lost data, it follows the rule to apply the win data on the real table on the publication site. This win data will be applied to each subsriptions on the next merge agent sync.

    3. the merge agent saves the lose data into the article mapped conlict table with rowguid on the publication database.

    When you click the view confict detail, the SSMS uses the rowguid to get the win data from the real table on the publication database, and get the lose data from the conflit table on the publication, and return.

    regards,

    Ling

    • Proposed as answer by tracycai Monday, May 12, 2014 2:59 AM
    • Marked as answer by tracycai Friday, June 6, 2014 2:50 AM
    Thursday, May 8, 2014 8:02 AM