Microsoft Sync framework issue while synchronizing relational database

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("Test1", (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.

ansaurus

tags:

views:

answers:

Microsoft Sync framework issue while synchronizing relational database

related questions