Lightweight Database (SQL or Nosql)

Lightweight database (SQL or NoSQL)

if you need the lightest-weight database i would say sqlite 3. it's purpose designed for this task, is small and fast, and in my experience is reliable and easy to use.

i don't use php myself, but there appears to be support here.

sqlite supports pretty much "standard" sql, except that it doesn't enforce types - you can define a column to be text, but store and retrieve an integer value, if you feel like it. in practice, it's not a big deal and as long as you don't use this "feature" you can switch to a larger database in the future with little trouble.

but, in practice, i would start with mysql since it is likely already installed and available. if it gives you issues with memory use, switch to sqlite. but for a simple, no frills database, you might as well start with mysql.

Lightweight SQL database which doesn't require installation

Depends on what you mean by lightweight. Easy on Ram? Or lighter db file? Or lighter connector to connect to db? Or fewer files over all? I'll give a comparison of what I know:

                    no of files    cumulative size of files    db size

Firebird 2.5             5                6.82 MB               250 KB

SqlServerCe 4            7                2.08 MB               64 KB

Sqlite 3.7.11.0          1                0.83 MB               15 KB

VistaDb 4.3.3.34         1                1.04 MB               48 KB

no of files - includes the .net connector and excludes the db file

The dbs are of 1 table with 2 columns and 2 rows. Take the db size with a pinch of salt as dbs could grow differently with further use. For instance SqlServerCe though initially was at 64 KB, it didn't grow at all after adding a few hundred records, while VistaDb grew easily from 48 to 72 to 140 KB. SQLite was the best in that regard which started from the lowest and grew linearly.

Few anecdotes: I had better performance using SqlServerCe with the factory settings which means its the easiest to get kick started without any configuration, while I found Firebird little bit harder to get it started due to lack of online materials. Firebird as I could read had widest standard sql compliance. While VistaDb is written in fully managed C# which means it can be merged with your application's assembly to have one single file, it seemed slowest to me. Of all, considering performance, ease and size I chose SQLite. SqlServerCe would be my second choice.

In short each has its pluses and minuses. Again, take my rant with a pinch of salt, its just my personal experience.

Which NoSQL database should I use for logging?

I've decided to revise this accepted answer as the state of the art has moved significantly in the last 18 months, and much better alternatives exist.

New Answer

MongoDB is a sub-par choice for a scalable logging solution. There are the usual reasons for this (write performance under load for example). I'd like to put forward one more, which is that it only solves a single use case in a logging solution.

A strong logging solution needs to cover at least the following stages:

Collection
Transport
Processing
Storage
Search
Visualisation

MongoDB as a choice only solves the Storage use case (albeit somewhat poorly). Once the complete chain is analysed, there are more appropriate solutions.

@KazukiOhta mentions a few options. My preferred end to end solution these days involves:

Logstash-Forwarder for Collection & Transport
Logstash & Riemann for Processing
ElasticSearch for Storage & Queries
Kibana3 for Visualisation

The underlying use of ElasticSearch for log data storage uses the current best of breed NoSQL solution for the logging and searching use case. The fact that Logstash-Forwarder / Logstash / ElasticSearch / Kibana3 are under the umbrella of ElasticSearch makes for an even more compelling argument.

Since Logstash can also act as a Graphite proxy, a very similar chain can be built for the associated problem of collecting and analysing metrics (not just logs).

Old Answer

MongoDB Capped Collections are extremely popular and suitable for logging, with the added bonus of being 'schema less', which is usually a semantic fit for logging. Often we only know what we want to log well into a project, or after certain issues have been found in production. Relational databases or strict schemas tend to be difficult to change in these cases, and attempts to make them 'flexible' tends just to make them 'slow' and difficult to use or understand.

But if you'd like to manage your logs in the dark and have lasers going and make it look like you're from space there's always Graylog2 which uses MongoDB as part of its overall infrastructure but provides a whole lot more on top such as a common, extensible format, a dedicated log collection server, distributed architecture and a funky UI.

What is a good choice of database for a small .NET application?

14/06/2016 Yep... still getting upvotes :-/

17/03/2014 I'm still receiving upvotes for this, be mindful of the date this was originally answered. Though the main three items listed are still entirely viable, the list will tend towards becoming stale. There are further database technologies available that are not listed.

You have a couple of immediately recognisable and free options:

SQL Server Express LocalDB
SQL Server Compact Edition
SQLite

The SQL Server Compact download comes with the ADO.NET provider that you will need to reference in code. The SQLite download might not have it so here is a link:

http://sqlite.phxsoftware.com/

All three use SQL, though likely with a few limitations / quirks. Management Studio works with Compact and LocalDB, whereas with SQLite you will need another UI tool such as SQLite Administrator:

http://sqliteadmin.orbmu2k.de/

There are NoSQL alternatives, such as:

Sterling
RavenDb

Personally I would avoid using MS Access in the face of other free options. You cannot go wrong with LocalDB, Compact, or SQLite. They are all lovely small databases that run relatively quickly in little RAM - personal preference as to the religious aspects about liking a Microsoft product I suppose :-)

I use Sterling for Windows Phone programming as it is built to use Isolated Storage. I have only seen articles on RavenDb, but I can tell you that it is a JSON based document storage framework.

Not to confuse the situation (go with SQLite, SQL Server Express LocalDB, or SQL Server Compact Edition), but there are other embedded / local databases out there, some are relational others are object-oriented:

Embedded Firebird
db4o
VistaDb
SharpHSQL
Berkeley DB
Eloquera
SiaqoDb

Not all of these are free. SQL / LINQ / in-proc support differs across them all. This list is just for curiosity.

There is now also Karvonite, however the code gallery link is broken. When it's live again I'll be looking into this one for WP7 development.

Lightweight Database (SQL or Nosql)