SQL Server to SQL Azure Synchronization using Sync Framework 2.1

SQL Server to SQL Azure Synchronization using Sync Framework 2.1

  • Comments 21

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

        }

    }

}

 

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • 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,

  • 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.

  • 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

  • Hi Liam,

    can we sync only the specific columns of a table  

  • 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

                   };

  • I get Exception:

    "The default schema does not exist"

    At this line:

    sqlAzureProv.Apply();

    I try to sync only one simple table in my db and it has no relations to other tables.

    I tried both MyTableName and dbo.MyTableName in: (but same error)

    SqlSyncDescriptionBuilder.GetDescriptionForTable("dbo.Errors", sqlAzureConn);

    I sync from Azure down to Local, (is that a problem?)

    /Thanks

  • 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.

    ?

  • 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

  • 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

  • james - the Sync Fx API allows you to specify which columns you want to sync.

  • 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

  • tuvian.wordpress.com/.../how-to-sync-schema-changed-database-using-sync-framework

  • 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?

  • 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.

  • @Sea Dog Mariner,

    are you running PerformPostRestoreFixup on for every copy of the original database that you are re-attaching?

Page 1 of 2 (21 items) 12