7

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.

Improve this question
4
  • Please read How to Ask and take the tour. Also see Off topic from the help center Commented Feb 11, 2018 at 21:41
  • 2
    Have a look here: stackoverflow.com/questions/1888242/… Commented Feb 11, 2018 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. Commented Feb 11, 2018 at 21:44
  • 4
    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. Commented Feb 12, 2018 at 2:05

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.