Database solution for client server application with encrypted database?

Question

Hello,

We have a Windows application on .net 2.0 that uses embedded encrypted database. The database consists of secret data - around 350mb, which is read-only and is updated with new one each 4 months.

Untill now we use SQLite file as a database and it worked good, but we need to move to client-server version since some customers have 20-40 clients and they dont want to license and update each client separatelly.

What database solution you can advise us for this:

• The database should stay encrypted so even the administrator cannot read the data.

• We should continue to exchange the database each 4 months.

• We plan to make the server application as Windows service so the service will read from the database and will send the data to the clients. But we can also use some free sql server if can provide acceptable encryption.

• It would be good to use the same database for our client-server and single-client version so no additional converts be needed on realease.

Since the database is readonly perhaps we will not have problem to use sqlite here again. Any ideas?

Answer 1

The obvious answer is to migrate to MySQL, however it is not so simple.

In particular you say your database is read-only. In that case SQLite will not need to use transactions or directly lock the database file at all.

That is a huge advantage because it is the physical locking of the database file that causes the biggest scalability problems for multi-user access to a SQLite database

(But even with a read/write database, the SQLite docs say it works fine for sites with 100,000 hits per day).

It also sounds like you intend to use a single centralized Windows Service to serve all clients. In that case all database access would be serialized by this service and so SQLite will never have two concurrent connections competing for access. In that case SQLite would not be a bottleneck, the service would.

If the service is multi-threaded of course, then yes, there may be concurrent access to SQLite, but once again read-only, so still not a actual problem..

Also, your requirements for encrypting the database and 4 monthly exchanges are much more simply met with SQLite than MySQL.

Have you read this earlier question on StackOverflow? Kyle Cronin gives an interesting commentary of his experiences over time and eventually says SQLite is a good option for lower volume multi-user web-sites.

Hope I've provided some food for thought.