As described by Microsoft at this link , “Microsoft Sync Framework is a comprehensive synchronization platform that enables collaboration and offline scenarios for applications, services, and devices. Using Microsoft Sync Framework, developers can build applications that synchronize data from any source using any protocol over any network.” I suggest you read more about the framework at http://en.wikipedia.org/wiki/Microsoft_Sync_Framework before you proceed.
However, I found this technology very hard to implement in my project which requires two SQL Express servers to be synchronized. Most of the samples dealt with SQL Server and SQL Server compact edition on the client side. And that was not my case.
With a week of googling and coding, I finally crack the solution. And I am happy to share you the easier way to learn this framework.
Requirements:
1. Install the Microsoft Sync Framework 2.0 Software Development Kit (SDK) that can be downloaded here.
2. Visual Studio 2008/2010
Assumptions:
1. Server1 – with SQL Server Express installed
2. Client1 – with SQL Server Express installed
3. A table named “Customer” is created in Server1
Steps:
1. Provision the server first. This will create a tracking table of the selected table (Customer in our example), stored procedures and triggers so that can it can be used to synchronize the data. The following C# code will provision the server.
//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
string scope = “sync_customer“;
// Create a scope
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scope);
//GetDescriptionForTable gets the schema of the table, so that tracking tables and triggers
//can be created for that table
DbSyncTableDescription tableDetailsCustomer = SqlSyncDescriptionBuilder.GetDescriptionForTable(“Customer”, serverConn);
//add the table description to scope
scopeDesc.Tables.Add(tableDetailsCustomer);
//Create the scope provisioner
SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
//We have to skip the creation of the table Customer because it is already created
serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
//Now we can provision the
serverserverConfig.Apply(serverConn);
And here is the provisioned server that was created.
But that’s only half of our work. We have to provision the client, too.
2. Provision the client.
//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=Server1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//client connection
SqlConnection clientConn = new SqlConnection(@”Data Source=Client1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
//this should be THE SAME with the scope name we provisioned
//in the server earlier
string scope = “sync_customer“;
//get the sync description from the server
//take note of the connection where we will get the description
//it should be the serverConn
DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scope,serverConn);
//add description to scope provision
SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning(clientSqlDesc);
//apply it
//take note that we have to apply to the client connection clientConn
clientSqlConfig.Apply(clientConn);
3. Now we can synchronize the two tables
//server connection
SqlConnection serverConn = new SqlConnection(@”Data Source=Server1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//client connection
SqlConnection clientConn = new SqlConnection(@”Data Source=Client1\sqlexpress;Initial Catalog=Test;Integrated Security=True”);
//scope name
//should be equal to the one we provision in the server and client
string scope = “sync_customer“;
//create an orchestrator
SyncOrchestrator sync = new SyncOrchestrator();
//specify that we will upload to the server first then download from the server
sync.Direction = SyncDirectionOrder.UploadAndDownload;
//specify the client
sync.LocalProvider = new SqlSyncProvider(scope, clientConn);
//specify the server
sync.RemoteProvider = new SqlSyncProvider(scope, serverConn);
//synchronize it
sync.Synchronize();
