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

        }

    }

}

 

Comments (25)

  1. ppez says:

    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,

  2. Govind says:

    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.

  3. Liam Cavanagh says:

    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

  4. james says:

    Hi Liam,

    can we sync only the specific columns of a table  

  5. Vincent says:

    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

                   };

  6. Thomas says:

    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

  7. Problems says:

    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.

    ?

  8. Tuvian says:

    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

  9. riya says:

    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

  10. JuneT says:

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

  11. Ansu says:

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

  12. 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?

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

  14. JuneT says:

    @Sea Dog Mariner,

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

  15. Still a work in progress.  I had used  PerformPostRestoreFixup in some previous trials when working on Liam’s blog “How to Synchronize Multiple Geographically Distributed SQL Server Databases using SQL Azure Data Sync”, but since removed it from my code.  So I assume that this must be accomplished immediately after db file is attached at RemoteOffice and before it is synced to Azure?

  16. JuneT:

    Do you have a sample of PerformPostRestoreFixup in vb code?  The WF for the attach/detach action is in vb, while the WF for the provision/sync action is in C#.

  17. JuneT:

    I guess I'd be looking for the vb equivilant of Liam's C# code below:

               SqlConnection serverConn = new SqlConnection();

               serverConn.ConnectionString = "Data Source=localhost;Trusted_Connection=True;Database=MYDATABASE";

               SqlConnection.ClearPool(serverConn);

               SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(serverConn);

               databaseRestore.PerformPostRestoreFixup();

    Since you mention a chance of sync ID issues without this procedure, I want to make sure we get it correct.

  18. JuneT says:

    yes, you have to do the PerformPostRestoreFixup before the synching the restored database for the first time.  and yes, that's the code for doing it.

  19. Khemarin says:

    Dear liamca,

    What is happen if someone create new tables? Will it auto sync the new tables?

    BR,

    Khemarin

  20. khemarin Set says:

    Dear All,

    I have one SQL Server 2008 R2 standard in Premises and SQL Server 2008 Express R2. I'm trying follow the your instruction guide. I'm getting error below:

    System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'sync_row_is_tombstone'.

    Invalid column name 'local_update_peer_key'.

    Invalid column name 'restore_timestamp'.

    Invalid column name 'update_scope_local_id'.

    Invalid column name 'last_change_datetime'.

      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

      at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,Boolean async)

      at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTriggerHelper.CreateInsertTrigger(SqlTransaction trans, DbSyncCreationOption option)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyScope(SqlConnection connection)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyInternal(SqlConnection connection)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply()

      at SQLAzureDataSync.Program.Setup() in c:UsersAdministratorDocumentsVisual Studio 2012ProjectsSyncToolProgram.cs:line 187

    Noted: Both servers is installed Microsoft Sync Framework 2.1.

    Any commend?

    BR,

    Khemarin

  21. Rose K says:

    Awesome information, this beginner's guide to SQL Azure could also be useful sqlturbo.com/beginners-guide-to-microsoft-azure-sql-database

  22. mdw says:

    @Problems

    If you encountered

    > ScopeName=ALLTABLESYNCGROUP

    > Starting Sync 10.03.2015 12:15:46

    > Microsoft.Synchronization.SyncException: Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). —> System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

    then the Visual Studio project had been configured for the wrong platform; eg. MS Sync x64 requires a Visual Studio project with Build/Platform target set to x64.

  23. Rahul says:

    Hi All

    I am trying to sync sql azure and sqlserver 2012 database, but while provisioning I am getting following errors:

    System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation.

      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

      at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

      at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

      at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTrackingTableHelper.PopulateFromBaseTable(SqlTransaction trans)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyScope(SqlConnection connection)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.ApplyInternal(SqlConnection connection)

      at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply()

      at Radix_s_Sync.Program.Setup() in c:usersmiitdocumentsvisual studio 2010ProjectsRadix's SyncRadix's SyncProgram.cs:line 90

    ClientConnectionId:07634923-37fc-415a-9158-5993d620e165

    Error Number:468,State:9,Class:16

  24. SteveSir1912 says:

    This is great, but when I try this, I notice the tables are not being created in Azure and when I make table changes, nothing is happening.  Is there any thing I can do here?

Skip to main content