4

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?

  • 1
    One thing to look into is SQL server replication. It's made for what you describe. But....we use it and to be honest it gives more headaches then it's supposed to solve. – Pieter B Jan 27 '17 at 12:15
  • Like you say, SQL Server replication is quite the headache (particularly because I prefer to code in C# but also because our licence structure would result in a very hefty cost). – Smithy Jan 27 '17 at 12:22
  • Also it will be going over GSM network that can only use port 80 and 443 – Smithy Jan 27 '17 at 12:45
2

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.

  • Thats a nice way of looking at it but does that mean solution 1 or 2 is inappropriate for a particular reason? – Smithy Jan 27 '17 at 19:05
  • 1
    The big problem is race conditions. There will always be a delay on syncing so you can't guarantee the integrity of your data. If server A updates a record and server B also updates the record before the sync happens you have just sold the same product to two different customers. If you choose option 2, (where I assume you mean 1 physical database that propagates the changes to the locations) then you come pretty close to the master slave relationship. But if you're going that way you might as well build an api on the central database. The data is going over the line anyhow. – Pieter B Jan 27 '17 at 22:33
  • I think I'm understanding you e.g. i have a central database, and via api access the clients can communicate changes made against their 'local data', the server then reaffirms the data the clients should have. So from the client -> Audits go up, current state comes down? – Smithy Jan 27 '17 at 22:46
  • Also, to clarify; the clients have their own data which does not impact another clients data, you can envision the server as a place to group all of the clients data for reporting, reconciliation etc, after the initial setup which comes from the server as well – Smithy Jan 27 '17 at 23:21
  • 1
    The thing is just, syncing isn't easy but you can solve it, Having two captains on one ship makes it a lot more complex. That's why I mentioned the api. With the api instead of having two captains change the same data you have one captain asking the other. – Pieter B Jan 28 '17 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.