3

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?

Corey Trager
  • 22,649
  • 18
  • 83
  • 121
  • If you're not creating forms/reports/etc. in Access, you're only using the Jet database engine, and you're not using Access at all. In my experience, Jet dbs don't corrupt unless you're incompetent in designing your app and in maintaining the operating environment. – David-W-Fenton Oct 25 '08 at 21:40
  • Also see http://stackoverflow.com/questions/271319/lightweight-sql-database-which-doesnt-require-installation – nawfal Feb 23 '13 at 19:50

5 Answers5

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.

Ryan Lundy
  • 204,559
  • 37
  • 180
  • 211
  • 1
    I have personally experienced Access corruption, but just twice in two years of daily debugging, crashing, stopping my app. In the real world, where our app is deployed at a few dozen places, once customer did end up with a corrupt db, which the Access app was able to repair. – Corey Trager Oct 24 '08 at 15:54
  • I do not remember any situation where access files were corrupted in a single-user environment. Kyralessa's backup routine proposal would make an access solution bullet-proof in your case. – Philippe Grondier Oct 24 '08 at 21:43
2

Another option is embedded Firebird.

CesarB
  • 43,947
  • 7
  • 63
  • 86
  • One of de advantages of Firebird is that the DataBase file can be used with either the Embdedded version or the actual server. – Fabio Gomes Oct 24 '08 at 15:29
1

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)

Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
  • Probably won't be a big deal since Access doesn't support them either. Still a very valid point if they are considering this switch for anything more than getting away from admin problems in Access. – Totty Oct 24 '08 at 15:09
1

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.

Rob Prouse
  • 22,161
  • 4
  • 69
  • 89
0

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

Totty
  • 926
  • 2
  • 9
  • 18