locked
Sync framework issue while synchronizing relational database RRS feed

  • Question

  • I am trying to use Microsoft Sync Framework for syncing 2 SQL Server 2005 database (server and client). There are multiple tables in the database with lots of foreign key relation between them. I am using SyncOrchestrator to synchronize the two databases.

    string clientConnectionString = "<connection string>";
    string serverConnectionString = "<connection string>";
    
    SqlSyncProvider localProvider 
    	= ConfigureClientProvider(clientConnectionString);
    SqlSyncProvider remoteProvider 
    	= ConfigureServerProvider(serverConnectionString);
    
    SyncOrchestrator orchestrator = new SyncOrchestrator();
    orchestrator.LocalProvider = localProvider;
    orchestrator.RemoteProvider = remoteProvider;
    orchestrator.Direction = SyncDirectionOrder.Download;


    In the function ConfigureClientProvider and ConfigureServerProvider I am initializing connection and checking if scope doesn't exits then create it:

    public static SqlSyncProvider ConfigureClientSyncProvider()
    {
    	SqlSyncProvider provider = new SqlSyncProvider();	
    
    	provider.Connection = new SqlConnection(Configs.ConnectionString);
    
    
    	DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("Test1");
    
    	SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning();
    
    	if (!serverConfig.ScopeExists(Configs.ScopeName, (System.Data.SqlClient.SqlConnection)provider.Connection))
    	{
    		scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable
    			("Employees", (SqlConnection)provider.Connection));
    		scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable
    			("Profiles", (SqlConnection)provider.Connection));
    		scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable
    			("Department", (SqlConnection)provider.Connection));
    
    
    		serverConfig.PopulateFromScopeDescription(scopeDesc);
    
    		serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    		serverConfig.Apply((System.Data.SqlClient.SqlConnection)provider.Connection);
    	}
    
    	return provider;
    }
    

    Now when I try to run sync its works fine for updated data but I got foreign key issues while there are any inserts or deletes in the database. e.g.

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_Departments". The conflict occurred in database "Sync_Client", table "dbo.Departments", column 'DepartmentID'.

    If I do some change in order of tables then I am able to resolve one case of another case arises because of deletion.

    The DELETE statement conflicted with the REFERENCE constraint "FK_Employees_Departments". The conflict occurred in database "Sync_Client", table "dbo.Employees", column 'DepartmentID'.

    Does anyone have any idea how this can be fixed. What I think the sync framework is not able to property executing changes in correct order. This order depending on several factor like foreign key relations, type of command e.g. insert, update etc. I am really stuck here. Early help will be appreciated.
    Thursday, January 21, 2010 11:23 AM

Answers

  • 1. Since [Employees] table has FK pointing to [Departments] and [Profiles] tables, then [Employees] is a child table and both [Departments] and [Profiles] are parent tables.  In this case, we should add [Departments]/[Profiles] before [Employees] table to the same sync scope object in code.
    2. In the order of [Departments > Profiles > Employees ], doing INSERT is fine during sync - from what you said.  But we see constraint confilct during apply DELETE.  And this is the DELETE action on the parent table [Departments].

    Please check, on the source replica database, do you have DELETE cascade on the PK/FK refernece between [Department] and [Employees] table.  And Please also check for the same DELETE cascade on the same PK/FE reference, on the destination replica database.

    If you still see both are the same with delete cascade, then please pass the definition of the PK/FK from the database diagram above and the DML you have done on the source replica before sync for repro.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 25, 2010 7:05 PM
    Answerer

All replies

  • When table description is added to the scope description, we need to add the Parent table before the Child table.

    If this does not help you, please provide your table schema of [Employees], [Profiles] and [Department].

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 22, 2010 1:29 AM
    Answerer
  • He Leo Zhou,

    Yes you are right. Changing the order of tables solves the problem for newly added records. 

    If I choose the order Departments > Profiles > Employees then I no more see below error:

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_Departments". The conflict occurred in database "Sync_Client", table "dbo.Departments", column 'DepartmentID'.

    But now if there is any deletion then I got following error
    The DELETE statement conflicted with the REFERENCE constraint "FK_Employees_Departments". The conflict occurred in database "Sync_Client", table "dbo.Employees", column 'DepartmentID'.

    Again changing the order to Employees > Profiles >  Departments solves the delete problem. But I can't have 2 different order for different case. Moreover if there are both newly added records and deletions then it will fail in atleast one case.

    For you reference here is the DB design: http://www.dailycoding.com/FileSharing/syncdb_relations.jpg

    Friday, January 22, 2010 4:15 AM
  • One thing you can try while I am looking and analysing your sync issue here is -

    without doing sync, can you repeat the same DML actions, same DML sequence of these 3 tables on the destination replica which you have performed exactly on the source replica?

    Are 3 tables have the exact same schema (including PK/FK cascading relationship) on both databases?

    Thanks.

    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 22, 2010 8:27 PM
    Answerer
  • Yes, I am able to do these.
    Monday, January 25, 2010 6:16 AM
  • 1. Since [Employees] table has FK pointing to [Departments] and [Profiles] tables, then [Employees] is a child table and both [Departments] and [Profiles] are parent tables.  In this case, we should add [Departments]/[Profiles] before [Employees] table to the same sync scope object in code.
    2. In the order of [Departments > Profiles > Employees ], doing INSERT is fine during sync - from what you said.  But we see constraint confilct during apply DELETE.  And this is the DELETE action on the parent table [Departments].

    Please check, on the source replica database, do you have DELETE cascade on the PK/FK refernece between [Department] and [Employees] table.  And Please also check for the same DELETE cascade on the same PK/FE reference, on the destination replica database.

    If you still see both are the same with delete cascade, then please pass the definition of the PK/FK from the database diagram above and the DML you have done on the source replica before sync for repro.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 25, 2010 7:05 PM
    Answerer
  • Thank you so much. This saved my day!!

    Tuesday, August 29, 2017 4:22 AM