Microsoft Synchronizer Framework

Posted on

Question :

I’m planning to synchronize a pc that has SQL Server 2008 running on it, and the server has a SQL Server 2008 R2 running on it. Are there going to be any problems with data loss or compatibility?

Answer :

We use Microsoft Sync Framework at enterprise to sync in one way or two ways several
database tables. It works amazing!. Sync framework is totally compatible with SQL 2008 R2 and you wont have any data loss if you configure it well.

The only thing you need to do is provisionate the source and destination tables, and you
can do it with .NET to have automatic task.

Here you have a microsoft example to sync SQL DATABASES with .sql setup files and project
example:

http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab/view/SourceCode#content

Here you have a full example to sync data between sql servers:

http://rvramos.wordpress.com/2010/10/19/learning-microsoft-sync-framework-2-0-the-easier-way/

Provisioning server example:

//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);

Sync Example:

//server connection 
SqlConnection serverConn = new SqlConnection(@”Data Source=Server1sqlexpress;Initial Catalog=Test;Integrated Security=True”); 
//client connection 
SqlConnection clientConn = new SqlConnection(@”Data Source=Client1sqlexpress;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();

EDIT:

It is recommended to use between databases with the SAME version (Sql server 2008 standard, enterprise, express). but with same version to avoid problems. Never tried with 2008 to 2005 but I read is not convenient.

Leave a Reply

Your email address will not be published.