As a rule I try to avoid the Access/CF discussion as it in-evitably provokes strong debate and even stronger emotions. Besides, the truth is - regardless of what I might suggest - Access remains an inexpensive and easily implemented solution. So why am I writing about Access now? Because I have come to realize that many users are considering only cost and performance in their decision-making process and are overlooking the bigger issues.

Let me start by making it very clear that I've nothing against Microsoft Access: it's a great desktop database product, perhaps the very best one there is. And despite this column's title (sorry, I couldn't resist that one), the comments that follow apply not only to Access. I'm writing about shared file- based databases in general, all shared file-based databases, of which Access happens to be the most popular.

File-Based Databases
You've undoubtedly used a word processor. When you open a document for editing, that file is opened on your computer and remains open until you close the document (or quit the application). From a functional perspective your document is a data file, much like an Access MDB file. When you use an Access MDB file (or a dBase DBF file or an Excel spreadsheet), the application you're running opens the file and directly manipulates the data in it. As you add, delete or update data, the application manipulates the opened data file accordingly. Even if you access the data via ODBC (perhaps using ColdFusion), that data file is actually open on the computer running the application. If two applications (or applications on two computers) open the same file at once, then both applications open the file (and some sort of locking mechanism is used to arbitrate requests).

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

Client/Server Databases
Unlike file-based databases, client/server database files are never accessed directly. In fact, they're usually on another computer altogether, one with restricted access. So how do you access client/server data? Your application communicates with a driver which in turn communicates with the database server and only the database server ever manipulates data.

As such, the underlying data files used to store client/server data are never opened by client applications. Even if you use your DBMS's bundled management tools (for example, SQL Server Enterprise Manager), all data manipulation is actually performed by the database server. Data access via ODBC (or even native database drivers) works much the same way: ODBC is merely a client of the database server; it can't access the data directly. If multiple client applications access data simultaneously, all requests are sent to the database server and it processes them sequentially or concurrently.

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

Data Integrity
Most of us have experienced computer crashes (GPFs, blue screens of death and so on). When something like this occurs - as you're probably painfully aware - you run the risk of corrupting the data in any open application. If you're lucky you'll lose just your most recent changes, but often entire documents and files become corrupt (containing random, incomplete or truncated data). If you don't quit applications gracefully you run the risk of losing data. We've all experienced this and hopefully we all hit the save button (or use an autosave feature) to help ease the pain somewhat.

Web servers crash; it's unfortunate but it's a fact. File and print servers seem to stay up forever; most Internet servers don't. I have a NetWare 3.12 server in my basement running on a 386/33 with 8 MB of RAM that has been up for over 200 days and hasn't missed a beat. I don't see many Web servers (running on any platform, not just Windows) that can make that claim. Hopefully servers and their uptime will improve, but for now you must assume that your Web server will crash and will need rebooting. And 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 trashing 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. Not a pleasant thought at all.

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. (We're assuming of course that your database server crashes less frequently than your Web server, which is an argument for never running the Web server and database server on the same machine.)

Security
Web servers are inherently insecure. They are highly visible, their IP addresses are usually known, the software they are running can easily be determined and path information can often be inferred. Couple that with the frequent discovery of new holes in Internet software and it becomes blatantly obvious why Web servers are primary targets for hackers and cyberthieves.

Good practice dictates that Webmasters be somewhat paranoid, always considering their Web servers to be highly vulnerable. The assumption that anything on the Web server can and will be stolen should be the driving force in determining what actually gets put on that server. (On a side note, this is why you must never hard-code login information or passwords in CFM files. Those files may get stolen and that information could end up in the wrong hands.)

So what has all this to do with databases? For ColdFusion to access file-based databases, it needs access to those files. This means the data files must reside on the Web server or on a path that it can access. And if CF can access those files, so can anyone who breaks into the server. If the thought of your code being stolen scares you, then the thought of your data being stolen could keep you awake at night.

Client/server databases don't run any such risk. If a hacker 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 server itself. (We're assuming that the database server itself is secure, as it should be.)

Features
Although data integrity and security are my 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:

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).

And There's Much More Too...
It's worth noting that these features tend to be very DBMS-specific, and support and implementation vary dramatically from one DBMS to the next. But regardless of the DBMS in question, all client/server DBMSs offer features of this type and they should be used.

Summary
File-based databases are cheap and easy to use and for smaller applications they usually perform quite well, sometimes even outperforming client/server databases. But they're also highly susceptible to data corruption and are terribly insecure. For most organizations there's nothing more precious than data and if this is true of your organization, then file-based databases should never be used with ColdFusion. This alone should be enough to push you toward client/server databases. When you add all those extra features to the equation, the conclusion is obvious: desktop databases belong on desktops, not on Internet servers.

Published May. 25, 2000— Reads 9,890 — Feedback 3
Copyright © 2000 SYS-CON Media, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.

About Ben Forta
Ben Forta is Adobe's Senior Technical Evangelist. In that capacity he spends a considerable amount of time talking and writing about Adobe products (with an emphasis on ColdFusion and Flex), and providing feedback to help shape the future direction of the products. By the way, if you are not yet a ColdFusion user, you should be. It is an incredible product, and is truly deserving of all the praise it has been receiving. In a prior life he was a ColdFusion customer (he wrote one of the first large high visibility web sites using the product) and was so impressed he ended up working for the company that created it (Allaire). Ben is also the author of books on ColdFusion, SQL, Windows 2000, JSP, WAP, Regular Expressions, and more. Before joining Adobe (well, Allaire actually, and then Macromedia and Allaire merged, and then Adobe bought Macromedia) he helped found a company called Car.com which provides automotive services (buy a car, sell a car, etc) over the Web. Car.com (including Stoneage) is one of the largest automotive web sites out there, was written entirely in ColdFusion, and is now owned by Auto-By-Tel.