What's the best zero (near zero?) administration relational database for a windows desktop app: Access, SQLite, SQL Server, other?

Question

We don't need multiuser. Just relational. Our app currently uses Access but Access isn't exactly zero-administration because it's prone to corruption.

Is SQLite rock, rock solid, pure zero-administration?

Or...is there a way to configure SQL Server as a desktop engine so that it is pure zero admin?

Or...is there some other relational choice?

We are using ADO as the layer to talk to Access, so switching to SQL Server wouldn't be hard, but my understanding is that I couldn't use ADO for SQLite, so switching to SQLite would require a lot of rewriting. Is that right?

Answer 1

Since you are looking at no need for multiuser, you might want to check out SQL Server Compact Edition. http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx

Answer 2

Another option is embedded Firebird.

Answer 3

The most easy switch would be to go with SQL Server compact edition, it can be embedded in your app, and from some light testing that I have done, it works really well.

Going to SQL lite will require more modification of the DAL.

NOW note that SQL Compact Edition doesn't support stored procedures! (Shouldn't be a big deal, but be sure to research the differences)

Answer 4

I few posters suggested SQL Compact Edition, but I think they really meant SQL Server EXpress Edition (http://www.microsoft.com/express/sql/default.aspx). The confusion over these editions is quite common, mainly due to Microsoft changing their naming at some point. Express edition runs on the desktop and does support stored procedures. Compact edition is designed to run on a mobile device.

Answer 5

There are ADO.NET wrappers for SQLite, not sure if that is what you want or just straight ADO. I have found SQLite to be very robust although you do need to VACUUM the database every now and then to recover unused space after deletions. You can do that from your code, so that isn't really administration. I just do it at startup or shutdown.

I agree with the others' opinions about SQL Express Edition. I use it for several apps.

If you are working with .NET, then you might want to look at Subsonic for your data access. It supports all of the above and more and makes switching fairly painless. For single user database apps, it is great. If you are going to do a rewrite, then it is worth it.

Answer 6

Other databases may be more stable than Access, but I'm not aware of anything that's as easy to use, and as easy for an end user to understand. Access (like SourceSafe) is always called "prone to corruption", but few and far between are the first-hand accounts of this. Most people "read it somewhere."

Consider just including a backup routine with your app that keeps the last two or three copies of the Access database if you're that worried about corruption.

If you insist on something else, then try SQL Server Compact Edition (as other have suggested), which (like Access) is just a file, a .sdf file in this case, and a few DLLs. Unlike SQL Server Express Edition, SQL Server CE doesn't leave a background process running all the time.