locked
Synchronization Order Question RRS feed

  • Question

  •  

     

    The objective is to perform the following SQL changes

     

    Existing Data

    Category Table

                   

    Category_Code

    ABC

     

    Transaction Table

     

    Transaction_Id

    Category_Code

    1000

    ABC

     

     

    Changes Required

    Category Table

                   

    Category_Code

    DEF

     

    Transaction Table

     

    Transaction_Id

    Category_Code

    1000

    DEF

     

    This will be accomplished by the following SQL script:

     

    1.       Insert new Category record (DEF)

    2.       Update the existing Transaction record to use the new Category Code (DEF)

    3.       Delete the previous Category record (ABC)

     

     

    Upon synchronisation, the server database will applies the changes in the following order:

     

    1.       Delete the previous Category record (ABC)

     

    è This causes a foreign key constraint violation because of the existing references in the Transaction table.

     

    2.       Insert new Category record (DEF)

     

    3.       Update the existing Transaction record to use the new Category Code (DEF)

     

     

    While reading the Synchronisation Services Books Online, we found out that Synchronisation Services applies changes in the following order: Delete, Insert and Update.

     

    Although we could specify the order on how the changes will be applied for the Parent and Child tables (i.e. Category and Transaction), we were surprised that the Delete, Insert and Update SQL apply sequence was not the same sequence as how it was run on the original database.

     

    The Transaction table updates could have been done in a different manner (i.e. by performing a logical row deletion - creating and setting an is_active flag, rather than  physical deleting the Category row), these updates are being performed by our client's existing desktop application, which they are not planning to upgrade anytime soon.

     

    Any help would be much appreciated.
    • Moved by Max Wang_1983 Friday, April 22, 2011 5:04 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, September 16, 2008 6:42 AM

All replies

  • Can you try to put these 2 tables into the same SyncGroup object, making sure adding the Category (parent) first before the Transaction table (child table)?

     

    Thanks,

    Wednesday, September 17, 2008 3:37 AM
    Answerer
  • Another option is to reset the FK relationship on the client database such as On Delete Set NULL.  In this case if the primary key is deleted, the child table will set this PK value to NULL.  When the child table row is updated later on, the new PK value will be in place.  ( but I still think you should have these 2 tables in one SyncGroup.

     

    Thanks.

    Wednesday, September 17, 2008 3:51 AM
    Answerer
  • Yup, we're already using the SyncGroup object to include both tables in the correct order.

     

     

    Thursday, September 18, 2008 1:26 AM
  • Our client is reluctant to implement this change since they're relying on the FK constraints to maintain data integrity for the application.

     

    Another recommendation we had in mind was to perform the synchronisation process in 2 batches.

     

    1.       Perform all Category table  inserts

    2.       Perform all Transaction table updates

    3.       Call Synchronisation Agent

    4.       Perform all Category table deletes

    5.       Call Synchronisation Agent

     

     

     

    Thursday, September 18, 2008 1:44 AM
  • Before you go this approach, please be aware of that after the first Synchronize() is called, the latest anchor value from Server is saved on the client side which is going to be used as a starting point to enumerate changes in the next Sync.  We may have a chance to lose Delete(s) in the second sync.

    Example.

    Insert happend on timestamp 7.

    Upadte happend on timestamp 8.

    Delete happened on timestamp 9.

    (Assum no changes after so the server anchange is timestamp 9).

    When the first Synchronize() is called, Insert and Update are enumerated on the server and sent to the client.  Also the latest server anchor value 9 is also sent to the client.  So if second Synchronize() is starting, 9 is sent back the server and look for any changes after 9.  You can see Delete happened on timestamp 9 is going to be missing during change enumeration phase.

     

    Thanks.

     

    Friday, September 19, 2008 6:54 PM
    Answerer