4

I have a vb.net project that uses a SQLite database. I do this by using dataset/table adapters. The client is happy and all works well. However I have just heard that they plan on providing this product to another customer that wishes to use their SQL Server database. So I am writing this post so I can mentally prepare for this before I begin. I am not a database pro and have really enjoyed the simplicity of setting up and managing an SQLite database.

So any ideas on the easiest way to support SQL Server as well? I am happy to run them parallel to each other. Can I just make a separate service / middleware that syncs the SQLite database to the SQL Server on a timer and does not care about what the main app is up to?

Any pointers are appreciated.

Improve this question

1 Answer 1

Reset to default
1

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

A timer-based approach doesn't sound efficient: in most cases, the timer doesn't have anything to do; and after some data change, there is some amount time where the databases are not synchronized.

Can't you just replace SQLite with MS SQL Server? I.e., have some configuration settings that determines whether your program's data lies in SQLite or on a server?

Assuming that an SQL Server database with the required structure already exist, this would, in theory, need nothing more than a changed connection string, and supplying some user name/password (if the server isn't configured to automatically use Windows logins). There shouldn't be any big differences in the SQL dialects used. You have, of course, to test all your queries.

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

1 Comment

Thanks for your response! Sounds like just switching the app to MSSQL when we install it is the way. But is it really as easy as switching connection strings such as the current one below. <add name="SQLiteDB" connectionString="Data Source=c:\sqlite\NetcardTimesheets.db3;Version=3;Journal Mode=Memory" providerName="System.Data.SQLite" /> Or is it more complex than that. Any links or help on the matter would be great! thanks again.

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.