Microsoft Sync framework issue

Question

I have been trying to use Microsoft Sync framework v2.1 to sync two MS SQL Server Databases, but It takes around 2 hours to sync only 35 tables with total number of records is less than 500,000 record which is not accepted at all.

So Am I missing anything that can be done to optimize performance as this is only a test data while production data is much more bigger. Also I have found that MS Sync Framework is comparing every record in source and destination tracking tables -every record in base table has a corresponding record in tracking table- while updates and inserts in only done in one of the two DBs only. Or should I just throw Sync framework away and use something else.

Here is the code I am using for provisioning and syncing.

Any help is appreciated.

Thanks in advance.

public void Initialize(string SourceConnStr, string DestConnStr)
    {
        SqlConnection src = null;
        SqlConnection dest = null;
        try
        {
            src = new SqlConnection(SourceConnStr);
            dest = new SqlConnection(DestConnStr);
            DbSyncScopeDescription scope = new DbSyncScopeDescription("SyncScopeDB");
            foreach (string tableName in tablesNames)
            {
                DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, dest);
                scope.Tables.Add(table);
            }
            SqlSyncScopeProvisioning srcProv = new SqlSyncScopeProvisioning(src, scope);
            srcProv.ObjectSchema = "SyncDB1";
            srcProv.Apply();

            SqlSyncScopeProvisioning destProv = new SqlSyncScopeProvisioning(dest, scope);
            destProv.ObjectSchema = "SyncDB2";
            destProv.Apply();
        }
        catch (Exception ex) { Console.Write(ex.Message); }
    }

public string Sync(string SourceConnStr, string DestConnStr)
    {
        SqlConnection src = null;
        SqlConnection dest = null;
        SampleSyncOrchestrator Orch = null;
        try
        {
            src = new SqlConnection(SourceConnStr);
            dest = new SqlConnection(DestConnStr);

            Orch = new SampleSyncOrchestrator(
                new SqlSyncProvider("SyncScopeDB", src, null, "SyncDB1"),
                new SqlSyncProvider("SyncScopeDB", dest, null, "SyncDB2"));
            var syncStats = Orch.Synchronize();
            Console.Write(syncStats.ToString());
        }
        catch (Exception ex) { return ex.Message; }
        return "Success";
    }

public class SampleSyncOrchestrator : SyncOrchestrator
{
    public SampleSyncOrchestrator(RelationalSyncProvider local, RelationalSyncProvider remote)
    {
        this.LocalProvider = local;
        this.RemoteProvider = remote;
        this.Direction = SyncDirectionOrder.Upload;
    }
}

score 0 · Answer 1 · answered Nov 15 '13 at 00:13

0

does the databases involved contain data already?

and no, Sync Fx doesnt compare source and destination rows. the _tracking tables are for tracking changes.

answered Nov 15 '13 at 00:13

JuneT

7,840
2
15
14

Microsoft Sync framework issue

1 Answers1