SQL Server 2008: Share data between Compact 3.5 and Express

Question

Question: How can we connect a SQL Server CE DB to a SQL Server Express DB? We can't use Merge replication because you can only be a Subscriber in SQL Server Express, not a Publisher.

More background information:

We have an application structured as follows: 1) Our headquarters, running SQL Server 2008 2) Branches, running SQL Server 2008 Express 3) Machines, running SQL Server Compact

Each machine needs to connect to its branch to send data. Then the branch needs to connect to our headquarters to send the data. The synchronization of data between the machines and branches should be fairly often (ideally twice a minute), because branch operators may need to respond to emergencies. The synchronization between the branch and the headquarters can be less often (I'm thinking daily). However, we would need to support network failures.

We do not expect to have conflict resolution, so I would like to avoid having to use Microsoft Synchronization Services. Looking at this page SQL Server Express BOL, I see that Merge Replication can meet our needs, and I am planning on using it between our HQ and branches, but as I said above, we need a way to connect the compact DB to Express.

Answer 1

You can design an application to connect to your SQL Express instance and perform a manual synchronization - something like a home-grown replication. I would deploy the app on your SQL CE machines, and "pull" the information required to synch down from each machine.

Answer 2

Check out the Microsoft Sync Framework:

a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices with support for any data type, any data store, any transfer protocol, and network topology.