Sharing data between remote locations

Question

I'm currently estimating how to best share data between offices at different geographical locations.
My current preference is for using SQL Server Merge Replication and have a main database and handful of subscribers.

The system will also need to allow a few work sites to work disconnected (no or little connectivity on construction sites).

The amount of data is not going to be large, we're talking about sharing data from a custom ERP system between a manufacturing plant, a handful of regional offices and work sites.

The Sync Framework also looks good and seems to have good support in SQL Server 2008.

1. What other proven system out there should I investigate that can answer these needs?
2. For those with experience on sharing data in a similar environment, do you have any particular recommendation and tips?
3. How difficult has it been for you to deal with data conflicts?

Answer 1

Definitely stick with SQL Server replication, then decide to go down the path of 'build your own replication framework.' I've seen some applications become horrible messes that way.

I've had environments that are setup for snapshot replication in a disconnected model, but the remote sites were read-only. They worked quite well with minimal issues.

I'd also be interested in hearing people's experiences with the sync framework.

You may want to look at what microsoft calls smart clients which is an architecture microsoft talk about for applications that may have temporary network connectivity.

Answer 2

Hello,

I have already discussed my own experience of SQLServer2005 with #cycnus. My answer is not a real one, just a few arguments to initiate a subject I am very interested in.

1. Our choice for 'not allways connected' sites is to implement web-based merge replication. Data exchanges happen to be even quicker than through VPNs (as we also have a combination of LAN merge replications). I will easily get a speed of 30 to 40 rows per second through web (512 Down/128 Up, shared) while I'll get a 5 rows per second through LAN (overseas, 256 Up/Down, dedicated). Don't ask me why ...
2. Tips are numerous: subscription should be of the client type (data circulating basically from the suscriber to the publisher before being distributed). Primary Keys should allways be GUID, for many reasons exposed here, but also for replication issues: we are then sure that any newly created record will be able to find its way up to the publisher, as its PK will be unique. Moreover, I recently had a non-convergence issue with one of my replications (bad experience, exposed here) , where I felt very happy not to use natural keys, as the problem occured on the potential "natural key" column.
3. Data conflicts should then be basically limited to work organisation problems, where (ususally for bad reasons) the same data is modified by different users in different places at the same time. With our "PK is GUID rule", we do not have conflicts out of these specific situations.
4. One should always have the possibility to modify its database structure, even if replications are running. It is possible to keep on adding fields, indexes, constraints while running merge replication processes. I also find a workaround for adding tables without reinitialising the replication process (exposed here, still did not understand why I was downvoted on this answer!)