none
Sync Remote SQL 2008 DB to SQL Azure with code (C#?) RRS feed

  • Question

  • Hi all,

    I have been looking through the Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit) to see what the possibilities were to sync up our existing SQl 2008 databases to sql azure. From what I have been able to figure out so far the walk-through provided in this package is only for local databases and it ends up generating a sql job that could used to complete the sync operation. 

    Are there any samples or tutorials out there that outline how to synchronize between a remote SQL 2008 database (change tracking enabled) and sql azure ? I am hoping for some code (C#) samples since i just finished getting the sync sorted out between two SQL 2008 DBs (which i dunno why but seemed like it was harder than it was suppose to be).

    So overall looking for a sample client SqlAzureSyncProvider implementation that could help me get moving the right direction. Thank you for any suggestions in advance.

    Thanks again

    Monday, June 28, 2010 1:34 AM

Answers

  • JuneT, thank you for all your suggestions and help they did point me in the right direction to get this all working. I did have to add a new build type to get the code to compile successfully from the Microsoft sample.

    Note 1: this URL was a good resource: http://msdn.microsoft.com/en-us/library/dd918848.aspx

    Note 2: Don't forget to download http://www.microsoft.com/downloads/details.aspx?FamilyID=BCE4AD61-5B76-4101-8311-E928E7250B9A&displaylang=en to get the SqlAzureSyncProvider

    for anyone else wanting to do a remote sql server sync to sql azure, here is the code i ended up using:

     

    static void Main(string[] args)
        {
    
          SqlConnection serverConn = new SqlConnection(masterDB);
          SqlConnection clientSqlConn = new SqlConnection(peerAzureDB);
    
           // This is the clean up code in case we want to redo the provisioning
    
          /*List<string> tablesToRemoveScope = new List<string>();
          tablesToRemoveScope.Add("AE_RemoteCommand");
          using (SqlConnection clientSqlConnForRemove = new SqlConnection(peerAzureDB))
          {
            clientSqlConnForRemove.Open();
            RemoveScope(clientSqlConnForRemove, false, tablesToRemoveScope, "all");
          }*/
    
          DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("all");
          DbSyncTableDescription Table1Desc =
            SqlSyncDescriptionBuilder.GetDescriptionForTable("Table1", serverConn);
    
         
          scopeDesc.Tables.Add(Table1Desc);
    
          string mode = "subsequent";
          if (!args.Contains("subsequent"))
          {
            mode = "initial";
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverConfig.Apply(serverConn);
    
            // Provision the azure client DB      
            Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncScopeProvisioning clientSqlAzureConfig = new SqlAzureSyncScopeProvisioning(scopeDesc);
            clientSqlAzureConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
            clientSqlAzureConfig.Apply(clientSqlConn);
          }
         
    
          MySyncOrchestrator syncOrchestrator;
          SyncOperationStatistics syncStats;
    
          SqlSyncProvider srcProvider = new SqlSyncProvider("all", serverConn, null, null);
          SqlAzureSyncProvider destinationProvider = new SqlAzureSyncProvider("all", peerAzureDB, null, null);
    
          // If we want batching can be enabled via: http://msdn.microsoft.com/en-us/library/dd918908.aspx
          // http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.relationalsyncprovider.memorydatacachesize.aspx
    
          //srcProvider.MemoryDataCacheSize =
    
          // Data is downloaded from the server to the SQL Server client.
          syncOrchestrator = new MySyncOrchestrator(
            destinationProvider,
            srcProvider
            );
          syncStats = syncOrchestrator.Synchronize();
          syncOrchestrator.DisplayStats(syncStats, mode);
        }
    
    }

     

    // This is the Sync Orchestrator class
    
      public class MySyncOrchestrator : SyncOrchestrator
      {
        public MySyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
        {
    
          this.LocalProvider = localProvider;
          this.RemoteProvider = remoteProvider;
          this.Direction = SyncDirectionOrder.Download;
        }
    
        public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
        {
          Console.WriteLine(String.Empty);
          if (syncType == "initial")
          {
            Console.WriteLine("****** Initial Synchronization ******");
          }
          else if (syncType == "subsequent")
          {
            Console.WriteLine("***** Subsequent Synchronization ****");
          }
    
          Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
          Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
          Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
          Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
          Console.WriteLine(String.Empty);
        }
    
      }

     

     

    • Edited by alsodev Thursday, July 8, 2010 2:39 AM changed to be a code field
    • Marked as answer by alsodev Thursday, July 8, 2010 2:39 AM
    • Marked as answer by alsodev Thursday, July 8, 2010 2:39 AM
    Thursday, July 8, 2010 2:32 AM

All replies

  • you're probably referring to the SQL Azure Data Sync Tool for SQL Server in the walktrough which i think is more geared to DBAs than for Devs or for point and click configurations.

    If you follow the SQL Azure Offline Plug-in for VS walktrough, you'll have some code to walk thru except that the local provider is pointed to SQL CE. you can use that as a starting point and replace all references to SQLCESyncProvider with SQLSyncProvider to connect to SQLServer instead.

    or you may grab the SQLServer to SQLExpress sample here https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3762 and replace one of the providers references to SQLSyncProvider  to use SQLAzureSyncProvider instead.

    Monday, June 28, 2010 1:24 PM
    Moderator
  • JuneT,

    Thank you for the response, however what I am seeking assistance is with the actual implementation of the SqlAzureSyncProvider as a class inherited from ClientSyncProvider. For starters in order to implement the ApplyChanges function I am assuming that one would call  the ProcessChangeBatch function in the SqlAzureSyncProvider however the function signatures don't quite line up

    in ClientSyncProvider you end up getting

    ApplyChanges(SyncGroupMetadata groupMetadata, System.Data.DataSet dataSet, SyncSession syncSession)

    which I think maps to

    ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics);

    in the SqlAzureSyncProvider

    So how does one go about creating a Microsoft.Synchronization.ChangeBatch from a System.Data.DataSet ?

    If anyone has a sample implementation of a ClientSyncProvider this would be a great help.

    thank you

     

    Wednesday, June 30, 2010 3:03 AM
  • are you trying create an offline provider that eventually makes calls to the SqlAzureSyncProvider which is a collaboration provider? or are you trying to make an offline provider to sync with SQLAzure?

    please note that the SqlAzureSync Provider is based off RelationalSyncProvider and not the ClientSyncProvider thus the differences.

    Not only that, the offline provider and the collaboration provider differs in the way they store and track change metadata. For example, the offline provider tracks the sent/received using so called "anchors" whereas the latter tracks it using so called "knowledge".

    Wednesday, June 30, 2010 9:38 AM
    Moderator
  • Alright I was a bit confused about this, so my end goal is to create peer-to-peer synchronization between a remote SQL 2008 Server and SQL Azure. I am trying to follow the example you provided http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3762, I am having some compile issues with this example (which i believe is due to me running on a 64 bit machine, some related info here: http://code.msdn.microsoft.com/sync/Thread/View.aspx?ThreadId=3037).

    Anyway if anyone does have a sample project which accomplishes a peer to peer sync using the collaboration providers for sql server and sql azure, I will be very happy to use as a reference.

    I am currently going down to the road of being able to come up with such a project my self.

    thank you

     

    Monday, July 5, 2010 11:41 PM
  • for your compile time issues, try changing the target platform from the project properties and see if it works.

    Tuesday, July 6, 2010 1:04 PM
    Moderator
  • JuneT, thank you for all your suggestions and help they did point me in the right direction to get this all working. I did have to add a new build type to get the code to compile successfully from the Microsoft sample.

    Note 1: this URL was a good resource: http://msdn.microsoft.com/en-us/library/dd918848.aspx

    Note 2: Don't forget to download http://www.microsoft.com/downloads/details.aspx?FamilyID=BCE4AD61-5B76-4101-8311-E928E7250B9A&displaylang=en to get the SqlAzureSyncProvider

    for anyone else wanting to do a remote sql server sync to sql azure, here is the code i ended up using:

     

    static void Main(string[] args)
        {
    
          SqlConnection serverConn = new SqlConnection(masterDB);
          SqlConnection clientSqlConn = new SqlConnection(peerAzureDB);
    
           // This is the clean up code in case we want to redo the provisioning
    
          /*List<string> tablesToRemoveScope = new List<string>();
          tablesToRemoveScope.Add("AE_RemoteCommand");
          using (SqlConnection clientSqlConnForRemove = new SqlConnection(peerAzureDB))
          {
            clientSqlConnForRemove.Open();
            RemoveScope(clientSqlConnForRemove, false, tablesToRemoveScope, "all");
          }*/
    
          DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("all");
          DbSyncTableDescription Table1Desc =
            SqlSyncDescriptionBuilder.GetDescriptionForTable("Table1", serverConn);
    
         
          scopeDesc.Tables.Add(Table1Desc);
    
          string mode = "subsequent";
          if (!args.Contains("subsequent"))
          {
            mode = "initial";
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
            serverConfig.Apply(serverConn);
    
            // Provision the azure client DB      
            Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncScopeProvisioning clientSqlAzureConfig = new SqlAzureSyncScopeProvisioning(scopeDesc);
            clientSqlAzureConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
            clientSqlAzureConfig.Apply(clientSqlConn);
          }
         
    
          MySyncOrchestrator syncOrchestrator;
          SyncOperationStatistics syncStats;
    
          SqlSyncProvider srcProvider = new SqlSyncProvider("all", serverConn, null, null);
          SqlAzureSyncProvider destinationProvider = new SqlAzureSyncProvider("all", peerAzureDB, null, null);
    
          // If we want batching can be enabled via: http://msdn.microsoft.com/en-us/library/dd918908.aspx
          // http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.relationalsyncprovider.memorydatacachesize.aspx
    
          //srcProvider.MemoryDataCacheSize =
    
          // Data is downloaded from the server to the SQL Server client.
          syncOrchestrator = new MySyncOrchestrator(
            destinationProvider,
            srcProvider
            );
          syncStats = syncOrchestrator.Synchronize();
          syncOrchestrator.DisplayStats(syncStats, mode);
        }
    
    }

     

    // This is the Sync Orchestrator class
    
      public class MySyncOrchestrator : SyncOrchestrator
      {
        public MySyncOrchestrator(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider)
        {
    
          this.LocalProvider = localProvider;
          this.RemoteProvider = remoteProvider;
          this.Direction = SyncDirectionOrder.Download;
        }
    
        public void DisplayStats(SyncOperationStatistics syncStatistics, string syncType)
        {
          Console.WriteLine(String.Empty);
          if (syncType == "initial")
          {
            Console.WriteLine("****** Initial Synchronization ******");
          }
          else if (syncType == "subsequent")
          {
            Console.WriteLine("***** Subsequent Synchronization ****");
          }
    
          Console.WriteLine("Start Time: " + syncStatistics.SyncStartTime);
          Console.WriteLine("Total Changes Uploaded: " + syncStatistics.UploadChangesTotal);
          Console.WriteLine("Total Changes Downloaded: " + syncStatistics.DownloadChangesTotal);
          Console.WriteLine("Complete Time: " + syncStatistics.SyncEndTime);
          Console.WriteLine(String.Empty);
        }
    
      }

     

     

    • Edited by alsodev Thursday, July 8, 2010 2:39 AM changed to be a code field
    • Marked as answer by alsodev Thursday, July 8, 2010 2:39 AM
    • Marked as answer by alsodev Thursday, July 8, 2010 2:39 AM
    Thursday, July 8, 2010 2:32 AM