Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Synchronize

Imagine that you want to have multiple copies of the same SQL Server databases located in different locations around the country or even around the world. Those SQL Server databases could exist in your headquarters, subsidiaries, retail stores, and even in your remote offices.  Currently, to accomplish this, there are a number of really great technologies to help you use on-premises software that you manage.  Some examples of this include Merge Replication and Sync Framework database providers (previously known as Sync Services for ADO.NET).  The most common concern that we hear with these technologies is the difficulty of getting the system up and running quickly and the complexity of the management requirements.  Quite often it requires working with IT to open holes in the corporate firewall and setting up web servers to host the synchronization logic.  What I would like to show you is an alternate way that you can accomplish this, by using a technique that removes the need to configure corporate firewalls or to install and configure web services.  With this technique you can take virtually any SQL Server database and share it with other users via Windows Azure.


To get started let’s begin by looking at a very basic example.  Imagine a company we will call Fabrikam that has a SQL Server in their New York headquarters.  They would really like to make an exact copy of that SQL Server database and make it available in their London subsidiary. Down the road they will also want to put one in their Tokyo office.  Fabrikam wants to be able to have the database local to each of these locations to remove any latency issues.  Ultimately, there will be users that make changes to both of these databases, so periodic synchronization will need to take place to move changes to and from each of these locations.


One of the ways that this can be accomplished is through the use of SQL Azure and in particular SQL Azure Data Sync.  SQL Azure is a fully managed relational database in the cloud.  This database is built on SQL Server technologies.  Using SQL Azure Data Sync, we can easily solve the first half of Fabrikam's problem, in that we can set up synchronization from their existing New York database and synchronize it to a SQL Azure database.  All of this can be done without any specific configuration to the corporate firewall (other than outbound only access on port 1433) and without the need to set up web services.  This is because SQL Azure Data Sync sets synchronization up within the SQL Server as a SQL Agent process that periodically pushes changes to and from the SQL Server and SQL Azure databases.  Since it makes outbound calls to the public SQL Azure database service there is no need to open holes in the corporate firewall.  The first part of the architecture looks like this:
There is a really good walkthrough document on how to set this up here: Microsoft Sync Framework Power Pack for SQL Azure.

The provisioning tool to set up synchronization between SQL Server and SQL Azure does not currently have support to extend this to other SQL Server databases.  However, Sync Framework, which powers SQL Azure Data Sync, does have the capability to support a number of databases, so although this scenario can not be configured automatically through the tool, with a little manual effort we can enable it as seen below.
                 

The first things we need to do are to back up the New York SQL Server database and restore it on the London server.  Next, since each database that is part of the synchronization ecosystem has unique attributes assigned to it, we will need to “reset” the synchronization state.  To do this we will use the SqlSyncStoreRestore (Sync Framework) class to update the synchronization metadata for the remote endpoint by calling the PerformPostRestoreFixup method.  After this you will be able to synchronize the London database as well.
In order to try to keep things a little simpler, I have included a simple C# command line application that allows you to do the SqlSyncStoreRestore against the copied database. 

To create this application, you will need to build a C# Visual Studio Project using the following 4 steps:

1. Open Visual Studio and choose: File | New | Project | Visual C# | Console Application. 
   
2. After the project is created, add a reference to Microsoft.Synchronization.Data.SqlServer.  To do this, within the Solution Explorer right click on Properties | Add Reference | Browse, and enter: C:\Program Files\Microsoft Sync Framework\2.0\Runtime\ADO.NET\V3.0\x86\Microsoft.Synchronization.Data.SqlServer.dll
   
3. Add the following code in your Program.cs file and update your ConnectionString to point to the database you restored. 
   
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Synchronization.Data.SqlServer;
using System.Data.SqlClient;


namespace ConsoleApplication9
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection serverConn = new SqlConnection();
            serverConn.ConnectionString = "Data Source=localhost;Trusted_Connection=True;Database=MYDATABASE";
            SqlConnection.ClearPool(serverConn);
            SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(serverConn);
            databaseRestore.PerformPostRestoreFixup();
        }
    }
}
4. Compile and run the application.

That should reset the synchronization information in the London SQL Server database and allow it to synchronize to SQL Azure just as the New York database has.  By the way, this code is a great way to create template databases that have data pre-provisioned in them so that you do not need to synchronize the initial data set.
To test this new database, you will need to add the same SQL Agent process on the London machine that you have on the New York machine.  If it is a separate machine you will also need to install the 32-bit version of the Microsoft Sync Framework 2.0 SDK (even if you are running on a 64-bit machine) as well as Sync Framework Power Pack for SQL Azure. If you are not familiar with SQL Agent, to find the command line that is being used on the New York database, open SQL Server Management Studio, connect it to the New York database, choose SQL Agent |Jobs, and then right click on the job that is of the format “SyncToSQLAzure-XXXXX” and choose properties.  Choose Steps | Edit and copy the contents from the text box.  It should look something like this:

"C:\Program Files\Microsoft Sync Framework\Power Pack For SQL Azure November CTP\SyncLocalSqlAzureDatabase.exe" -localServer localhost -localDb Archetype -SqlAzureDb MyDB -scope Sync_MyDB -SqlAzureServer XXXXXXX.database.windows.net  -SqlAzureUser sa -SqlAzurePassword passwordaf1786d3-jda5-4e55-9368-9kdffb2bdea3 -ConflictResolutionPolicy SqlAzureWins

You can either manually run this command line from the London machine or create a new SQL Agent that executes the same task.
That’s it!  At this point you have two SQL Server databases that can communicate with each other through a SQL Azure database.