Snapshot Synchronization with SQL Server Compact 4.0

It has been made clear by the SQL Server Compact team that version 4.0 does not support Sync technologies like Merge Replication and Sync Framework.

Imagine a scenario where you have a desktop or web app, where the data is mix of the user’s own operational data and lookup data from a central data source. How would you enable this using SQL Server Compact 4.0?

RDA (Remote Data Access) is a simple and proven technology, that allows you to “Pull” an entire table from a SQL Server over http (take a snapshot), without much coding effort. I decided to test if this technology would still work with 4.0, and lo an behold, it does. (Notice that RDA support will be removed from SQL Server Compact in a future release)

That means that you can pull down lookup data from a central server to your SQL Server Compact 4.0 database file as needed. I configured my SQL Server Compact ISAPI agent DLL according to my post here, and was able to pull a table to my version 4.0 database file. (Remember to DROP the local table before you Pull).

Here is the small amount of code required (Console application):

using System;
using System.Data.SqlServerCe;

namespace TestRDA40
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection String to the SQL Server
            //
            string rdaOleDbConnectString = "Data Source=(local);Initial Catalog=ABC; " +
                "User Id=xxx;Password=yyy";

            // Initialize RDA Object
            //
            SqlCeRemoteDataAccess rda = null;

            try
            {
                // Try the Pull Operation
                //
                rda = new SqlCeRemoteDataAccess(
                    "http://localhost/ssce35/sqlcesa35.dll",
                    @"Data Source=C:\data\sqlce\test\nw40.sdf");

                rda.Pull("ELMAH", "SELECT * FROM dbo.ELMAH_Error", rdaOleDbConnectString);
            }
            catch (SqlCeException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                rda.Dispose();
            }

        }
    }
}

Hope you find this useful.