How to choose light version of database system

Question

I am starting one POS (Point of sale) project. Targeting system is going to be written in C# .NET 2 WinForms and as main database server We are going to use MS-SQL Server. As we have a lot of POS devices in chain for one store I will love to have backend local data base system on each POS device.

Scenario are following: When main server goes down!! POS application should continue working "off-line" with local database, until connection to main server come up again.

Now I am in dilemma which local database is going to be most adoptable for me. Here is some notes for helping me point me in right direction:

1. To be Light "My POS devices art usually old and suffering with performances"
2. To be Free "I have a lot of devices and I do not wont additional cost beside main SQL serer"
3. One day Ill love to try all that port on Mono and Linux OS.

Here is what I've researched so far:

1. Simple XML "Light but I am afraid of performance, My main table of items is average of 10K records"
2. SQL-Express "I am afraid that my POS devices is poor with hardware for SQLExpress, and also hard to install on each device and configure"
3. Less known Advantage Database Server have free distribution of offline ADT system.
4. DBF with extended Library,"Respect for good old DBFs but that era is behind Me with clipper and DBFs"
5. MS Access
6. Sqlite "Mostly like for now, but I am afraid how it is going to pair with MS SQL do they have same Data types".

I know that in this SO is a lot of subjective data, but at least can someone recommended some others lite database system, or things that I shod most take attention before I choice database.

Answer 1

SQL Server Compact

It's designed for embedded devices (i.e. Windows Mobile), but can also run on PCs. It's 2MB, runs in-process, single database file, that can have whatever name you like.

Its meant as a local high-performance database. You can't connect to it remotely, and doesn't support stored procedures, or user-defined functions.

---

But to answer your actual question: how to choose?

Choose what have management tools, with an easy, compatible, upgrade path when you outgrow it.

Answer 2

I'm doing much the same thing: central server probably running MS SQL server and distributed systems though these are running Linux. We opted to do the data transfer in XML and use sqlite on the distributed systems.

It's early days but seems to be going well so far.

There are .net bindings for sqlite.

The reason we chose sqlite were:

1. because it doesn't need any database management, which would be tricky on the remote systems.
2. It seems very widely used: for example firefox uses sqlite for local storage.
3. We can use it on Windows and Linux.
4. It's supposed to be good at not losing data if there's an unexpected power outage.

Answer 3

I use System.Data.Sqlite, which is an open-source ADO.Net wrapper around Sqlite from http://sqlite.phxsoftware.com/. You can use it from within Visual Studio to build databases. It supports a subset of the field types of Sql Server, and writing an interface class between the two databases should be a snap. And you get the benefits of simple deployment by including a single DLL in your project and a single-file database. And it includes encryption, too.

Answer 4

I would suggest Sql Compact Edition as it's lightweight and free, so it solves two of your three problems. I have no idea if it works on Mono.....

I've used it in the past, and I was actually quite impressed with the performance. The one big drawback is the lack of stored procedures...