How to Perform 2-Way Sync from Multiple Clients to Central Server

csqlsynchronization

I have many client applications that sync up to a central server.
I am using C#, MS SQL server, Win Forms, ASP.NET MVC + API.

I need to learn/figure out how to perform a 2 way sync for data changes across my server database and local databases.

My server and local databases are almost (but not quite) an exact match.

There are tables for Employees, Items, Products, Locations, etc and these can be changed on the server or on a local client -> I need to merge this data.

Example 1:

I have products in a location and I store the qty that is there.
The Qty in location can be changed via the website or the client (at the same time before a sync).

Potential Solution 1:

For every database table I have; I make an audit table for changes against them and sync them e.g.

ProductInLocation

  • Product
  • Location
  • Qty

ProductInLocationAudit

  • ProductBefore
  • ProductAfter
  • LocationBefore
  • LocationAfter
  • QtyBefore
  • QtyAfter

Then on the server I can calculate the differences per table and sync accordingly (i.e. ServerQty = LocalQtyAfter – LocalQtyBefore)

Potential Solution 2:

I store all edits to all tables in a single database:

AuditTable

  • Model
  • Property
  • BeforeValue
  • AfterValue
  • DataType

This way I can use Reflection?? to grab the correct models, calculate the difference between integers and simply overwrite strings to sync the data.

Potential Solution 3:

Microsoft Sync Framework

Seems like it would do the job, but I believe it's being/about to be sunsetted?

Potential Solution 4:

Track Data changes using SQL Server directly, this however would be difficult to put into source control and would need advanced code to manipulate the data before saving (i.e. to work out integer differences).

Which solution should I take (if any)? Which has the least pitfalls / is most likely to work successfully?

Is there a standard/correct way I should be doing this type of thing?

Best Answer

Replication is hard. Incredibly hard especially because you want it two way with multiple databases involved. There are many many pitfalls which you need to solve. Also relational database are designed as a single location central database server to insure integrity. We've had more success with a central server and designing an api for the remote locations.

For your situation I would do research into developing a master-slave relationship where you design it in a way to not be bi-directional anymore.

I mean, a warehouse location could be the master over their own stock, you can then synchronize them 1 way to the central server. The warehouse becomes the master of their own inventory and others are able to see it but not mutate it directly. They could like send a (api)message to "reserve" an item if you need something transferred. That way you can still "reach" the items of the warehouse, but the warehouse would be responsible for managing them.

So my answer in short: try to make your problem a 1-way sync problem instead of a 2-way sync.

Related Topic