locked
SQL Azure Data Sync RRS feed

  • Question

  • It looks like Project Huron (in part) is finally here!  We have designed a windows forms app to read a local sql compact database file.  We want to be able to publish this to SQL Azure and then be able to sync.  I don't see that capability yet.  Am I mistaken?  Can we not publish the sql compact database file to SQL Azure? 

    Wednesday, November 18, 2009 6:58 PM

Answers

  • Hi Vincent,
    Yes this is very much supported in the power pack we released. We only shipped automated tools for the schema creation for the following two scenarios. Sql Server -> Sql Azure (Sql Azure Data Sync Tool) and SQL Azure -> Sql Compact database (VS template). You can easily move your schema from compact to Sql azure. Here is the sample code to do that.


    SqlCeConnection conn = [YourCeConnection];

    using(conn)
    {
          conn.Open();
          DbSyncScopeDescription desc = new DbSyncScopeDescription();
          foreach(string TableName from your_CE_Database_tables)
          {          
           desc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable("", conn));
           }

           SqlCeSyncScopeProvisioning ceProv = new SqlCeSyncScopeProvisioning(desc);
           string scope = "myScope";
           if(!ceProv.ScopeExists(scope, conn))
           {
                  ceProv.Apply(conn);
           }

           SqlConnection azConn = new SqlConnection("YourSqlAzureConnString");
           azConn.Open();
           SqlAzureSyncScopeProvisioning azProv = new SqlAzureSyncScopeProvisioning(desc);
           if(!azProv.ScopeExists(scope, azConn))
           {
                  azProv.Apply(azConn);
           }
    }

    Once you have them provisioned you can just create the providers by invoking SqlCeSyncProvider and SqlAzureSyncProvider with the scope name and start synchronizing between them.
    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    • Marked as answer by VincentCotton Thursday, November 19, 2009 1:26 AM
    Thursday, November 19, 2009 1:07 AM

All replies

  • Hi Vincent,
    Yes this is very much supported in the power pack we released. We only shipped automated tools for the schema creation for the following two scenarios. Sql Server -> Sql Azure (Sql Azure Data Sync Tool) and SQL Azure -> Sql Compact database (VS template). You can easily move your schema from compact to Sql azure. Here is the sample code to do that.


    SqlCeConnection conn = [YourCeConnection];

    using(conn)
    {
          conn.Open();
          DbSyncScopeDescription desc = new DbSyncScopeDescription();
          foreach(string TableName from your_CE_Database_tables)
          {          
           desc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable("", conn));
           }

           SqlCeSyncScopeProvisioning ceProv = new SqlCeSyncScopeProvisioning(desc);
           string scope = "myScope";
           if(!ceProv.ScopeExists(scope, conn))
           {
                  ceProv.Apply(conn);
           }

           SqlConnection azConn = new SqlConnection("YourSqlAzureConnString");
           azConn.Open();
           SqlAzureSyncScopeProvisioning azProv = new SqlAzureSyncScopeProvisioning(desc);
           if(!azProv.ScopeExists(scope, azConn))
           {
                  azProv.Apply(azConn);
           }
    }

    Once you have them provisioned you can just create the providers by invoking SqlCeSyncProvider and SqlAzureSyncProvider with the scope name and start synchronizing between them.
    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    • Marked as answer by VincentCotton Thursday, November 19, 2009 1:26 AM
    Thursday, November 19, 2009 1:07 AM
  • This is great news!

    Thanks,

    Vince
    Thursday, November 19, 2009 1:27 AM
  • One more question.  Do these tables need to be added to SQL Azure in a particular order or can I just loop through them and not worry about constraints?

    Thanks,

    Vince
    Thursday, November 19, 2009 2:26 AM
  • Yes ordering matters. The same rules apply to these tables as we have in the releases SQL providers. If you have two tables with PK-FK relationship then add the parent table first before adding the child table.

    Maheshwar Jayaraman - WCF -- http://blogs.msdn.com/dotnetremoting
    Thursday, November 19, 2009 5:34 AM
  • Hi Maheshwar,

    I've noticed the code from your answer doesn't seem to upload any code that already existed in the SqlCe database before it was provisioned to work with the SyncFx.

    I'm using a SqlServer 2008 Database instead, but that shouldn't make any difference. How can I force the SyncFx to upload any existing data in the local SqlCe database in the first sync?



    Thanks.


    // Connections
    SqlCeConnection clientSqlCeConn = new SqlCeConnection(@"Data Source=C:\Temp\sync1.sdf;Persist Security Info=false;");
    SqlConnection serverConn = new SqlConnection(@"Data Source=.\SQL2005;Initial Catalog=peer1;Integrated Security = true");
    
    DbSyncScopeDescription desc = new DbSyncScopeDescription("sales");
    desc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable("orders", clientSqlCeConn));
    desc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable("order_details", clientSqlCeConn));
    
    // Provisions the client
    SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(desc);
    if (!ceConfig.ScopeExists(scopename, clientSqlCeConn))
    {
        ceConfig.Apply(clientSqlCeConn);
    }
    
    // Provisions the server
    SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(desc);
    if (!serverConfig.ScopeExists(scopename, serverConn))
    {
        serverConfig.Apply(serverConn);
    }
    
    // Providers
    SqlCeSyncProvider localProvider = new SqlCeSyncProvider(scopename, clientSqlCeConn);
    SqlSyncProvider serverProvider = new SqlSyncProvider(scopename, serverConn);
    
    // Synchronize       
    SyncOrchestrator orchestrator = new SyncOrchestrator {
        LocalProvider = localProvider,
        RemoteProvider = serverProvider,
        Direction = SyncDirectionOrder.DownloadAndUpload
    };

    Friday, February 5, 2010 11:33 AM