Sync nightmare - is it possible to use Merge Replication (or RDA) between 2 SQL CE instances without IIS?

Question

We are faced with the following problem that involves keeping the following in sync:

• 1 Centralised Server (IIS / MSSQL 2005)
• Many Desktop WPF clients distributed by ClickOnce
• Many Mobile clients - (Windows CE)

alt text http://img502.imageshack.us/img502/8246/deployment.png

With these thorny constraints:

• all sync relationships are bi-directional
• the desktop & mobile nodes require offline mode
• the mobile nodes cannot sync with the central server but are to sync with the desktop nodes over USB. The desktop nodes act as a server for the mobile clients, not the central server. Updates from the mobile nodes then need to propagate to central server by virtue of syncing with the desktop, and then syncing that desktop with the central server. phew.

Nasty.

To sync the desktop and server we know we can use Merge Replication - the server being a provider, the desktop as a subscriber, all good. BUT what about syncing the mobile and desktop nodes? Seemingly the Desktop cannot be a publisher without SQL server + ISS, right? We can only deploy MSSQL CE to the desktop.

Even if this was the case, could a node be both subscriber and publisher? I suspect that this arrangement isn't supported by the Merge Replication architecture - seemingly hub and spoke is the only supported model.

We are trying to determine if we can eliminate RDA or Merge Replication as a possible solution - if so we will probably pursue the MS Sync Framework.

Perhaps there are other technologies that we could consider?

Thanks all,

Ashley

UPDATE: we've just started having a play with MS Sync Framework and it rocks!

Stealthy

Just for clarification: you need the mobile client to be able to sync with the desktop while the desktop is offline? Because if the desktop is online when the mobile client syncs to it (using ActiveSync, I'm guessing?), then the mobile client would be able to "see" the server as well. – Gneiss

Yeah - the mobile needs to be able to sync with the desktop (field laptop) when it too is offline. I wish we could just stick to hub and spoke - would be so much simpler. – Stealthy

Opposed to the ol' hub and spokes which have hubs (with spokes!) – Stealthy

Answer 1

For reasons not directly related to your specific question, I think you can go ahead and eliminate RDA/Merge Replication as a possible solution. Microsoft does not advertise this (for obvious reasons), but merge replication is not 100% reliable under conditions where the network connection can be dropped in the middle of the replication process (which happens quite frequently with Windows Mobile clients).

The result of this problem is that changes made to client databases are sometimes not fully propagated to the master database, but you get no detectable indication that this has happened. As you can imagine, this is a bad thing, especially if you sold your client on this solution based on the assumption that RDA works as advertised. The only short-term solution we found to this problem was to implement a very clunky double redundancy process to confirm that each record added or modified on the client made it to the server without error. The long-term solution was to never again use RDA for anything.

This problem may have been fixed in recent versions of RDA (our problem occurred with the latest version 2 years ago), but I'll never know (once bitten twice shy).

Update: by the way, I've had many people I've spoken to about this (including Microsoft support techs) deny that this could possibly be true, but it's very easy to verify: add a bunch of data on the client, then start replication, then yank the USB cable out of the device (assuming your client is Windows Mobile connecting through ActiveSync) while in the middle of this.

If I seem slightly miffed, it's partially because this was what the MS tech was finally forced to say (actual quote): "well, just keep replicating - the data will merge eventually".

Gneiss

Answer 2

Check out the Collaboration Scenarios (Peer to Peer) in Microsoft Framework 2.0. The providers included there allow two SQL Server Compact databases to be synced directly. They are also able to sync directly with other providers.

Halothane