Remove From My Forums

SQL Change Tracking and Microsoft Sync Framework

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

Asked by:

SQL Change Tracking and Microsoft Sync Framework

Question