none
LocalInsertRemoteInsert - what is the best way to keep all data? RRS feed

  • Question

  • Hi,
    I'm syncing two SQL Express 2008 DB's using SqlSyncProvider. and DyncOrchestrator.
    When I have new data rows on each of the DB's standard seems to overwrite because the Id of the rows is the same.
    Is there a simple way to keep all new data rows that were inserted since the last sync?
    The obvious way is to manually insert those rows to all DB's manually, but thats a lot of work and not quite elegant.

    Any Ideas welcome!
    thx
    Erik
    Monday, February 8, 2010 9:28 PM

Answers

  • Hi Erik,

    Your problem is actually a very common one if the database was not designed to be distributed across several tiers.
    See the MSF docs: http://msdn.microsoft.com/en-us/library/bb726011(SQL.105).aspx

    If you still have the chance to change your DB schema, I would suggest using one of the techniques in the article to choose a suitable primary key. This will solve your problem instead of trying to fix the symptoms. Then there won't be any conflict and there won't be a need to do any sort of operation. Then you can just let the Sync Framework do its 'magic'.

    HTH,
    Rudi
    Tuesday, February 9, 2010 9:40 AM

All replies

  • Hello Erik,

    If you insert a row at each end with same primary key, it will be handled as conflict as the case qualifies as insert-insert conflict.

    This will need to be resolved at conflict level. There is no resolution policy to keep both rows currently.

    Application will need to handles this within ApplyChangeFailed event by modifying the conflicitng row, or similar logic to

    resolve the conflict by looking at the source and destination rows within the event argument.

    Hope this helps,

    Thanks.

    Patrick

    • Proposed as answer by Patrick S. Lee Monday, February 8, 2010 10:26 PM
    Monday, February 8, 2010 10:25 PM
  • Another possibility is to have a [rowguidcol] to be the sync identity column and if there is a PK conflict raised, update one PK value.  However this approach needs a lot of manual work to modify the existing provisioning script.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, February 8, 2010 11:39 PM
    Answerer
  • Hi Patrick,

    So I have to select all the new entries on both sides, all the related tables as well, update the id, update the related tables and write the whole data to all dbs.

    Quite a lot of work for a very common task. Hard to believe MS people could not come up with a simple solution for it.
    But anyway seems I have to go that way.
    Does someone know where to find examples for this?

    thx
    Erik
    Tuesday, February 9, 2010 9:03 AM
  • Hi Erik,

    Your problem is actually a very common one if the database was not designed to be distributed across several tiers.
    See the MSF docs: http://msdn.microsoft.com/en-us/library/bb726011(SQL.105).aspx

    If you still have the chance to change your DB schema, I would suggest using one of the techniques in the article to choose a suitable primary key. This will solve your problem instead of trying to fix the symptoms. Then there won't be any conflict and there won't be a need to do any sort of operation. Then you can just let the Sync Framework do its 'magic'.

    HTH,
    Rudi
    Tuesday, February 9, 2010 9:40 AM
  • hi Erik,

    how did you set the insert and update timestamps values?

    As Patrick mentioned, this should have raised a ClientInsertServerInsert conflict and will have to be resolve by code since there is no default resolver for the providers you mentioned (there is a ConflictResolver on the SQLCE provider).

    Check out the Adding Columns to Track Insert and Update Operations section at http://msdn.microsoft.com/en-us/library/cc305973.aspx

    As mentioned in the docs, if the timestamp values are not set properly, the inserts may appear to be have been updated immediately after the insert.

    I suspect in your case, it's applying the inserts as an update instead.
    Tuesday, February 9, 2010 10:59 AM
    Moderator
  • Hi JuneT,

    thanks for the link, but since I'm using SQL Server Change Tracking I think I do not need to worry about timestamps.
    Forgot to mention that in the original post, sorry.

    thx
    Erik
    Tuesday, February 9, 2010 1:02 PM
  • Hi Rudi,

    Thanks for the Link.
    I'll try the GUID as PK, looks quite good and simple enough.
    i will need to transfer existing data and update all foreign keys.

    thx
    Erik
    Tuesday, February 9, 2010 1:05 PM
  • BTW, If you are going to use GUID as PK, be aware of the performance issue's because of the default clustered index on the PK.

    There are numerous discussions around this topic (e.g: http://ayende.com/Blog/archive/2006/04/13/PrimaryKeysIdentityVsGuids.aspx)

    Cheers.
    Tuesday, February 9, 2010 1:16 PM