locked
SQL Change Tracking and Microsoft Sync Framework RRS feed

  • Question

  • I'm kind of new with databases and SQL and I'm struggling trying to understand how SQL Change Tracking and Microsoft Sync Framework work together.

    I couldn't find some clear examples about how to sync databases with Microsoft Sync Frameworkbut hopefully I found this site, modified the code and got syncing working on my two databases, here is the code I got:

    // Server connection
            using (SqlConnection serverConn = new SqlConnection(serverConnectionString))
            {
                if (serverConn.State == ConnectionState.Closed)
                    serverConn.Open();
    
                // Client connection
                using (SqlConnection clientConn = new SqlConnection(clientConnectionString))
                {
                    if (clientConn.State == ConnectionState.Closed)
                        clientConn.Open();
    
                    const string scopeName = "DifferentPKScope";
    
                    // Provision Server
                    var serverProvision = new SqlSyncScopeProvisioning(serverConn);
                    if (!serverProvision.ScopeExists(scopeName))
                    {
                        var serverScopeDesc = new DbSyncScopeDescription(scopeName);
                        var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, serverConn);
    
                        // Add the table to the descriptor
                        serverScopeDesc.Tables.Add(serverTableDesc);
    
                        serverProvision.PopulateFromScopeDescription(serverScopeDesc);
                        serverProvision.Apply();
                    }
    
                    // Provision Client
                    var clientProvision = new SqlSyncScopeProvisioning(clientConn);
                    if (!clientProvision.ScopeExists(scopeName))
                    {
                        var clientScopeDesc = new DbSyncScopeDescription(scopeName);
                        var clientTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table, clientConn);
    
                        // Add the table to the descriptor
                        clientScopeDesc.Tables.Add(clientTableDesc);
    
                        clientProvision.PopulateFromScopeDescription(clientScopeDesc);
                        clientProvision.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                        clientProvision.Apply();
                    }
    
                    // Create the sync orchestrator
                    var syncOrchestrator = new SyncOrchestrator();
    
                    // Setup providers
                    var localProvider = new SqlSyncProvider(scopeName, clientConn);
                    var remoteProvider = new SqlSyncProvider(scopeName, serverConn);
    
                    syncOrchestrator.LocalProvider = localProvider;
                    syncOrchestrator.RemoteProvider = remoteProvider;
    
                    // Set the direction of sync session
                    syncOrchestrator.Direction = direction;
    
                    // Execute the synchronization process
                    return syncOrchestrator.Synchronize();
                }
            }
    

    So on this way any changes are synchronized between my two databases. But I wanted a way for my C# app to automatically synchronize both databases when something changes so I found something called Change Tracking here. I downloaded the example code that provides a SynchronizationHelper that also creates tables in my databases called "{TableName}_tracking". This is another table that tracks the changes and indeed it does, whenever I change something in my database the _tracking is updated with the elements I changed, added or removed. Change Tracking doesn't automatically synchronize my databases, it just keeps track of the changes in them, what's the purpose of this?

    With the first code, synchronization works but no _tracking table is created, does it just synchronize everything in the table no matter what changed? If that's the case, for big databases I should be using Change Tracking?

    Maybe this is something trivial but I have been googling and testing a lot of code but I can't find a clear answer.



    • Edited by Andres Torti Wednesday, February 24, 2016 3:14 AM
    • Moved by DotNet Wang Friday, February 26, 2016 9:51 AM Sync Framework related
    Wednesday, February 24, 2016 2:30 AM