5

My current development plan is to write remote apps that each of them will use it's own SQLite database locally. There will be also one central database which should be synchronized from each remote app db. My questions here are:

  1. Is there any library which could make synchronization towards a central database (SQL Server) from remotes (SQLite) databases?

  2. Let's assume for a moment that I decide to insert data to central database manually from all remote databases. Good information is each remote databases are using different data, however once the remote database creates their unique ids in tables which are (records) in relationships with each other by those ids then the problem could be such ids values could be already in use in central database. Therefore it's a problem.

How to overcome this? Hope you get my point.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arie
  • 109
  • 1
  • 7
  • Please read [ask] and take the [tour]. Also see [Off topic](https://stackoverflow.com/help/on-topic) from the [help] – Ňɏssa Pøngjǣrdenlarp Feb 11 '18 at 21:41
  • 1
    Have a look here: https://stackoverflow.com/questions/1888242/does-sqlite-support-replication#1888295 – orhtej2 Feb 11 '18 at 21:41
  • The only way of synchronizing database is to use a time stamp on each row of data in the database indicating last changed date. So also want to keep a time stamp of the last time the local database was connected to main database. Then only rows that have changed since last connection needs to be verified. – jdweng Feb 11 '18 at 21:44
  • 2
    If IDs are being generated in multiple locations then you might consider using GUIDs. They are slower to search but basically guarantee that you won't get duplicates. – jmcilhinney Feb 12 '18 at 02:05

1 Answers1

5

This is a really complicated design solution - if you can possibly avoid it, you should.

MSDN has a good overview of your architectural choices. It describes two approaches, the "data centric" and "service oriented" routes.

Data centric may seem the easiest - but as SQLite doesn't have a nice, built-in replication solution, you may end up writing a lot of plumbing code. You will need to solve the primary key question (GUIDs are probably easiest), conflict resolution (what happens if two clients create a similar record at the same time?), error handling etc.

Service oriented is a bit old fashioned, but the idea of using a messaging protocol to interact with the data store is gaining popularity (especially in micro service architectures) - CQRS is a useful place to start. In simple terms, in this model your application doesn't directly issue CRUD statements against the database, instead is sends messages to interact with the data store (e.g. "create new record for this customer"). You can send those messages to multiple data stores, asynchronously, so your central server can handle them when the client connects.

Both architectures introduce a lot of extra work.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52