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

Answer 1

This is an old question now, but since there s no real answer:

Sync requires you to list tables in each scope in insert order, so that all Foreign Key parents are in place before any Foreign Key children are inserted. Sync will automatically reverse that order on delete.

This is all fine and dandy, but if you have a database where for whatever reason the data in your parent or child tables is stored on different servers based on some independent piece of information, so that the parent and child might have different sync rules, you ve overstepped any automatic processing that s available.

In this case, where the normal sync filters are built against the primary key information in your BASE tables, you will need to force the filters to use the primary key information in the TRACKING tables instead. There is now some content about this on social.msdn.microsoft.com.

友情链接