locked
Sync Framework seems to hang RRS feed

  • Question

  • Hi,
       I´m using MSF 2.0, and i´m synchronizing a server relational DB (SQL Server Enterprise) with a local relational DB (SQL Server EXPRESS) using the builtin SqlSyncProvider. When the database is new, it synchronizes in few seconds. Then, after inserting some data, it seems to synchronize forever. I waited for about 30 minutes and nothing happened. Then, i created a new DB and started to work again..and again, after a while, it stop working. The thing that worries me is that the amout of data inserted in the database is really small (about 10 registers) so i´m suspecting that the real problem is something else. 

    Thanks in advance
    Paola

    One thing that i noted is that this problem appeared when i added table relations in the database. So, the synchronization process works when the database doesn´t have relations, and stop working when i add them. I suspect that there can be a loop between the relations that can cause the synchronizer to stop working. Can it be the case? if this is true, is there a way to support this kind of relations in MSF?
    Friday, October 2, 2009 6:58 PM

All replies

  • the relateionships among tables ( i.e. RI-s ) should be supported by the sync services. could you please try to nail down this case a bit ? how many tables are involved in this case ? how complex the schema is ? does it seem hang when getting changes or applying changes ?

    thanks
    yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 6, 2009 12:59 AM
    Moderator
  • Hi Paola,

    any further info on this ?

    thanks
    yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 8, 2009 11:29 PM
    Moderator
  • Hi,
       I´ve been investigating this issue a little bit. It seems not be related to the amout of tables that i want to synchronize since it hangs always, even if i want to apply or get one or many changes. It seems that something got broken, and after this, the synchronization process stops working at all. One thing i´ve tried is to run a small program, that given a database, returns the order of the tables acording to the relations. What happened here was that the program failed in an "out of memory", thing that confirmed my suspect about relational loops. Now if there is a loop in the relations how does Sync Framework work in this case? I´m giving a table order to the syncrhonizer which i think does not help when loops exists in the relations...am i right?

    Thanks in advance
    Paola
    Tuesday, October 13, 2009 7:00 PM
  • Just curious, can you turn on the SQL profiler to profile both SQL enterprise and SQL express and see if we get into the infinite loop?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 13, 2009 8:44 PM
    Answerer
  • mmm, I see that the EXPRESS version does not have that feature :(
    Tuesday, October 13, 2009 9:05 PM
  • Hi,
       Is there another way to debug? the sql express version does not have SQL profiler.
       One thing that i´ve tried was the following: I´ve created the relational databases (Server and local) for a specific user and created the user scope on that DBs. This user didn´t have problems synchronizing but, when i created my own local DB in order to be able to synchronize with the same Server DB (added my own scope, too) that that user was synchronizing with, the synchronization didn´t work, meaning that it hung. So i couldn´t synchronize even once. One thing that i´ve tried was to remove all the info from the tracking tables and to re initialize the scopes info. After that cleaning, the synchronizer stopped hanging but now it does not synchronize the data, that is, it seems it finishes ok, but data is not being synchronized to the local DB. And stopped working for the first user that was able to synchronize, too.

    Regards
    Paola
    Friday, October 23, 2009 1:45 PM
  • Hi,
       Ive succeeded to debug the problem using SQL profiler, and i definetly can confirm that the problem is the circular relations of the DB. For example, the DB has a table named Person with a self-relation, that is, it has a relation named FK_Person_Person. Once i´ve added 2 registers to the table person and tried to synchronize the DB, the SQL profiler shows that the synchronizer hangs (when trying to download data to the client DB) calling the following SP in an infinite loop:
         

    1.

    exec [Person_selectrow] @P_1='7CA57C57-C484-4635-A400-0B5DCE7EDD2C',@sync_scope_local_id=2,@sync_scope_restore_count=0

     

    2.

    RPC:Completed               declare @p18 int

    set @p18=0

    exec [Person_insert] @P_1='7CA57C57-C484-4635-A400-0B5DCE7EDD2C',@P_2=N'Eric',@P_3=NULL,@P_4=N'Delahaye',@P_5=N'',@P_6=NULL,@P_7=NULL,@P_8=NULL,@P_9=NULL,@P_10='95840F51-270E-44BF-991A-EC31FB219113',@P_11='C4469536-CBF8-4AC3-AA40-2A06633C3060',@P_12=NULL,@P_13='17A367A1-6282-42F0-8128-1F4DE46BCFFA',@P_14=N'pmoguillansky',@P_15=N'edelahaye',@P_16=NULL,@P_17=NULL,@sync_row_count=@p18 output

    select @p18       .Net SqlClient Data Provider      edelahaye          HEXACTA\edelahaye     0             14           0             0             7924                53           2009-10-23 16:33:14.783               2009-10-23 16:33:14.783                0X00000000130000001E005B0050006500720073006F006E005F0069006E0073006500720074005D004400000048002000241075006E0069007100750065006900640065006E007400690066006900650072000800400050005F003100577CA57C84C43546A4000B5DCE7EDD2C3800000082001800E7306E00760061007200630068006100720028003200300029000800400050005F0032000800000045007200690063002C00000082001800E7506E00760061007200630068006100720028003200300029000800400050005F0033004000000082001800               

     

    Now if there is a loop in the relations how does Sync Framework work in this case? I´m giving a table order to the synchronizer which i think it does not help when loops exist in the relations...am i right? What should i do in these case? Does sync Framework provide a way to detect concurrency violations that let me replace the DB relations for it?

     

    Thanks in advance,

    Paola

     

    Friday, October 23, 2009 8:20 PM
  • Hi Paola,

    how is your conflict resolution logic looks like ? i.e. the logic you put in the ApplyChangeFailed Event ? my suspection is that the re-try is used so that you get into the infinate loop with this case.

    as a general recommendataion, I would suggest to re-design your db schema to avoid the circular relationships among tables. this is not a good pratice anyway and error prune anyways.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, October 26, 2009 6:30 PM
    Moderator
  • Hi Yunwen,
       Yes, that is exactly the problem, combined with the reflexive relationship on the Person table. My AppliedChangeField Event has the following if statement:

          if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
          {
              e.Action = ApplyAction.RetryWithForceWrite;
          }

        which causes the synchronization to loop after getting the conflict caused by the reflexive relationship. As workaround this IF statement was replaced by the following one:

           if (e.Conflict.Type == DbConflictType.ErrorsOccurred)
          {
            //This is the case in which the sync fails due to a loop found between
            //tables relashionships.
            if (e.Error.Message.Contains("FOREIGN KEY"))
            {
              e.Action = ApplyAction.RetryNextSync;
              needSyncIteration = true;
            }
            else
            {
              e.Action = ApplyAction.RetryWithForceWrite;
            }
         }

        The reason the message string is checked is because i haven´t found any other way to detect this type of problem, since the conflict type is too generic (ErrorsOccurred). Tell me if you have any other suggestion about how to detect this type of problem.
        Then the needSyncIteration flag will indicate if there is a need for another synchronization iteration to happen immediatly after the current one, meaning that in this case, the whole synchronization process will take more than one synchronization iteration to finish.

    Regards,
    Paola

        
    • Proposed as answer by amir9898 Thursday, November 5, 2009 11:56 AM
    Monday, October 26, 2009 9:26 PM
  • In my case with foriegn keys tabels sync error (Failed insert because FOREIGN KEY) i  have just rearrage the

    InitializeSyncTables procedure in the SyncAgent CLASS with an order that follows the FOREIGN KEY logic,

    For examle if TASK TABLE has FOREIGN KEY to PROJECT TABLE i add first the PROJECT TABLE to the the
    sync group and secondly the TASK TABLE that way i did not have a conflict when i insert new PROJECT and new TASK (Relate to that new project) in the same sync cycle.

    Thursday, November 5, 2009 12:00 PM
  • Hi ,
       Thanks for the response. My problem is that i´m using SqlSyncProvider s so i don´t have syncAgents, and no syncAdapters, so i don´t know where to add the logic to solve concurrency conflicts when they occur. I mean, i only have the AppliedChangeField  Event, which indicates the appopiate ACTION (RetryWithForceWrite, RetryNextSync, etc) as showed above, but and nothing more than that. Evidently, i´m missing something here, right?

    Regards
    Paola
    Thursday, November 5, 2009 2:58 PM
  • In my case with foriegn keys tabels sync error (Failed insert because FOREIGN KEY) i  have just rearrage the

    InitializeSyncTables procedure in the SyncAgent CLASS with an order that follows the FOREIGN KEY logic,

    For examle if TASK TABLE has FOREIGN KEY to PROJECT TABLE i add first the PROJECT TABLE to the the
    sync group and secondly the TASK TABLE that way i did not have a conflict when i insert new PROJECT and new TASK (Relate to that new project) in the same sync cycle.


    Hello

    I am facing a similar "foreing key - relationship" problem with the Sync Framework. The records of a child table is saved before the records of the parent table in the local database and it fails because of the foreign key constraint.

    Is it a limitation of Syn Framework "by design" ?
    Is the workaround proposed by Amir9898 is the unique solution to this problem ??? :-/


    Regards,

    Littlesteps

    Tuesday, December 8, 2009 2:33 PM
  • Hi,

    There seems to be a way to synchronize self-referencing tables with Sync Framework by doing it in two passes. Look here for more details:

    http://www.8bit.rs/blog/index.php/2009/12/replicating-self-referencing-tables-and-circular-foreign-keys-with-microsoft-sync-framework/


    Regards!
    Monday, December 14, 2009 12:48 PM