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.
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.
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:
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?