Large scale Merge Replication strategy - what can go wrong?

Question

Hi, I'm developing a piece of software that uses Merge Replication and SQL Compact on Windows Mobile 6. At the moment it is running on 5 devices reasonably well.

The issues I've come up against are as follows:

• The schema has had to change a lot, and will continue to have to change as the application evolves. There have been various errors replicating these schema changes down to the device, uploads failing due to schema inconsistencies.

• Subscriptions expiring (after 14 days) and unable to reinitialize with upload - AKA, potential data los of unsynced data up to that point.

Basically, the worst case scenario is data loss, and when merge replication fails, there seems to be no way back to get the data off. My method until now has been to drop and create the subscription on the device. I don't hear many people doing this, though it seems to solve everything.

The long term plan is to role this out to 500+ devices. Any advice on people who have undertaken similar projects, and how to minimise data loss and make it so that there's appropriate error handling code to recover from sync failures would be much appreciated.

James

Answer 1

Rebuilding replication from a script seems a fairly straightforward task until you start getting into data inconsistencies and then have to manually correct data before it can be synchronized.

I would suggest you look into Sync Services for ADO.Net rather than replication.

http://msdn.microsoft.com/en-us/sync/bb887608.aspx

Answer 2

James,

We hace been using an application called Utility Center for about 7 years that uses SQL server merge replication. Currently we have the application on 120 machines. We are constantly having issues with data not getting to the subscribers but it does not render the software unusable. Also the software is installed on laptops etc that people take home shutdown etc. In addition some folks sync wirelessly which has its own set of issues.

Let me know if you want any additional info about this.

Hamish