Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Random Foreign Key Errors RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I've researched foreign key errors related to Sync, but have not found anything directly related to my scenario and was hoping somebody might have some suggestions. Here is the scenario:

    We have 60 plus databases spread across a dozen servers (about 5 to 6 databases per server) that we are consolidating into one unified database cluster. We are using triggers to go from the individual databases on each server to a local consolidated database on the same server. We are using triggers so we can munge the data as we go.

    We then use replication to send the data from the 12 consolidated databases to the unified cluster. Our mobile units then sync from the unified cluster.

    I have 2 tables (not the only ones but these are the ones that have the most issues) that give me an occasional error on their foreign key. When I build the SyncAdapters I make sure that I put the key table in first and then the foreign key table. We are also performing download only syncs to our mobile units. I have not been able to reproduce and it seems to be pretty random.

    To me this seems to be a timing issue between the time the records are being replicated and the sync being kicked off. Does anybody have any thoughts?
    Thursday, March 4, 2010 9:15 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Are the two tables within the same syncgroup?

    I've seen it happen in some cases too.

    In our case, the two tables are synched independent of one another.

    Let's say the replication is adding 1000 rows on the primary and say another 500 on the child table. Assuming you kicked in the sync when replication has just committed say row number 600 on the primary table. And assume the get new anchor command retrieves the timestamp on the 600th row as its upper bound limit for changes to download. The client then gets rows 1 to 600 but during the time it was downloading the changes, replication has finished the remaining 400 rows on the primary and the 500 rows on the child table as well. This in turn has already advanced the timestamp on the server when the next get new anchor fires up.

    Now, the client moves to sync the child table, fires up get new batch anchor and get's a new upper bound limit anchor for that sync session. It then tries to download the 500 rows on the child table, but then some of the parent rows for the child rows may not have been downloaded when it did the sync on the primary table earlier.


    Friday, March 5, 2010 1:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The table are in the same sync group and the key table gets added to the SyncAdapter first followed directly by foreign key table. I am adding a New Anchor Sync command to my source Provider. Do you know if the New Anchor command gets called for each table in the SyncAdapter or does it get called once and all tables within the SyncAdapter use the same anchor?
    Friday, March 5, 2010 12:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    AFAIK, if the tables are in the same syncgroup, the changes are selected once so am assuming they should use be using the same anchor.

    am assuming you added the tables to the SyncTables collection in the same order you added them to SyncAdapters? are you using batching? what's the client btw? if its SQL Compact, have you checked this http://support.microsoft.com/kb/974068?
    Friday, March 5, 2010 3:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I doubled check to make sure that we are adding them to the tables collection in the same order and we are. We use an XML file to define the tables and their columns that we sync and then we deserialize it and loop through adding them to the tables collection and adapters.

    Also, the client side is SQLExpress 2008 and the server side is SQL 2008. We use Change Tracking on the server side. We are also running Sync Framework v1 with ADO.NET 2.0. We don't do any batching and we created our own client provider that is inherited from the ClientSyncProvider. Within the client provider we use the DbServerSyncProvider for applying changes.

    Friday, March 5, 2010 6:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    1. When the FK error appeared, can we catch it in the event handler (ApplyChangeFailed) and find out what exact the issue is
    2. One thing you might want to check is the whether FK are defined the same on both the Source Server and the Target Server, typically the on UPDATE/DELETE action?

    Thanks,
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, March 7, 2010 5:33 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    Also when you configure the Sync in the Designer there is an option to Perform the Sync in one Transaction.
    If this is not selected it maybe opening and closing separate transactions per table.

    Hope this helps.

    Richard
    Monday, March 8, 2010 4:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    1. When the FK error appeared, can we catch it in the event handler (ApplyChangeFailed) and find out what exact the issue is
    2. One thing you might want to check is the whether FK are defined the same on both the Source Server and the Target Server, typically the on UPDATE/DELETE action?

    Thanks,
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.

    I can add the logic to capture that information in the ApplyChangedFailed event, however I have not been able to reproduce this in my development environment at will. It is very random.

    As for the FK's they are defined the same on both sides.
    Monday, March 8, 2010 10:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Also when you configure the Sync in the Designer there is an option to Perform the Sync in one Transaction.
    If this is not selected it maybe opening and closing separate transactions per table.

    Hope this helps.

    Richard

    I'll investigate this further, but do you know if committing a large transaction like this would cause a performance issue, especially on a re-initialize. From my experience committing large transactions could be a performance issue.
    Monday, March 8, 2010 10:35 PM