The logical three-tier model divides an application into three logical components.
Figure 1. The three-tier model
These services join records and maintain database integrity—for example, constraints on valid values for a customer number and an enforced foreign-key relationship between the customer table and the orders table.
These services apply business rules and logic—for example, adding a customer order and checking a customer's credit availability.
These services establish the user interface and handle user input—for example, code to display available part numbers and orders for a selected customer.
When deploying an application, there are many ways you can arrange these three logical layers on physical machines. The following sections describe four physical implementations of the logical three-tier model:
A common method for deploying an application is a physical two-tier implementation with fat clients, where the business logic and presentation services all run on the client. In this implementation, the server acts only as a SQL Server database. Most applications written today using the Microsoft Visual Basic® or PowerBuilder programming systems are examples of this model.
Figure 2. Two-tier implementation (fat client)
A new option in this implementation is the ability to do OLE packaging of business rules for improved reuse. For example, using Visual Basic version 4.0 or later you can code business rules into an OLE object that you can call from another Visual Basic application. This allows you to physically separate business rules from your presentation logic in the code base. If both the user interface application and the business object run at the client, it is still a physical two-tier implementation. Separating the code, however, makes it easy to move to the physical three-tier implementation described later in this paper.
A primary advantage of this fat client implementation is that the tools that support it are powerful and well established. As of this writing, PowerBuilder is in its fifth major version, and Visual Basic is about to be released in its fifth major version. A disadvantage of this implementation is that deploying the business services at the client generally means more network traffic because the data has to be moved to the client to make the decisions coded in the business logic. On the other hand, the client computer is a good place to store "state" information associated with the user, such as the primary key of the record the user is currently viewing.
In a physical two-tier implementation with a fat server, business logic and presentation services are deployed from the server database. In this implementation, business logic is generally written as stored procedures and triggers within the database. For example, in the TPC-C benchmarks published for Microsoft SQL Server, the core transaction logic is coded as Transact–SQL stored procedures in the server. Many internally-developed corporate applications also make extensive use of stored procedure logic. Microsoft uses this implementation to handle internal business functions, such as customer information tracking.
Figure 3. Two-tier implementation (fat server)
The major new development in this implementation is the availability of a Transact–SQL debugger. This debugger is integrated into the Enterprise Editions of both Microsoft Visual C++® version 4.2 and later and Visual Basic version 5.0. This debugger makes it possible to step through Transact–SQL code, set breakpoints, and view local variables.
The major advantage of this fat server implementation is performance. The business logic runs in the same process space as the data access code and is tightly integrated into the data searching engine of SQL Server. This means data does not have to be moved or copied before it is operated on, which results in minimal network traffic and the fewest possible network roundtrips between client and server. The published TPC-C benchmarks from Microsoft Corporation and other major database vendors all use this implementation. In the SQL Server TPC-C implementation, each of the five measured transactions is performed in a single roundtrip from client to server because all of the logic of the transaction takes place in a Transact–SQL stored procedure.
The main disadvantage of this implementation is that it limits your choice of development tools. Stored procedures are written in the language supported by the database. SQL Server supports calls from the server to code written in languages other than Transact–SQL, but this option adds complexity and is generally not as efficient as the same functionality written in Transact–SQL.
The physical three-tier implementation is one of many implementations of the logical three-tier model. Commonly referred to as the "three-tier model," it is often incorrectly thought of as the only physical implementation of the logical three-tier model. In this implementation, business logic runs in a separate process that can be configured to run on the same server or a different server from the server the database is running on. The key distinction of the physical three-tier implementation is that there is a cross-process boundary, if not a cross-computer boundary, between data services and business services, and another cross-process or cross-computer boundary between business services and presentation services. SAP's R/3 application suite is a physical three-tier implementation, as are many of the large financial and line-of-business packages from other vendors. Transaction processing monitor products such as Encina or Tuxedo also use this implementation.
Figure 4. Three-tier implementation
A major new option for using this implementation is Microsoft Transaction Server. Transaction Server can host business services written in any language that can produce OLE objects. Transaction Server manages the middle layer and provides many of the run-time services that would otherwise have to be built for a physical three-tier implementation. For example, Transaction Server provides a mechanism for reusing object instances among multiple users.
The physical three-tier implementation offers advantages of database independence. Most physical three-tier implementations access several databases. These applications generally treat databases as standardized SQL engines and make limited use of database-specific features.
Some variations of the physical three-tier implementations also offer language independence. Microsoft Transaction Server, for example, supports any language that can produce OLE/COM in-process objects, including Visual C++, Visual Basic, and Micro Focus COBOL. Any of these languages can be used to write business logic that is then hosted at run time by the Transaction Server. SAP's application, on the other hand, does not offer language independence—all application code developed in R/3 is written in their language called Advanced Business Application Programming (ABAP).
In some cases, the physical three-tier implementation is more scalable than other physical implementations. If the business logic code consumes a great deal of processor time or physical memory, it can be advantageous to locate those business processes on one or more servers separate from the database to avoid contention for resources. This potential scalability gain is offset by the additional cost of moving data across the network to the middle-tier servers, so it is not a win in all cases. Physical three-tier applications can also potentially access partitioned databases on multiple computers, giving an additional dimension of scalability. Partitioning the database, however, introduces enormous complexities into the application and is not a widespread practice in the industry today.
A disadvantage of the physical three-tier implementation is that it tends to require more management. Also, while the physical three-tier implementation can offer the capability to employ more physical computers on an application, it generally does not offer as compelling a price/performance ratio as an application whose logic is implemented in stored procedures.
The Internet has introduced a new twist on the logical three-tier model: the ability to split the presentation services onto a browser client and a Web server. The Web server is actually responsible for formatting the pages that the user sees. The browser is responsible for displaying these pages and downloading additional code they may need. Between the Web server and the database, the choices remain the same for locating the business services logic.
A common Internet implementation is to run both business and presentation services at the Web server. In some products, the business logic can run in the Web server's process space, thus avoiding the overhead of crossing an additional process boundary. An example of a product that uses this implementation for database applications is Microsoft Internet Database Connector (IDC), which is part of the Microsoft Internet Information Server (IIS) in the Microsoft Windows NT® operating system. IDC connects to any ODBC data source, including SQL Server, retrieves data, and formats the data into an HTML page that is sent immediately to a browser client.
Figure 5. Internet implementation
There are many newly released products that support Internet implementations of database applications. For example, IIS version 3.0 allows developers to write business and presentation services in Visual Basic Script and includes the ability to load and invoke an OLE Automation object. Also, Microsoft ActiveX™ controls offer a way to run more of the presentation services and possibly the business services from the browser client. These extensions to Internet technologies give more flexibility for where you can deploy the logical three tiers of a database application written for browser clients.
One key advantage of Internet implementations is that anybody who has a browser client can access these applications. With little or no additional development effort, an application can be accessed simultaneously from the Microsoft Windows® operating system version 3.1, Windows 95, Windows NT, Apple® Macintosh®, OS/2, and UNIX clients. All of the client functionality required is provided by standard Web browsers. Ease of management is another key advantage of an Internet implementation. In an Internet application, an update to the Web server automatically updates all clients. Managing Web page code at a few servers is easier than managing application versions at many clients.
The basic Internet implementation today (for example, using IIS and IDC and putting business services at that Web server layer) is not a high volume online transaction processing (OLTP) solution. But it is important to note that the application implementations discussed in this paper can be mixed to combine their advantages. For example, an implementation that uses an application's business services in stored procedures and that handles presentation services at the Web server can be very efficient. In fact, Microsoft's latest TPC-C benchmarks use IIS to handle browser clients, as opposed to using the alternatives (see note below). So an Internet-style application can be used for high volume OLTP if business services are executed as stored procedures in the database.
Note The TPC-C benchmark requires fixed-function workstations (terminals) for end users. Most TPC-C benchmarks from other database vendors use Telnet-like servers running on UNIX to support these terminals. The IIS based on Windows NT has proven to be more cost-effective and more efficient at managing clients for SQL Server benchmarks.
Your application requirements will determine which physical implementation of the logical three-tier model you choose. The key requirements to consider are:
If your throughput requirements are high and optimum price/performance is the goal, an implementation that uses business logic in stored procedures may be called for.
If your business services are resource intensive and the ability to apply many servers to the application is the goal, a physical three-tier implementation may be best.
On the other hand, PC hardware has become so powerful and cost effective that your application performance requirements can be satisfied easily by any one of these implementations.
If a variety of client platforms must have access to your application, an Internet implementation is compelling.
If you have developer skills or existing code in a particular language, the cost of choosing an implementation supported by that language is significantly lower.
Different implementations require different administrative overhead.
Some implementations require an application to be oriented to a specific database or language.
All of these considerations affect the decision of how to physically implement a three-tier application. There is no one correct answer—the best course of action is to thoroughly understand the alternatives and the trade-offs before choosing an implementation.
When you choose a development tool and decide how to implement the logical three-tier model, you must also choose the interface by which your client application will communicate with SQL Server. The best interface to use depends on the development language and the type of application under development. The choices fall into three categories: