Access vs SQL Server - What is Better for Your Lab's LIMS?

Access vs SQL Server - What is Better for Your Lab's LIMS?

For many people the thought of having to understand database architecture and/or mechanics may seem dizzying. It certainly can be, especially if database architecture is not your wheelhouse.

That's okay, you don't have to understand the databases in detail. What you really need to know is how different types of databases vary.

SQL Server is a full blown enterprise RDBMS (ODBC compliant) database package whereas MS Access is really a desktop (ISAM) database tool. To be fair, MS Access is an amazing product for a desktop app, but it cannot scale nearly as much as SQL Server. 

Whether you are looking for the best LIMS program that money can buy, or simply the most affordable LIMS program for a small lab, scalability will eventually become an issue.

SQL Server is a 'real' database system, on the same level as Oracle and Sybase. Some individuals would also like to add MySQL to this list, however MySQL is a free database package and ultimately you do get what you pay for.

SQL Server is for multiple users. MS Access is technically for multiple users, but have you used it in this fashion? Have you had twenty or more concurrent (simultaneous) users? How did that work out for you? - Please feel free to leave a heated comment below about any interesting experiences on this. Feel free to vent on our dime.

SQL Server is designed to handle many more users. As a result of this, SQL Server is more expensive, not just to acquire the program, but additionally their are user licenses (CAL's) to consider. That being said, it is not entirely unexpected. A typical new automobile is much more expensive than a typical new bicycle, but no one would really question that, would they?

In the old days (late 90's) many smaller labs would come with a home grown LIMS in MS Access that would eventually need to be converted to a SQL Server backend with most likely a VB front end. These days you are almost better off starting right with SQL Server, especially since you can start off with SQL Server Express, a free solution. The thought is that you can upgrade to a more robust version of SQL Server as your business grows, which means that you will be able to afford the more robust version.

Another difference is that MS Access needs to constantly be monitored. There is a setting called "Repair and Compact database". Seriously! What other apps comes with a built in feature that basically says: "We know this will break" ?

You are probably past asking "do I need a LIMS?" or "Does my lab need a LIMS?" since the answer is painfully obvious. Today it is not a question of whether or not you need a LIMS. Maybe that was valid eighteen years ago. Today the real question is how extravagant of a LIMS solution do you need?

Many Access based apps may work fine for you, for a while, but you will eventually need to scale upward, if you are planning on growing.

Some LIMS vendors, like Lablite, makers of Lablite SQL LIMS, originally only offered an Access based app, but quickly scaled up to a full blow SQL Server backend. Note: to give you an idea of how long ago this was, you could install the app with a stack of twenty or so 3.5 inch floppies. We are talking about the stone ages.

One last thing to say about Access. If you think that as a stand alone app your Access database isn't really a Client Server app, or more generically that it doesn't have a front end and a back end, well, guess again.

Most developers are smart enough to create two separate Access files. The back end file contains the data tables. The front end has the forms and reports, or the "GUI".

This is so that the developer can make changes to a copy of the front end (reports and forms) while the back end is still being used by the lab. Later, when the changes are made, the developer just needs to replace the front end file, for the lab to see the report changes and /or the form changes, but the back end database has remained undisturbed.

At run time, the two files need to be synced up in order for the whole app to work. Does that sound like a stand alone app to you?

For more information on the topic of databases, see the article:

SQL Server or MS Access, which is right for me?