MS Access vs SQL Server and others ? Is it worth taking a db server when less than 2 Gb and only 20 users

Question

After my experiment with MSAccess vs MySQL which shows MS Access hugely overperforming Mysql odbc insert by a factor 1000% before I would do the same experiment with SQL Server I searched for some other's people and found this one:

http://blog.nkadesign.com/2009/access-vs-sql-server-some-stats-part-1/

which says

"As a side note, in this particular test, Access offers much better raw performance than SQL Server. In more complex scenarios it’s very likely that Access’ performance would degrade more than SQL Server, but it’s nice to see that Access isn’t a sloth."

So is worth bother with some db server when data is less than 2 Gb and users are about 20 (knowing that MS Access theorically supports up to 255 concurrent users though practically it's around a dozen concurrent users only).

Are there any real world studies that really compare MS Access with other db in these specific use case ? Because professionaly speaking I keep hearing people systematically recommend DB server from people who have never used Access just because they think DB Server can only perform better in every case which I used to think myself I confess.

Answer 1

For 20 users and amounts of data that are well below Jet/ACE's limitations (I would start planning to upsize if a Jet/ACE data file approached 1GB), there is not necessarily a performance benefit to upsizing.

However, if you want the freedom to just throw 20 more users at your app, you'd have to have written it very carefully for that not to cause problems with a Jet/ACE data store. It can be done, but it requires more care in application design. Of course, if those 20 more users are almost all read-only, that increases your headroom, but they generally are not.

The other thing to consider is security and reliability. With a server back end you get a higher level of both. That doesn't mean the upsizing is necessarily justified (upsizing comes with costs that many fail to recognize, e.g., administrative, redesign of the app, and different user/security management tools) -- you have to balance the entire range of costs and benefits.

Conversely, a server back end is often fully justified with much smaller user populations and smaller amounts of data precisely because of the security and reliability requirements of those particular applications.

At 20 users, I'd say that if you're contemplating new development rather than upsizing an existing app, it would make more sense to use a server back end from the get-go in order to avail yourself of the breathing room it gives you for expansion. It means you don't have to be as careful in terms of implementation (unless you want to run it across a WAN, in which case you wouldn't be able to use a Jet/ACE back end, anyway).

But if you're just asking whether or not an existing app should be upsized just because it's reached 20 users, then the answer is, no, unless there are already performance issues with the Jet/ACE back end.

Answer 2

The main benefits of using SQL Server over Access have little to do with performance.

After all, you can get great insert performance by writing flat files.

SQL Server (and the SQL Server engine available even in the Express version) offer a vastly different feature set to Access.

Just as an example, Access in a networked or multi-user environment is not robust in any way - shut off a network connection while Access is open (even without a read or write in progress) on a network share, and your database willbe considered dirty and could possibly even be unrecoverable.

Answer 3

you can struggle over the possibilities and capabilities of different databases all day. However in the end, it all comes down to your actual table design and the queries that you will write.

Given the best server and best database in the world (whatever that is) if a noob designs a lame schema and uses poorly written queries, does it really matter what database is being used?

Conversely, given a modest server and database, an expert will still be able to get good performance.

Answer 4

You need to analyze what type of application you are going to be building. If it is something integrated with Microsoft office applications, access may be the right choice. If you cannot think of a specific advantage of using Access over MySQL for your application, I recommend you go with MySQL. You get the advantage of scalability and speed from the beginning. Not that you should worry about pre-mature optimization, but who knows if you will ever need to support more data and more users. Why limit yourself?