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