Microsoft Office 2000/Visual Basic Programmer's Guide

File-Server vs. Client/Server

What is a client/server solution? A useful way to understand it is to describe the differences between the underlying system architectures of file-server and client/server solutions. In a file-server solution, when your solution needs data, Access and the Jet database engine (which are running locally on a user's workstation) determine how to directly access the network drive, which files should be read, and what data to retrieve. Because retrieving data from the database file may require a series of requests and responses that must be sent between the server and the workstation, network traffic is increased. For example, to edit a record on a file-server database, the Jet database engine must read an index, retrieve the data, read and write to the locking file (a file that is used to coordinate requests to edit records between multiple users), and then update the index and the database itself. Figure 16.2 illustrates the file-server system.

Figure 16.2 A Typical File-Server System

A multiuser database system within a file-server environment is made up of:

• A database that resides on a network file server running an operating system such as Microsoft Windows NT Server or Novell NetWare.
  
  
• One or more users accessing the database from a workstation's application software.

In a client/server system, the network database server processes all requests for data on the server itself. The solution running on a user's workstation doesn't request data at the file level, but sends a high-level request to the server to execute a specific query and return its results. The primary advantage of this technique is that network traffic is reduced because only the result set of the query is returned to the workstation, as shown in Figure 16.3.

Figure 16.3 A Typical Client/Server System

A client/server system is typified by:

• A back-end database residing on the server, and controlled and maintained by the server software, such as Microsoft SQL Server.
  
  
• One or more users running a local client application, such as an Access project (.adp), which requests data from the server through an interface such as an OLE DB data provider.

The performance and simplicity of a file-server architecture make it ideal for small- to moderate-sized solutions. The primary deciding factor when choosing whether to use a file-server or client/server architecture is the number of users who will be working with your solution. As an absolute limit, an Access database can handle up to 255 simultaneous users, but if users of your solution will be frequently adding and updating data, an Access file-server is generally best for a maximum of about 25 to 50 users.

A file-server database also has inherent limitations because it is maintained as a file in the file system. By isolating all database files under the control of a database server, the client/server architecture can provide advanced features that can't be furnished by a file-server architecture. For example:

• Online backup   Use an automatic scheduler to back up your database without having to exclude users from the database.
  
  
• Durable transactions SQL Server   logs transactions so that updates made within a transaction can always be recovered or rolled back if either the client or the server computer fails. This allows the SQL Server database to fulfill all four requirements of true ACID transactions, as described earlier in this chapter in "Using Transactions."
  
  
• Better reliability and data protection   If either a workstation or file server fails while an Access database (.mdb) file is being written to, the database may be damaged. You can usually recover a damaged database by using the CompactDatabase method from either DAO or Microsoft Jet and Replication Objects (JRO), but you must have all users close the database before doing so. This rarely happens with a server database such as Microsoft SQL Server.
  
  
• Faster query processing   Because an Access database (.mdb) is a file-server system, it must load the Jet database engine locally to process queries on the client. For large databases, this can involve moving a lot of data over the network. In contrast, SQL Server runs queries on the server, which is typically a much more powerful computer than client workstations. Running queries on the server increases the load on the server more than an Access file-server solution, but can reduce the network traffic substantially — especially if users are selecting a small subset of the data.
  
  
• Advanced hardware support   Uninterruptible power supplies, hot swappable disk drives, and multiple processors can all be added to the server with no changes to the client workstations.

Another factor you need to consider when choosing whether to use a file-server solution or a client/server solution is the amount of data your solution will be required to work with. In Access 2000, an Access database can handle up to 2 gigabytes of data per .mdb file. If you really need to, you can effectively create even larger databases by using linked tables to several different .mdb files. However, SQL Server has a much higher limit, and is much less prone to data corruption.

If your solution needs to handle more users or requires the reliability of a database server, consider creating a client/server solution that employs an Access project as the client application and a server back-end database such as Microsoft SQL Server. For more information about building Access client/server solutions, see "Client/Server Solutions" earlier in this chapter.

Note   Access project files also support creating databases by using Microsoft Data Engine (MSDE). For more information about using MSDE to develop databases, see "Using Microsoft Data Engine" later in this chapter.