How to Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Sync

How to Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Sync

Rate This
  • Comments 11

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:

Sync to SQL Azure 

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.

SQL Server Synchronization
 
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. 
  4. 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();

            }

        }

    }

  5. 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.  If you need more, you can follow the same process to add additional SQL Server databases. 
I’d love to hear your feedback on this!

For more details on how to get started with SQL Azure Data Sync, please visit our developer page.

Liam

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • Can the local SQL engine be SQL Server Express (2008 or otherwise)?

  • I beleive you can use any edition as long as port is not blocked by firewall.

  • That would be good, but I know when I looked at the Sync Framework in the past there was limited support for Express.

    I assume because in part it will cannibalise sales of the full SQL Server.

    I know Express doesn't have SQL Agent, but there are ways round that with third-party products etc.

  • @SF-Ben, actually we have support for SQL Express in the Sync Framework.  For SQL Azure Data Sync, we were not able to add support because it does not have SQL Agent (which SQL Azure Data Sync requires).  In the next release of SQL Azure Data Sync we are hoping to extend this to SQL Express.

    Liam

  • I have a very large database (approx 600GB) that needs to be syncronized between 2 facilities.  1 in TX and 1 in NC.  The TX facility has limited WAN bandwidth (T1 is the maximum badwidth available).  I would use merge replication over our WAN but the application developers built the DB using identity columns for 90% of the 300+ tables.  unfortunately the NC facility is coming online 2/1/2010 ( I've bee given the month of january to solve this problem ) and there is not enough time to rewrite the app and rearchitect the DB.

    Can SQL Azure host a DB that large?  Will it sync the identity columns or does it have the same limitations of normal SQL replication?  How will it resolve conflicts when a new record is created in NC and TX at the same time?

    Thanks for any guidance,

    Greg

  • Greg, the largest database you can host on Azure is 10gb.

  • The size limit settles that issue then.  Back to the drawing board I go.

  • I have a question..what if the user from London deletes a record from the database, and the user from New York modifies the same record that was deleted by the user from London??? how can I resolve that syncronization problem?

  • What about a new insert on a table with identity can this create a conflict? I know how to work around this using merge replications

  • I am getting the following exception thrown on the last line when running debug.

    line:  databaseRestore.PerformPostRestoreFixup();

    “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)”

    I am using Windows Authentication, Win7, SQLExpress2008.   I can successfully connect to the SQL Server Instance and db within Visual Studio 2012, but still get the exception when running debug.  Any ideas what is going wrong?

  •    I am getting the following exception thrown on the last line when running debug.

       line:  databaseRestore.PerformPostRestoreFixup();

       “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider,

    I am using Windows Authentication, Win7, SQLExpress2008.   I can successfully connect to the SQL Server Instance and db within Visual Studio 2012, but still get the exception when running debug.  Any ideas what is going wrong?

Page 1 of 1 (11 items)