I am working for a company and I need to create a program really fast. My program will run with 100 users and they will make approximately 100 transactions each per day. As I am under time pressure, and various other constraints it is not possible to set up a proper database running on a server. I am therefore looking for alternatives that have some sort of transaction support without running on a server. I believe this could be solved using Microsoft Access, which is an alright solution, though I believe I will run into locking problems. Isn't is so that a whole table is locked as soon as one user attempts to read from it? Anyways... My question is what other alternatives there are.
+1
A:
Is sqlite a proper solution? Not sure how remote storage is supported, though. That's not a common feature.
Romain
2009-12-03 14:30:32
I have (accidentally) ended up with a SQLite database on a network (mapped) drive. Everything worked fine. My main problem was finding it... I thought I was using a local one and was very surprised when I noticed the "last modified" time wasn't changing. :-)
Richard J Foster
2009-12-03 15:16:23
+2
A:
The real answer is likely to vary significantly depending on what quantity of data is being talked about here.
I'd take a look at SQLite. It supports transactions, triggers, etc and is supported by things like NHibernate which may make your database mapping life much easier.
Richard J Foster
2009-12-03 14:37:50
Correct, that's what it's designed for. You could write your own wrapper layer, but that would be defeating the point...
Joe
2009-12-03 14:54:50
Yes, and I would have some concerns about using it in the long term. It might be fine with 100 users @ 100 transactions per day, but sooner or later the database size would start to become an issue. That's why I suggested NHibernate... it *should* make changing to a (supported) client-server database *much* easier.
Richard J Foster
2009-12-03 15:10:20
Oh... and I should probably clarify... by "database size" I don't mean file size - SQLite has no real issues to worry about there - I am more concerned about the query / update time. There is no way I know of to partition a SQLite database for better performance.
Richard J Foster
2009-12-03 15:13:47
+1
A:
There are many options. As others have stated, setting up and running with SQLLite, SQL Server Express, or any of a number of other small, light, and free databases.
Assuming you need this today, I would go with the one you know most about. Further, I would stay away from anything resembling Access. If you don't already have experience in using it for multi user access, you are going to burn too much time figuring out the problems.
That said, I'd lean towards SQL Server express first. It's free and can scale up to full sql server with no code changes.
Chris Lively
2009-12-03 14:43:15
A:
I believe this could be solved using Microsoft Access, which is an alright solution, though I believe I will run into locking problems.
I'd say locking and queuing would be the least of your worries. With 100 concurrent users, Access will probably corrupt itself in minutes. With 10k+ records/day, it will likely bog down your entire network in a month or so.
As I am under time pressure, and various other constraints it is not possible to set up a proper database running on a server.
You can bring a database server up in an hour. Much less time than you'll spend hacking away at Access. There's open-source virtual machine images, MSSQL Express, hosted solutions, etc. Time and cost should be non-issues.
About the only thing I can think of that would have you using Access is the Forms support (which can be hooked to MSSQL Server) or DBA maintenance. Frankly, though, at 100 users Access will take so much babysitting that you can afford a hosted SQL instance and still come out ahead.
Mark Brackett
2009-12-03 14:44:34
A:
I think that Firebird can be a very good alternative. Firebird is available in embedded and can also work with server. It have many features.
Hugues Van Landeghem
2009-12-03 19:11:09