Access and File Based Databases Versus Enterprise Level Databases

Access is a cheap and easy to use file based database solution. However, it is designed to function in desktop environment. Using it in a web server environment poses many issues. In a production environment the official line is that Access should not be used.

What is a file based db?

An Access MDB file is a data file like a word document or text file. When you use an Access MDB file (or a dBase DBF file or an Excel spreadsheet), the application you are running opens the file and directly manipulates the data in it. As data is added, deleted or updated, the application manipulates the opened data file. If two applications (or applications on two computers) open the same file at once, then both applications open the file. On a practical level Access files are limited to 3 concurrent connections.

Access, FoxPro, dBase, FileMaker and Paradox are all examples of file-based databases.

How does this compare to Client/Server Databases?

Unlike file-based databases, client/server database files are never accessed directly. They are usually on another computer with restricted access. Your application communicates with a driver which in turn communicates with the database server and only the database server ever manipulates data.

The underlying data files used to store client/server data are never opened by client applications. If multiple client applications access data simultaneously, all requests are sent to the database server and it processes them sequentially or concurrently. Far more concurrent connections are available with these databases than with file based systems.

MySQL, SQL Server, Oracle, DB2, Informix and Sybase are all examples of client/server databases.

How data integrity is affected

When a computer crashes the data in any open application may be corrupted. If applications are not shut down in the correct manner then data may be lost.

All web servers will crash and will need rebooting. Hitting the reset button is anything but graceful. If you have an Access file open (just as if you had a Word file open), you run a very real risk of ruining that file, rendering it utterly useless and requiring that you restore a backup. Of course, backing up open files is highly problematic itself, so you might not even have a good backup of your data.

Client/server databases don't run this risk. If your Web server is rebooted, all open connections to the database server will be broken but that's it: the database server itself stays up and so the data remains safe. This assumes that the database server will be stopped far less frequently than the web server.

Security

Client/server databases don't run any such risk. If a cracker were to gain access to the Web server, the most they'd have access to is information about the database server, but not the database itself.

Features

Although data integrity and security are primary concerns here, there is one other important point to mention. Client/server databases support important features that are a pivotal component of secure and scalable applications. These include:

• User security: Used to manage and restrict data access to prevent accidental or malicious damage.
• Stored procedures: Used to improve performance as well as provide an additional level of data abstraction and security.
• Triggers: Used to perform server-side data manipulation resulting in improved performance and better quality data.
• Scheduled execution: Used for the scheduled execution of data housekeeping operations on the server. These execute far quicker than client-side events, with no client impact (and less server impact).

What are my Alternatives?

File-based databases are cheap and easy to use and for smaller applications they usually perform quite well, but they're also highly susceptible to data corruption and are insecure.

One excellent alternative is to use MySQL. This is an client/server database available with open source code from MySQL.com.

Information relating to how Access files can be converted to MySQL files can be found there.

Reality

Despite everything written above, most ColdFusion applications continue to run Access. In our experience, for the majority of applications which do not have high traffic, Access works without problems. The database is easy and cheap to implement and is more robust than you may think. Perhaps the best way to start is with Access, then upgrade database as your traffic and needs for performance and security increase.

Coldfusion Resources Home  |  Top of Page