VB.NET: SQLite for SQL Server

Question

I have a vb.net project that uses a SQLite database. I do this using dataset / table adapters. The customer is happy and everything works well. However, I just heard that they plan to provide this product to another client who wants to use their SQL Server database. Therefore, I am writing this post to mentally prepare for this before I begin. I am not a database professional and really enjoyed the ease of creating and managing a SQLite database.

So, any ideas on an easy way to support SQL Server? I am glad that you are conducting them parallel to each other. Can I just create a separate service / middleware that synchronizes the SQLite database with SQL Server on a timer and does not care about what the main application supports?

Any pointers are appreciated.

Answer 1

Synchronizing two databases is possible if it is rather complicated. You need some kind of mechanism to find out which records have been changed, and if it is possible to have new changes in both databases, you should also resolve conflicts.

The timer approach is not effective: in most cases, the timer has nothing to do; and after some data change there is some time when the databases are not synchronized.

Can't you just replace SQLite with MS SQL Server? Ie have some configuration options that determine if your program data is in SQLite or on the server?

Assuming that a SQL Server database with the required structure already exists, theoretically this may be required as a modified connection string and provision of some username / password (if the server is not configured to automatically use Windows logins). There should be no big differences in the dialects of SQL used. Of course, you should check all your queries.