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 Framework
but 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.