SQL Server to SQL Azure Synchronization using Sync Framework 2.1

I have just posted a webcast to Channel 9 that shows you how to extend the capabilities of SQL Azure Data Sync by writing a custom sync applications to enable bi-directional data synchronization between SQL Server and SQL Azure.  This enables you to add customization to your synchronization process such as custom business logic or custom conflict resolution through the use of Visual Studio and Sync Framework 2.1.

In this video I show you how to write the code to both setup (provision) the databases for sync and then to actually execute synchronization between the two databases.  During the setup phase the tables used for synchronization are created in the SQL Azure database and the associated tables required for synchronization are also automatically generated.

Below I have included the main code (program.cs) associated with this console application that allows me to syncronize the Customer and Product table from the SQL Server AdventureWorks databases to SQL Azure. Make sure to update it with your own connection information and add references to the Sync Framework components.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using Microsoft.Synchronization.Data.SqlServer;

using Microsoft.Synchronization.Data;

using Microsoft.Synchronization;

namespace SQLAzureDataSync

{

class Program

{

public static string sqlazureConnectionString = "Server=[Your SQL Azure Server].database.windows.net;Database=AdventureWorksLTSQLAzure;User ID=[Your SQL Azure User Name]@[Your SQL Azure Server];Password=[Your SQL Azure Password];Trusted_Connection=False;Encrypt=True;";

public static string sqllocalConnectionString = "Server=(local);Database=AdventureWorksLT2008;Trusted_Connection=True";

public static readonly string scopeName = "alltablesyncgroup";

static void Main(string[] args)

{

// Test if input arguments were supplied:

if (args.Length == 0)

{

System.Console.WriteLine("Please enter an argument.");

System.Console.WriteLine("Usage: SyncTest.exe -setup");

System.Console.WriteLine(" SyncTest.exe -sync");

}

else if (args[0] == "-setup")

Setup();

else if (args[0] == "-sync")

Sync();

}

public static void Setup()

{

try

{

SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);

SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);

DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);

DbSyncTableDescription Customer = SqlSyncDescriptionBuilder.GetDescriptionForTable("Customer", sqlServerConn);

DbSyncTableDescription Product = SqlSyncDescriptionBuilder.GetDescriptionForTable("Product", sqlServerConn);

// Add the tables from above to the scope

myScope.Tables.Add(Customer);

myScope.Tables.Add(Product);

// Setup SQL Server for sync

SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myScope);

if (!sqlServerProv.ScopeExists(scopeName))

{

// Apply the scope provisioning.

Console.WriteLine("Provisioning SQL Server for sync " + DateTime.Now);

sqlServerProv.Apply();

Console.WriteLine("Done Provisioning SQL Server for sync " + DateTime.Now);

}

else

Console.WriteLine("SQL Server Database server already provisioned for sync " + DateTime.Now);

// Setup SQL Azure for sync

SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(sqlAzureConn, myScope);

if (!sqlAzureProv.ScopeExists(scopeName))

{

// Apply the scope provisioning.

Console.WriteLine("Provisioning SQL Azure for sync " + DateTime.Now);

sqlAzureProv.Apply();

Console.WriteLine("Done Provisioning SQL Azure for sync " + DateTime.Now);

}

else

Console.WriteLine("SQL Azure Database server already provisioned for sync " + DateTime.Now);

sqlAzureConn.Close();

sqlServerConn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

}

public static void Sync()

{

try

{

SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);

SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);

SyncOrchestrator orch = new SyncOrchestrator

{

LocalProvider = new SqlSyncProvider(scopeName, sqlAzureConn),

RemoteProvider = new SqlSyncProvider(scopeName, sqlServerConn),

Direction = SyncDirectionOrder.UploadAndDownload

};

Console.WriteLine("ScopeName={0} ", scopeName.ToUpper());

Console.WriteLine("Starting Sync " + DateTime.Now);

ShowStatistics(orch.Synchronize());

sqlAzureConn.Close();

sqlServerConn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

}

public static void ShowStatistics(SyncOperationStatistics syncStats)

{

string message;

message = "\tSync Start Time :" + syncStats.SyncStartTime.ToString();

Console.WriteLine(message);

message = "\tSync End Time :" + syncStats.SyncEndTime.ToString();

Console.WriteLine(message);

message = "\tUpload Changes Applied :" + syncStats.UploadChangesApplied.ToString();

Console.WriteLine(message);

message = "\tUpload Changes Failed :" + syncStats.UploadChangesFailed.ToString();

Console.WriteLine(message);

message = "\tUpload Changes Total :" + syncStats.UploadChangesTotal.ToString();

Console.WriteLine(message);

message = "\tDownload Changes Applied :" + syncStats.DownloadChangesApplied.ToString();

Console.WriteLine(message);

message = "\tDownload Changes Failed :" + syncStats.DownloadChangesFailed.ToString();

Console.WriteLine(message);

message = "\tDownload Changes Total :" + syncStats.DownloadChangesTotal.ToString();

Console.WriteLine(message);

}

}

}

Comments

• Anonymous
  September 16, 2010
  This is great help! However when trying to do the intial sync for a table with 1.4 millions rows I'm running into timeout issues. Is there a way sync a certain amount of rows? Thanks,

• Anonymous
  September 19, 2010
  Usually what works for me is create an same initial db on both sides using BCP etc...than start the sync -as the name suggests.

• Anonymous
  September 24, 2010
  Hi ppez, I just added a post on this subject.  Can you check it out to see if it help? blogs.msdn.com/.../how-to-sync-large-sql-server-databases-to-sql-azure.aspx Liam

• Anonymous
  January 03, 2011
  Hi Liam, can we sync only the specific columns of a table  

• Anonymous
  February 11, 2011
  I was looking at this code and see one thing that looks off.  If you are trying to do unidirectional synchronization, the orchestrator sync providers are reversed.  They should read as shown below:                SqlConnection sqlServerConn = new SqlConnection(sqllocalConnectionString);                SqlConnection sqlAzureConn = new SqlConnection(sqlazureConnectionString);                SyncOrchestrator orch = new SyncOrchestrator                {                    RemoteProvider = new SqlSyncProvider(scopeName, sqlAzureConn),                    LocalProvider = new SqlSyncProvider(scopeName, sqlServerConn),                    Direction = SyncDirectionOrder.Download //UploadAndDownload for bidirectional sync, Download for cloud to local, Upload for local to cloud                };

• Anonymous
  March 20, 2011
  The comment has been removed

• Anonymous
  March 28, 2011
  Hello, Code all runs and the setup creates the extra table but when I run Sync I get this messasge The CLR has been unable to transition from COM context 0x1c130f20 to COM context 0x1c131170 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. ?

• Anonymous
  May 08, 2011
  If there any chema changes in the source db, sync failed. In this case we need to deprovison entire db (remove all objects related to previous sync) and sync again. For complete code please refer following link tuvian.wordpress.com/.../how-to-sync-schema-changed-database-using-sync-framework

• Anonymous
  June 29, 2011
  my on premise database is huge in size.I wanted to synchronize a table in that db into sql azure database.can u suggest me a better possibility for synchronization

• Anonymous
  August 10, 2011
  james - the Sync Fx API allows you to specify which columns you want to sync.

• Anonymous
  August 10, 2011
  riya - Liam has a post on how to sync large databases. see: blogs.msdn.com/.../how-to-sync-large-sql-server-databases-to-sql-azure.aspx

• Anonymous
  September 07, 2011
  tuvian.wordpress.com/.../how-to-sync-schema-changed-database-using-sync-framework

• Anonymous
  March 15, 2013
  Thanks JuneT for steering me in the right direction (previous post). Thanks Liam for a detailed article. I have successfully followed your article and am using Sync Framework 2.1 as you have outlined to sync between a SQL Server 2012 instance and SQL Azure.   SQL Server db provisions and syncs fine to SQL Azure db (Azure obviously provisions fine also) I want to now add other SQL Instances of the same database name to sync similarily to your article: How to Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Sync - Sync Team Blog - Site Home - MSDN Blogs.   When I copy the db (before it has been provisioned as per your Sync Framework 2.1 article) and then run the provision/sync code to sync a second remote db, I receive text in console as follows: Provisioning SQL Server... Done Provisioning SQL Server... SQL Azure already provisioned.... Then when I try to sync the 2nd db SQL Instance, I receive text in console as follows: ScopeName=ALLTABLESSYNCGROUP Starting Sync 3/14/2013 8:20:10 PM...... and then nothing. It just seems to lock (or end), with no end result. I have also applied your "Changes Applied event fired" code as I was previously timing out (db about 650,000 records).  First sync to fresh Azure db takes about 30 minutes.  Subsequent syncs takes 15 seconds.  I have left the 2nd db trying to sync for several hours, but still no "Changes Applied event fired" notice (and no movement). Any further insight on adding additional SQL Server instances to sync scenario as described in: SQL Server to SQL Azure Synchronization using Sync Framework 2.1?

• Anonymous
  March 21, 2013
  OK- so I answer my own post... Further diagnosis and trials lead to a very simple answer.   Only need to provision the first Instance.  Then sync.   Then detach and copy the applicable .mdf/.ldf files to a new directory.  Reattach the first Instance and then attach the copied .mdf file in the new directory to a second Instance.  There is no need to provision the second Instance again - it is already provisioned.  Just complete a sync.   The first Instance and second Instance will now sync via the SQL Azure db.   You can add as many remote site syncs as required using this method.

• Anonymous
  March 21, 2013
  @Sea Dog Mariner, are you running PerformPostRestoreFixup on for every copy of the original database that you are re-attaching?

• Anonymous
  March 25, 2013
  Still a work in progress.  I had used  PerformPostRestoreFixup in some previous trials when working on Liam’s blog “How to Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Sync”, but since removed it from my code.  So I assume that this must be accomplished immediately after db file is attached at RemoteOffice and before it is synced to Azure?

• Anonymous
  March 26, 2013
  JuneT: Do you have a sample of PerformPostRestoreFixup in vb code?  The WF for the attach/detach action is in vb, while the WF for the provision/sync action is in C#.

• Anonymous
  March 26, 2013
  JuneT: I guess I'd be looking for the vb equivilant of Liam's C# code below:            SqlConnection serverConn = new SqlConnection();            serverConn.ConnectionString = "Data Source=localhost;Trusted_Connection=True;Database=MYDATABASE";            SqlConnection.ClearPool(serverConn);            SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(serverConn);            databaseRestore.PerformPostRestoreFixup(); Since you mention a chance of sync ID issues without this procedure, I want to make sure we get it correct.

• Anonymous
  March 29, 2013
  yes, you have to do the PerformPostRestoreFixup before the synching the restored database for the first time.  and yes, that's the code for doing it.

• Anonymous
  May 13, 2014
  Dear liamca, What is happen if someone create new tables? Will it auto sync the new tables? BR, Khemarin

• Anonymous
  May 13, 2014
  The comment has been removed

• Anonymous
  December 26, 2014
  Awesome information, this beginner's guide to SQL Azure could also be useful sqlturbo.com/beginners-guide-to-microsoft-azure-sql-database

• Anonymous
  March 09, 2015
  The comment has been removed

• Anonymous
  January 06, 2016
  The comment has been removed

• Anonymous
  February 11, 2016
  This is great, but when I try this, I notice the tables are not being created in Azure and when I make table changes, nothing is happening.  Is there any thing I can do here?