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?
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
Here you have a full example to sync data between sql servers:
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);
//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();
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.