Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 1 '12 at 1:21

This question came from our site for professional and enthusiast programmers.

1 Answer 1

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=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();

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.

share|improve this answer
    
Ok, but there is not going to be any trouble if I sync between different SQL VERSIONS? –  user1867125 Nov 30 '12 at 18:32
    
It is recommended to use between databases with the SAME version (Sql server 2008 standard, enterprise, express). but with same version and service packs to avoid problems. Never tried with 2008 to 2005 but I read is not convenient. –  Carlos Lande Nov 30 '12 at 18:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.