Tuesday, March 29, 2011

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.


9 comments:

Sumon Das said...

is this applicable on windows mobile 8?

ErikEJ said...

Sumon: No, it is not

I'd Puck That said...

We have been able to get the Sync Framework 2.1 working with a SQL Compact 4.0 database. We are doing a one way sync from multiple clients to another temporary SQL Compact 4.0 database (i.e. on a USB key in case the source computer does not have an Internet connection), and then up to a SQL Server 2008 box. There are some interesting issues that have appeared, but it is possible. -Jon

Rick Cloud said...

I'd Puck That...

I'm working on the same type of Sync. I have a CE 4.0 and SQL Server 2012 database. I can provision the server, but could not do the CE 4.0
Can you let me know how you did it?
Here is my LinkedIn page:
http://www.linkedin.com/pub/rick-cloud/a/2b4/6b0/

ErikEJ said...

Rick: You may find this useful: http://jtabadero.wordpress.com/2012/04/20/sync-framework-and-sql-compact-4-yes-you-can/

Rick Cloud said...

Erik, I've looked at the site you suggested... I get an 'invalidcast' exception on KnowledgeSyncProvider...

Also, in looking again at the code you have here, I don't have http://localhost/ssce35/sqlcesa35.dll at all. I've totally uninstalled 4.0 & 3.5(sp2)... Then reinstalled 3.5(x86 & x64)... then 4.0... still get the same error as above...

ErikEJ said...

Rick: I am confused - do you want to use Sync Framework or RDA? Suggest you ask in the MSDN forum and provide more detail on what you want

Rick Cloud said...

Erik, Sorry for the confusing post. Using what you suggested before (jtabadero's link), I have been able to run sync using sqlce-4.0. However, I'm now thinking I really need to use RDA, as I believe I need to merely replace an entire table of data, instead of doing updates/inserts. Since the sync'ing would be up to my users, and since the updates/inserts are not sync'd in the order that they were applied to the original SQL database..., they could get mismatches between the updates and the inserts, depending upon how long the time is between their individual syncs.

My question is this, I don't have a folder 'sync' under my sql-ce folder. Nor is there a file called 'ConnWiz.exe' anywhere. Also, I don't have http://localhost/ssce35/sqlcesa35.dll
I have 3.5 sp2 & 4.0 installed, as well as sync 2.1

Is there something else I should have installed?

ErikEJ said...

Rick: Yes, you need the Server Tools http://www.microsoft.com/en-ie/download/details.aspx?id=9290 - and suggest you buy Rob Tiffanys book and read it.