I'm creating an application that needs to be able to support MS SQL Server, but I also want to create an "easy to deploy" version of the application that has as few dependencies as possible and which would simply work with a flat file database (no DB Server required).

Originally I'd planned on using MS Access for the easy to deploy version, but it seems nHibernate has some issues with MS Access (and MS Access has a few other limitations besides) and so I was wondering what database type(s) other folks were using in this kind of scenario, where the DB is either a simple flat file database or an embeded database. I know of plenty of DBs that provide the functionality I need - what I'm worried about is how reliable these will be when used in conjunction with nHibernat... So basically I'm looking for either a flat file or an embedded database which has been tried and tested with nHibernate.

Can anyone recommend such a database that they've used either in a production application or have extensively tested with nHibernate? One of the main things I want to avoid, since I'm supporting multiple databases, is having to manually maintain the schema for both/all the database types that I support, so it's important that the DB I choose be supported by the SchemaExport tool as well.

TIA.

Best Regards,

James Crosswell
Microforge.net LLC
http://www.microforge.net

What about using the SqlServer express edition?

Thanks, yeah I've used that in the past and it's a PITA. I started off using MSDE a few years ago, with an app that ran as an ISAPI, which meant it had to use SQL Server authentication. Getting MSDE to use this mode is not all that simple (there's a bug in the installer for MSDE 2000 that means even if you tell it to install with Mixed mode authentication enabled it doesn't). So had to fiddle around writing VB Scripts for the installer to modify registry settings directly. Then a few months later Microsoft releases a service pack which breaks various stuff and so on and so forth for ever basically. Building for SQL Server is like building a house on quicksand - it's an ever changing target and you're never sure when MS is going to bring out the next service pack to break everything.

Basically, even though they call it an "Express" edition, from experience, people often run into problems running this edition of the software. Even simple things like "What's the instance name of the DB" complicate things unecessarily.

The app I'm writing WILL support SQL Server (express edition included) but I want to support another databases which has zero deployment (basically a flat file, either like an MS Access mdb file or like the embedded version of Firebird which you can use simply by shipping a dll with your app - no separate install of a DB engine required and no complications like protocols, instance names, port numbers etc. to talk to the database - just the path to the databases - something that couldn't possible break.

In fact, firebird would be fine, providing this works well with nHibernate. What I'm looking for is someone who's used something like firebird in production or extensively in testing who can say "yep, that works great - SchemaExport works great and there's no hidden catches using xyz database type with nHibernate."

Best Regards,

James Crosswell
Microforge.net LLC
http://www.microforge.net

Understand.

I've used SQL Server 2005 for local development and deployed to Oracle and the transition between the two was a quick config change. It did affect DB design a bit (for instance, had problems when trying to use GUIDs as surrogate keys). As far as zero-deployment solutions, I've only had simple web apps wil express on the back end. I have used Access, but it's crap. I've also had friends use the CE version of SqlServer2005, but have never done it myself.

I've also heard of folks using Firebird, but am unsure in regard to your specific request. In any case, it seems to work fine with NHibernate.
Here's a thread on Ayende's site where people were trying to find in-memory DB solutions (primarily for testing), maybe it will be of some use:
http://www.ayende.com/Blog/archive/9041.aspx
It includes people using Firebird as an in-memory only solution.

I haven't messed with Bamboo much, but know that it's a .Net port of Prevayler. If it could be used as the database rather than just the second-level cache, your objects would "persist" in-memory only. Bamboo should persist snapshots of the current state in XML files. If you had a small database and could use it as the database with NHibernate it would rock, but I haven't tried to go there yet.

Thanks, it looks like SQLLite could do the job, as could both Firebird and MySQL (both of which have "embedded" versions available for them).

I guess all I'm after now is for someone who's actually used one of these databases to chip in and give one of them the thumbs up!

Best Regards,

James Crosswell
Microforge.net LLC
http://www.microforge.net

I've used MySql (not the embedded version though). I like it a bunch and the price is right. They done a lot over the last few years to improve it and are working aggresively on improvements. They are open source, but in a RedHat sort of way. Clients I've worked with are more keen on SqlServer and Oracle, but DoD has been using MySql increasingly.

I first started messing with MySql when adopting XPlanner about 5 or so years ago. More recently, I threw together an app that would print note cards using our project data in MySql (XPlanner DB). I used NHibernate for the work and it was pretty easy stuff.

As far as SqlLite, I have a friend that's used it for an embedded system. She seems well enough disposed toward it.

OK cool - I'll try one of those then. I guess I just got off on the wrong foot with MS Access :-(

LOL...Is there a right foot with Access?

OK, well after a bit of frilling around reading various web sites (Ayende Rahien's among them) I eventually came to the following conclusions:

- MS Access is probably not worth the trouble. It has very limited support for a whole bunch of join types and is basically broken... to be steered clear of even if the nHibernate project does fix the SchemaExport to work with this DB Type.

- SqlLite might work for some folks but has limited support for certain column types (Guids being one of them and being a type that I use quite extensively, so I passed on this)

- MySql has certain licensing issues and the API for this (which is what they suggest you use for embedded apps) is C++ with no ADO.NET or .NET wrapper that I could find. It could be that I didn't look hard enough and MySql embedded could be OK if you're cool with the way it's licensed, but I didn't bother looking into it because I've had some success with the following DB

- Firebird has an embedded mode which is extremely easy to use (comes with a nice fuzzy ADO.NET driver/assembly that you can link to) and deploy (copy a few dlls into your application directory and you're good to go). IMO Firebird is a more capable database than MySQL in any case and about the only catch I found here was that you have to use Firebird version 2.02 or later, since 2.01 has issues with Vista and Windows XP SP2 when running in embedded mode (long story there, but providing you use 2.01 or later you don't need to hear it).

So my executive summary would be :
- Firebird is an excellent choince for embedded databases
- MySql might be an option to... would need further looking into though.

Hope this helps someone else out there.

Best Regards,

James Crosswell
Microforge.net LLC
http://www.microforge.net

I've looked into the issue myself...

For what it's work, MySql does distribute a dotNet data adapter for dotNet development. I've used it bofore with NHibernate. That being said, you are right about the licensing and I couldn't quickly find a download for the embedded version without addressing the licensing issue.

Firebird also looks to me like the best bet and I'll start using it soon to support database testing.

Just my two cents. Even if SQLite only support strings under the hood (and just recently integers and reals), it's not that much of a problem in practice. Guids can be converted into strings, so you shouldn't disregard SQLite on these grounds.

http://www.sqlite.org/datatype3.html

However, the thing that finally made us switch from SQLite to Firebird was the fact that it only support one open transaction at a time. This makes it essentially useless for multithreaded/multiuser applications. SQLite is excellent for some tasks, but if you have more than one thread/process accessing the database at the same time you really should consider using another database.

http://www.sqlite.org/lang_transaction.html

I've used NHibernate with embedded Firebird on several small projects, and I never had any problems. The only (very minor) issue I experienced was when running integration tests from Testdriven.NET. Firebird locks the database to the TestDriven process, forcing you to kill the process manually after every test run.