Synchronize SQL Server and SQL Azure using Sync Framework

[アーティクル]
04/12/2012

In this Blog, I will discuss Synchronizing data between an On-Premise SQL Server database and a SQL Azure database in the Cloud using Microsoft Sync Framework. There are others ways of synchronizing data between On-Premise SQL Server databases and SQL Azure databases, but this blog is focused on using Microsoft Sync Framework and C# code to synchronize data.

Before we begin you will need to download and install Microsoft Sync Framework from the Microsoft Download Center. You will also need to add references to the following DLL files after you have installed the Microsoft Sync Framework.

Microsoft.Synchronization.dll
Microsoft.Synchronization.Data.dll
Microsoft.Synchronization.Data.SqlServer.dll

In this example I am using the SyncDB database and the Products table. You can use this Walkthrough to create the SyncDB database.

I am also using a DBConnection class which stores and returns my database connection strings.

Now lets discuss the Synchronization. There are two ways to structure your application to synchronize data between SQL Server Database and SQL Azure Database. You could either use 2-tier architecture or N-tier architecture. The difference between the two is as follows:

2 - Tier Architecture - With this option the Sync Framework runs on the local computer and uses a SqlSyncProvider object to connect the SQL Azure database with a SQL Server database.

N – Tier Architecture – With this option the Sync Framework database provider runs in a Windows Azure hosted service and communicates with a proxy provider that runs on the local computer.

In this blog will implement a 2 – Tier Architecture.

To synchronize On-Premise SQL Server database with a SQL Azure database in the cloud we will complete the tasks below.

Define the scope based on the tables from the SQL Server database, then provision the SQL Server and SQL Azure databases.
This will prepare the SQL Server and SQL Azure databases for Synchronization.
Synchronize the SQL Server and SQL Azure databases after they have been configured for synchronization as per step 1.
Optionally if you wish you can use the SqlSyncDeprovisioning class to deprovision the specified scope and remove all associated synchronization elements from the database.

Task 1: Scope the SQL Server database and then provision SQL Server and SQL Azure databases.

Below are the Sync Framework Classes we will use to scope the SQL Server Database and then provision the SQL Server and SQL Azure databases,

DbSyncScopeDescription - Represents a synchronization scope, which is a logical grouping of tables (optionally filtered) that are synchronized as a unit.

DbSyncTableDescription - Represents the schema of a table that is included in the Tables list of a DbSyncScopeDescription object. This is used during database provisioning.

SqlSyncDescriptionBuilder - Represents scope and table information for a SQL Server database that is involved in synchronization.

SqlSyncScopeProvisioning - Represents the provisioning of a SQL Server database for a particular scope that is represented by a DbSyncScopeDescription object.

We will now write the method to scope and provision the databases.The first thing we will do is to create two SqlConnection objects to connect to the SQL Server and SQL Azure databases as shown below.

// Connection to on premise SQL Server database
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure database
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

We will then use the Sync Framework classes to create scope, choose the tables and choose the columns within the tables and add them to the scope. Below is the code which accomplishes the task.

We create the scope named “product” using the DbSyncScopeDescription class shown below.

DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

Then we select the columns from the dbo.Products table to be included by adding them to a Collections object. We select the ID, Name and ListPrice columns as shown below.

Collection<string> includeColumns = new Collection<string>();
includeColumns.Add("ID");
includeColumns.Add("Name");
includeColumns.Add("ListPrice");

We then define the schema for the dbo.Products table using the DbSyncTableDescription and SqlSyncDescriptionBuilder classes shown below.

DbSyncTableDescription productDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
includeColumns, onPremiseConn);

After the schema for the dbo.Products table has been defined we add it to the Tables collection of productScope as shown below.

productScope.Tables.Add(productDescription);

Now we create a SQL Sync Provisioning object using the SqlSyncScopeProvisioning class for the on premise SQL Server as shown below.

SqlSyncScopeProvisioning onPremiseProvision = new SqlSyncScopeProvisioning(onPremiseConn,
productScope);

We filter rows for the specific ListPrice column and then filter the rows based on the criteria defined below. [side] is an alias for the tracking table.

onPremiseProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
onPremiseProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice]<'600'";

if (!onPremiseProvision.ScopeExists("product"))
onPremiseProvision.Apply();

We create a SQL Sync Provisioning object for the SQL Azure database using the SqlSyncScopeProvisioning class as shown below.

SqlSyncScopeProvisioning azureProvision = new SqlSyncScopeProvisioning(azureConn,
productScope);

Apply the provisioning to the “product” scope if the scope does not exist.

if (!azureProvision.ScopeExists("product"))
azureProvision.Apply();

Finally close the SQL Server and SQL Azure database connections as shown below.

onPremiseConn.Close();

onPremiseConn.Dispose();

azureConn.Close();

azureConn.Dispose();

The complete Setup method for scoping and provisioning is shown below.

public static void Setup()

{
// Connection to on premise SQL Server database
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure database
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

// Create a scope named "product" and add tables to it.
DbSyncScopeDescription productScope = new DbSyncScopeDescription("product");

// Select the colums to be included in the Collection Object
Collection<string> includeColumns = new Collection<string>();
includeColumns.Add("ID");
includeColumns.Add("Name");
includeColumns.Add("ListPrice");

// Define the Products table.
DbSyncTableDescription productDescription =
SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Products",
includeColumns, onPremiseConn);

// Add the Table to the scope object.
productScope.Tables.Add(productDescription);

// Create a provisioning object for "product" and apply it to the on-premise database if one does
not exist.
SqlSyncScopeProvisioning onPremiseProvision = new SqlSyncScopeProvisioning(onPremiseConn,
productScope);

// Filter Rows for the ListPrice column
onPremiseProvision.Tables["dbo.Products"].AddFilterColumn("ListPrice");
onPremiseProvision.Tables["dbo.Products"].FilterClause = "[side].[ListPrice] < '600'";

if (!onPremiseProvision.ScopeExists("product"))
onPremiseProvision.Apply();

// Provision the SQL Azure database from the on-premise SQL Server database if one does not exist.
SqlSyncScopeProvisioning azureProvision = new SqlSyncScopeProvisioning(azureConn,
productScope);

if (!azureProvision.ScopeExists("product"))
azureProvision.Apply();

// Shut down database connections.

onPremiseConn.Close();

onPremiseConn.Dispose();

azureConn.Close();

azureConn.Dispose();

}

Task 2: Synchronize the SQL Server and SQL Azure databases

Now that we have the scope and provisioning complete we will write the method to Synchronize the databases.

We will use the Sync Framework SyncOrchestrator class below.

SyncOrchestrator - Initiates and controls synchronization sessions.

SqlSyncProvider - Represents a synchronization provider that communicates with a SQL Server database and shields other Sync Framework components from the specific implementation of the database.

The first thing we will do is to create create two SqlConnection objects to connect to the SQL Server and SQL Azure databases as shown below.

// Connection to on premise SQL Server database
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure database
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

We then create the SyncOrchestrator object as shown below.

SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

We then create SqlSyncProviders for SQL Server and SQL Azure as shown below.

SqlSyncProvider onPremiseProvider = new SqlSyncProvider("product", onPremiseConn);
SqlSyncProvider azureProvider = new SqlSyncProvider("product", azureConn);

Set the Local provider of SyncOrchestrator to the onPremise provider, the remote provider of SyncOrchestrator to the azureProvider provider and the the direction of SyncOrchestrator session to Upload and Download as shown below.

syncOrchestrator.LocalProvider = onPremiseProvider;
syncOrchestrator.RemoteProvider = azureProvider;
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

Create a SyncOperations Statistics Object and display the synchronization statistics as shown below.

SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);

Finally close the SQL Server and SQL Azure database connections as shown below.

onPremiseConn.Close();
onPremiseConn.Dispose();
azureConn.Close();
azureConn.Dispose();

Once the Synchronize method is executed successfully you will see the statistics as shown in the figure below.

You can also query the SQL Azure database from SQL Server Management Studio to make sure the synchronized data is in the table as shown in the figure below.

The complete Synchronize method is shown below.

public static void Synchronize()
{
// Connection to On-Premise SQL Server
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

// Perform Synchronization between SQL Server and the SQL Azure.
SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

// Create provider for SQL Server
SqlSyncProvider onPremiseProvider = new SqlSyncProvider("product", onPremiseConn);

// Set the command timeout and maximum transaction size for the SQL Azure provider.
SqlSyncProvider azureProvider = new SqlSyncProvider("product", azureConn);

// Set Local provider of SyncOrchestrator to the onPremise provider
syncOrchestrator.LocalProvider = onPremiseProvider;

// Set Remote provider of SyncOrchestrator to the azureProvider provider
syncOrchestrator.RemoteProvider = azureProvider;

// Set the direction of SyncOrchestrator session to Upload and Download
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

// Create SyncOperations Statistics Object
SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

  // Display the Statistics
     Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
     Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
     Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
     Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);

  // Shut down database connections.
     onPremiseConn.Close();
     onPremiseConn.Dispose();
     azureConn.Close();
     azureConn.Dispose();

}

Task 3: Deprovision the scope and remove all associated synchronization elements

After we use the Setup method to scope and provision the SQL Server and SQL Azure databases, the synchronization metatables, triggers and stored procedures are created. The figure below shows the metatables created for the Products table.

We can use the SqlSyncScopeDeprovisioning class to remove the metatables shown above in addition to removing scopes, templates, and other synchronization elements from a SQL Server and SQL Azure databases that were previously provisioned for synchronization.

First we create two SqlConnection objects to connect to the SQL Server and SQL Azure databases as shown below.

// Connection to on premise SQL Server database
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure database
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

Create two SqlSyncScopeDeprovisioning objects for Sql Server and SQL Azure.

SqlSyncScopeDeprovisioning onPremiseSqlDepro = new SqlSyncScopeDeprovisioning(onPremiseConn);
SqlSyncScopeDeprovisioning azureSqlDepro = new SqlSyncScopeDeprovisioning(azureConn);

We will now use the SqlSyncScopeDeprovisioning class's DeprovisionScope menthod which will deprovision the specified scope by removing all associated synchronization elements from the database and the SqlSyncScopeDeprovisioning class's DeprovisionStore method which will remove all synchronization metadata tables, triggers and stored procedures from the databases. Please see the code below for deprovisioning the scope for SQL Server and SQL databases.

onPremiseSqlDepro.DeprovisionScope("product");
onPremiseSqlDepro.DeprovisionStore();

azureSqlDepro.DeprovisionScope("product");
azureSqlDepro.DeprovisionStore();

Finally close the SQL Server and SQL Azure database connections as shown below.

onPremiseConn.Close();
onPremiseConn.Dispose();
azureConn.Close();
azureConn.Dispose();

The complete Deprovision method is shown below.

public static void Deprovision()
{
// Connection to on premise SQL Server database
SqlConnection onPremiseConn = new SqlConnection(DBConnection.ConnStringSQLServer);

// Connection to SQL Azure database
SqlConnection azureConn = new SqlConnection(DBConnection.ConnStringSQLAzure);

// Create Scope Deprovisioning for Sql Server and SQL Azure.
SqlSyncScopeDeprovisioning onPremiseSqlDepro = new SqlSyncScopeDeprovisioning(onPremiseConn);
SqlSyncScopeDeprovisioning azureSqlDepro = new SqlSyncScopeDeprovisioning(azureConn);

// Remove the scope from SQL Server remove all synchronization objects.
onPremiseSqlDepro.DeprovisionScope("product");
onPremiseSqlDepro.DeprovisionStore();

// Remove the scope from SQL Azure and remove all synchronization objects.
azureSqlDepro.DeprovisionScope("product");
azureSqlDepro.DeprovisionStore();

// Shut down database connections.
onPremiseConn.Close();
    onPremiseConn.Dispose();
    azureConn.Close();
    azureConn.Dispose();

}

The complete code for the WIndows Console Application is shown below.

using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace OnPremiseTOCloudSynchronization

{

class Program {

static void Main(string[] args) {

string answer = string.Empty;

string selection = string.Empty;

do
{

Console.WriteLine("\nEnter a choice below:");

Console.WriteLine(@"Type 'setup' to scope the database");

Console.WriteLine(@"Type 'sync' to sync the database");

Console.WriteLine(@"Type 'delete' to remove scope of the database");

Console.Write("Enter command: ");

selection =Console.ReadLine();

               if (selection.Equals("setup"))
Setup();
              else if (selection.Equals("sync"))
Synchronize();
              else if (selection.Equals("delete"))
Deprovision();

Console.Write("Do you wish to exit? (y/n): ");
answer = Console.ReadLine();

}
while (answer.Equals("y"));
}