MS Access and SQL Server and others? Should I take a db server with less than 2 Gb and only 20 users
After my experiment with MSAccess vs MySQL, which shows that MS Access is heavily overloading Mysql odbc, insert a factor of 1000% before I do the same experiment with SQL Server. I searched some other people and found this:
http://blog.nkadesign.com/2009/access-vs-sql-server-some-stats-part-1/
which says
"As a side note, in this particular benchmark, Access offers much better performance than SQL Server. In more complex scenarios, it's very likely that Access's performance will degrade more than SQL Server, but it's nice to see that Access isn't a sloth."
So it's worth tinkering with some kind of db server when the data is less than 2GB and the users are about 20 (knowing that MS Access theoretically supports up to 255 concurrent users, although in practice it only has about a dozen concurrent users).
Is there any real world research that actually compares MS Access to other db's for these specific use cases? Because professionally speaking, I constantly hear people systematically recommend a database server from people who have never used Access because they think that a database server can only perform better in every case that I used to consider myself recognized as.
a source to share
For 20 users and data that is well below the Jet / ACE limits (I would start planning a boost if the Jet / ACE data file is close to 1GB), there is no need to improve performance.
However, if you wanted the freedom to just throw 20 more users into your application, you would have to write this very carefully so as not to create problems with the Jet / ACE datastore. It can be done, but it requires more care when developing applications. Of course, if those 20 users are almost entirely read-only, this increases your headroom, but they usually aren't.
Safety and reliability are another matter. On the back of the server, you get a higher level of both. This does not mean that the increase should be justified (the increase in costs is associated with costs that many cannot recognize, such as administration, application redesign, and various user / security management tools) - you need to balance the full range of costs and benefits.
Conversely, a server end is often fully justified with much fewer users and less data precisely because of the security and reliability requirements of these particular applications.
With 20 users, I would say that if you are planning a new development and not increasing the size of an existing application, it would be wiser to use the server end from get-go to take advantage of the breath that gives you extensibility. This means you don't have to be as careful from an implementation standpoint (unless you want to run it over the WAN, in which case you won't be able to use the back end of Jet / ACE).
But if you are just asking if an existing application should be jailbroken just because it reached 20 users, then the answer is no, unless there are performance issues with the Jet / ACE back end.
a source to share
you can compete daily for the capabilities and capabilities of different databases. However, in the end it all comes down to your actual table design and the queries you will be writing.
Considering the best server and best database in the world (whatever it is), if noob designs chrome schema and uses poorly written queries, does it really matter which database is used?
Conversely, given the modest server and database, an expert can still get good performance.
a source to share
The main benefits of using SQL Server over Access have little to do with performance.
After all, you can get excellent insert performance by writing flat files.
SQL Server (and the SQL Server engine available even in the Express version) offers a completely different feature available to Access.
For example, access in a network or multi-user environment is not reliable - disconnect the network connection while access is open (even without reading or writing) on a network share, and your database is considered dirty and may even be unrecoverable.
a source to share
You need to analyze what type of application you are going to build. If it's something integrated with Microsoft office applications, Access might be the right choice. If you can't think of the specific benefit of using Access over MySQL for your application, I recommend that you turn to MySQL. You benefit from scalability and speed right from the start. Not that you are worried about early optimization, but who knows if you ever need to support more data and more users. Why limit yourself?
a source to share