Understanding File-System Databases
(How Are They Different from Client/Server? And, What Does That Mean To Me?)
by Mike Robinson
There are two fundamental technologies in common use today for the handling of databases: filesystem (or “shared file”) databases, and client/server. In this article, we’ll take a look at how each of these technologies work, and what those differences mean to you and to your business.
Paradox®, dBase®, and Microsoft Access® are typical examples of the “filesystem” approach. Physically, “a database” is either a directory or a single file. All of the logic needed to work with the database is located on each user’s machine. If the databases are being shared with other users, they are being shared only as files. The server that is providing the file-sharing capability has no idea what the files contain. The server simply responds to “file read/write” requests, as it would for any file.
Microsoft SQL Server,® MySQL,® Oracle,® and DB/2® are typical examples of the “client/server” approach. Physically, “a database” is a directory, a single file, a disk partition, or even one or more entire disk volumes. But the server which provides access to the database is fully aware that they are “a database” and provides you access to the data only in that way. All of the logic for managing the database exists only on the database server(s).
It is customary to refer to client/server database servers as “SQL servers,” but strictly speaking this is not correct. Several fileserver-based systems implement SQL, with Microsoft Access perhaps being the most complete example of this. The SQLite™ engine (see below) implements a more-limited but nonetheless quite powerful subset. Furthermore, there are some “hybrid” technologies which blur the distinct line that I have just drawn, placing a fully-functional database engine both on the end-user’s workstation and on a central server, and partitioning the responsibilities intelligently between both computers.
The filesystem strategy is perfectly appropriate when you are sharing small amounts of information between a handful of machines. It is frequently the case that “the user’s local machine does most of the real work anyway,” and the amount of file input/output that must take place against the central database files is comparatively small. If the central tables are well-indexed and mostly read-only, a filesystem database may out-perform a client/server database quite handily. There are also many applications, such as point-of-sale terminals, which require the ability to work just as well “offline” as they do when connected to a server.
The client/server strategy is appropriate when the volume of data is very large, when data security is very important, and when the workload is large enough that it needs to be shared among multiple servers, and perhaps, multiple geographic locations. But the drawback is obvious: the database server(s) are obliged to shoulder all of the work, on behalf of all of their clients. A successful, large scale, client-server database implementation can require some very “big iron,” indeed. (If you are thinking that mainframe computers “went the way of the dodo bird,” think again ...)
Incidentally, just in case anyone might be thinking that a fileserver-based strategy is somehow “obsolete,” let the record show that SQLite™, a public-domain SQL-based fileserver engine, is found in just about every portable electronic device that is to be found anywhere today. It is “the most widely deployed SQL-based database engine in the world,” estimated at more than 500 million installations. A fileserver-based technology.
The “care and feeding” strategy for both types of databases is remarkably similar. (First of all, you must have such a strategy and you must adhere to it religiously.) The hardware must be top-rated and designed for continuous service. The power supply must be protected by a robust uninterruptible power supply (UPS) system. The databases must be backed-up regularly, and checked for internal consistency before there is any outward indication of problems.
Fileserver-based databases have the additional consideration that each computer with access to the database could well be modifying the files. Hence, all of them must have UPS power-supply protection (as must the network routers and other hardware), and they must be running the same version of the operating system and database engine software. When a problem arises in such a database, it can usually be traced to one workstation.
The most important management strategy is: be proactive, not reactive. Look for trouble before it finds you. Database table repair tools such as ChimneySweep® are designed to promote this behavior.