Lightweight databases (SQL Server alternatives)

Portions of this post will read like some Bukowski stream of consciousness thing. Apologies.

As part of my new project (more to come on that later) I'm interviewing lightweight databases. What I'm really looking for is:

• will work in a shared hosting environment with no additional software installs
  
• xcopy deployable
  
• has a decent IDE
• will work well with LINQ
  

The list so far (thanks to the people on StackOverflow for helping me here):

• SQL Server Compact Edition
• SQLite
• VistaDB
• Firebird
  

Next step, evaluation:

SQLite:
I already knew a little bit about SQLite because of my exposure to Android. It's the db that's used on Android and iPhone OS by default. So it's already in the lead because I'll need to know about it for those two platforms.

Installation: easy. Go to sqlite.org and hit the download page. There's 4 files for windows but it's pretty easy to see what's what. Download the dll, put it in a place that's easy to find. There's no install. Just a dll. Done, in 1 minute.

GUI: I read about SQLite administrator on mikeduncan.com so I tried that. Easy, quick, light. Some bugs, but no killers so far. All fine, but get this, it has AutoComplete! It was like a choir of angels singing when I saw that one. AutoComplete in a free SQL tool. Whoda thunk. I wanted to find a clip of a choir of angels singing and a light from heaven from a movie but couldn't find it or think of one. Damn.

Downsides:
No FKs. Damn, I really hate this one. FKs seem mandatory for a relational db for me. You can enforce them with triggers but that seems like a lot of work for FK enforcement.
Entire database is locked for a write. Versus row or table locking on more sophisticated dbs. So if your db is going to see a lot of writes then this will hurt your performance. Since my project is for a public-facing website the R/W ratio will be so high it won't matter. The SQLite FAQ says it's appropriate for sites that see up to 100k hits per day. Which is 90% of them?


Next up: SQL Server Compact

Disclaimer: I wrote about this in real time as I was installing, so I may look like I could've done this better.

Upon going to the download page, I'm hit with the dreaded Wall of Text. There's 3 versions on there, with 5 download links each. But if it's a simple database, it seems like it should be simple to install. SSC is failing there. "SQL Server Compact 3.5 Service Pack 1 for Windows Desktop (includes Synchronization Services for ADO.NET version 1.0 Service Pack 1)" seems like my best option. Then I go to the download page and it's the same thing, wall of text. 4/5th of the way down the page is "SSCERuntime-ENU-x86.msi" that's probably what I need. Then I hit install and I get the option of Repair or Remove. No install. OK great I downloaded the Service Pack version, I need to download the non-service pack version then install the service pack I guess? Ugh. I want a big button that says "Get the latest version!!" So I go back in the browser and hit the link for version 3.5 (without the Service Pack). It asks me to register, I say no. Then it sends me to the download page for 3.5 SP1. Arrrrgh. I'm ready to give up on Compact Edition right here. Done, over, finito. I've blown 10 mins on just the freakin installation. I wonder at this point if I already have Compact installed because I have SQL Server 2005 installed? I don't remember actually installing it, but the options in the installer are "Repair" or "Remove" so maybe... Is SQL Server (Full) installed? Don't see any management tools in my Programs list. Go to add/remove programs. Wait. Update blog post. Yep, sure enough, it's installed.

This is the point where I'm giving up on SQL Server Compact. I want a database solution that can exist totally independently of SQL Server and that's not clear from what I've done so far. I suspect that it can operate without SQL Server, but I don't want to uninstall SQL Server just so I can be sure. And I want management tools that are free and good (I already know this is possible with SQLite).

TLDR edition: Too much work to install, especially compared to SQLite.

Next up: VistaDB

Again, a recommendation from StackOverflow. VistaDB has an unfortunate naming collision with the most hated of all OS's, so I felt bad for em. They were probably there first.

The features look great. But then there's the license. No free license. There's a single dev distribution license, but it's pricey. I can get 3 years of SQL access on shared hosting for the price of that license. Gonna have to take a pass, the math just doesn't work. It's possible that I don't understand the licensing. But I already know SQLite is totally free and easy to use, so I'm not inclined to dig into the particulars of the licensing here.

Next up: Firebird

The specs look good. Requires an install, which is a con already. But maybe it'll be OK. I install, pause briefly when I see the option for installing as an Application or a Service. Really I don't want either, but I pick Application. Then there's "Start Firebird automatically every time you boot up." Rut-roh, does this mean that any host I put this my site's db on will have to have Firebird installed? Hmmm. I complete the install. CPU usage seems pretty extreme for an install. Then I hit "Finish" and get an exception: "Firebird server failure: Missing configuration file [Program Files]\Firebird\Firebird_2_1\firebird.conf". So it failed to bring in a file that it needed when it installed. Great. Now I have to track down whether that file exists and what it's supposed to do. I'm tempted to dump Firebird right here. Can't make an installer correctly? I don't have time to deal with your software.

Actually, I'm passing on Firebird. The installation makes me think that whatever host I pick would have to support it; my target host (discountasp.net) doesn't support it. I don't like restricting hosting options for my clients. Plus, it has a *nix smell about it; the first paragraph in the install guide says "Please study this chapter before attempting to install any servers." In other words, RTFM, which feels *nixy to my ears. I want a quickstart guide (which I did find, but after the install guide).

Wait, what's this, there's an embedded version. OK, that might work. I look around for an install link and find this in the Firebird documentation: "It's typically named Firebird-n.n.n.xxxx_embed_win32.zip, with n.n.n.xxxx the Firebird version and build number". At least give me a link to the page! Blerg. Here's the link for win32. Before I install Firebird Embedded I should uninstall Firebird (to make sure Embedded doesn't require the full version). Programs->Firebird->Uninstall. It fails, missing shortcut. Great. So I go through "Add / Remove Programs" and when I uninstall, I get an error; it tells me it "may already have been uninstalled." Man. Maybe I uninstalled and the uninstaller didn't clean up the Programs menu? Or the Add/Remove Programs list? Again, *nixy. I'm gonna give it 10 more mins at this point. Got to budget time.

The big question about Firebird Embedded is: can I use a GUI with it? I'm of the opinion that command lines should be easy to get, but rarely needed, it's the Roe v. Wade opinion of GUIs. I found a blog post, but his suggestion involves renaming a dll and providing a nonexistent password so FlameRobin can connect to the embedded server. I think if the GUI wasn't intended to do something you're asking for trouble when you trick it into doing that thing. We'll see. Here's the blog post.

I'm using Firebird 2.0 instead of 1.5 so that's a red flag already. I try what he suggests and get: "A fatal error has occurred. If you know how to reproduce..." Well that's the end of that. Firebird might be fine, but it's best GUI tool won't work with the embedded version.

And The Winner: SQLite. I can live without foreign keys. I'll be doing the whole data access layer in LINQ, so no sprocs will be fine. I need to know it for Android programming. The whole-db-is-locked-for-a-write issue won't affect this project very much. It's incredibly easy to get going. The GUI tool that I'm using has autocomplete. Auto. Freakin. Complete.

On a related note, I found the dbml project which is aimed at providing LINQ access to a variety of databases. Looks great, they've got a bazillion unit tests in every project. Not all of them are passing though; it's still Alpha.