SQL Azure Data Sync – Moving Schema from Compact Database to SQL Azure

Update 12/11: Fixing a typo in the code as pointed by the commenter.

With yesterdays announcement of the Microsoft Sync Framework Power Pack for SQL Azure, we have had questions on how to move sync schema from SQL Compact database up to SQL Azure. The power pack only comes with tools to automate the sync schema setup from SQL Server to SQL Azure (via the SQL Azure Data Sync Tool) and taking SQL Azure data offline to a new compact database (via the VS Add New Item template). There is no UI tool to go from Compact to SQL Azure or even SQL Azure to SQL Server.

Just wanted to point out that its quite simple to achieve the above two non UI supported scenarios via code. Attached is the simple code that shows how to move sync schema from Compact to SQL Azure.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServerCe;
using Microsoft.Synchronization.Data.SqlAzure;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlCeConnection conn = new SqlCeConnection("c:\temp\abc.sdf");
            using (conn)
            {
                conn.Open();
                DbSyncScopeDescription desc = GetDbSyncDescription(conn);

                //Check and sync enable the Compact database
                SqlCeSyncScopeProvisioning ceScope = 
                    new SqlCeSyncScopeProvisioning(desc);
                if (!ceScope.ScopeExists(scopeName, conn))
                {
                    ceScope.Apply(conn);
                }

                SqlConnection azConn = 
                    new SqlConnection("YourSqlAzureConnectionString");
                using (azConn)
                {
                    azConn.Open();
                    //Check and sync enable the SQL Azure database
                    SqlAzureSyncScopeProvisioning azScope = 
                        new SqlAzureSyncScopeProvisioning(desc);
                    if (!azScope.ScopeExists(scopeName, azConn))
                    {
                        azScope.Apply(azConn);
                    }
                }
            }
        }

        static string[] tableNames = new string[] { "Foo", "Bar" };
        static string scopeName = "FooBarScope";
        private static DbSyncScopeDescription GetDbSyncDescription
            (SqlCeConnection conn)
        {
            DbSyncScopeDescription desc = new DbSyncScopeDescription();

            /***
             * Option 1 for generating schema - Iterate through the list 
             * of tables you need and get description for them
             */ 
            foreach (string table in tableNames)
            {

                 desc.Tables.Add(
                    SqlCeSyncDescriptionBuilder.GetDescriptionForTable
                    (table, conn));

             }

            /***
             * Option 2 for generating schema - If you already have a 
             * Compact database provisioned for sync you
             * could just read the whole scope description from it.
             **/
            // desc = SqlCeSyncDescriptionBuilder.GetDescriptionForScope(
            // scopeName, conn);

            return desc;
        }
    }
}

To move from SQL Azure to SQL Server you just need to replace SqlCeSyncScopeDescription type with SqlSyncScopeDescription. To access the new types under Microsoft.Synchronization.Data.SqlAzure just add a reference to the dll of the same name found under %programfiles%\Microsoft Sync Framework\Power pack For Sql azure November CTP\

Maheshwar Jayaraman