Although all OLTP systems are oriented toward the same goals, there are several different underlying system architectures that you can use for the deployment of OLTP, including the traditional two-tier model, a three-tier model, and a model that encompasses the use of the Web.
The late 1980s saw the rise of two-tier client/server applications. In this configuration, PCs acted as clients accessing a separate database server over a network. The client ran both the GUI and the application logic, giving rise to the term fat clients. The database server processed SQL statements and returned the requested results back to the clients. While database servers were relatively simple to develop using visual tools, client/server systems were difficult to deploy and maintainthey required fairly high-bandwidth networks and the installation and regular upgrading of specific client software on every user's PC.
Figure 8-1 illustrates the two-tier architecture.
Oracle7 introduced stored procedures written in PL/SQL, Oracle's proprietary language for writing application logic. These procedures are stored in the database and executed by clients issuing remote procedure calls (RPCs) as opposed to executing SQL statements. Instead of issuing multiple SQL calls, occasionally with intermediate logic to accomplish a task, the client issues one procedure call, passing in the required parameters. The database executes all the required SQL and logic using the parameters it receives.
Stored procedures can also shield the client logic from internal changes to the data structures or program logic. As long as the parameters the client passed in and received back don't change, no changes are required in the client software. Stored procedures move a portion of the application logic from the client to the database server. By doing so, stored procedures can reduce the network traffic considerably. This capability increases the scalability of two-tier systems. Figure 8-2 illustrates a two-tier system with stored procedures.
The OLTP systems with the largest user populations and transaction throughput are typically deployed using a three-tier architecture. In the past, the three-tier architecture involved a transaction processing (TP) monitor, but now it more frequently uses an application server. Clients access a TP monitor or application server in the middle tier that, in turn, accesses a database server on the backend. The notion of a TP monitor dates back to the original mainframe OLTP systems. Of course, in the mainframe environment all logic ran on one machine. In an open system environment, application servers typically run on a separate machine (or machines), adding a middle tier between clients and the database server.
There are various classes of application servers:
Older, proprietary servers such as Tuxedo from BEA Systems on Unix and Windows, or CICS from IBM on mainframes
Industry-standard application servers based on Java 2 Enterprise Edition (J2EE)
The Microsoft .NET application server environment as part of the Windows operating systems for servers, for example, Windows 2000 or Windows 2003
Application servers provide an environment for running services that clients call. The clients don't interact directly with the database server. Some examples of calling services provided by an application server or a TP monitor on a remote machine seem similar in many ways to the stored procedure architecture described in the previous section, which is why stored procedure-based systems are sometimes referred to as "TP-Lite." On the other hand, systems that make use of true TP monitors are sometimes referred to as "TP-Heavy."
Application servers provide additional valuable services, such as:
Funneling
Like Oracle's shared servers, application servers leverage a pool of shared services across a larger user population. Instead of each user's connecting directly to the database, the client calls a service running under the application server's control. The application servers invoke one of its services; the service interacts with the database.
Connection pooling
The application server maintains a pool of shared, persistent database connections used to interact with the database on behalf of clients in handling their requests. This avoids the overhead of individual sessions for each client.
Load-balancing
Client requests are balanced across the multiple shared servers executing on one or more physical machines. The application servers can direct client service calls to the least-loaded server and can spawn additional shared servers as needed.
Fault-tolerance
The application server acts as a transaction manager; the monitor performs the commit or rollback of the transaction.[2] The underlying database becomes a resource manager, but doesn't control the transaction. If the database server fails while executing some transaction, the application server can resubmit the transaction to a surviving database server, as control of the transaction lies with the application server.
[2] TP monitors usually control transactions using the X/Open Distributed Transaction Processing standard published by the X/Open standards body. A database that supports the XA interface can function as a resource manager under control of a TP monitor, which acts as a transaction manager.
This type of transaction resiliency is a hallmark of the older TP monitors such as Tuxedo, and the newer application servers and standards offer similar features.
Transaction routing
The logic in the middle tier can direct transactions to specific database servers, increasing scalability.
Application servers can manage transactions across multiple heterogeneous database serversfor example, a transaction that updates data in Oracle and DB2.
While developing three-tier OLTP systems is complex and requires specialized skills, the benefits are substantial. Systems that use application servers provide higher scalability, availability, and flexibility than the simpler two-tier systems. Determining which architecture is appropriate for an OLTP system requires (among other things) careful evaluation and consideration of costs, available skills, workload profiles, scalability requirements, and availability requirements.
Figure 8-3 illustrates a three-tier system using an application server.
The middle tier of web-based systems is usually an application server and/or a web server. These servers provide similar services to the application server previously described, but are more web-centric, dealing with HTTP, HTML, CGI, Java, and Object Request Brokers (ORBs).
J2EE and .NET application servers have evolved a great deal in the last 5 years and are the clear inheritors of the TP monitor legacy for today's N-tier systems. Different companies have different standards and preferencesthe proprietary nature of .NET leads some firms to J2EE, while others prefer the tight integration of Microsoft's offerings. A detailed discussion of the relative merits of J2EE and .NET, and application server technology in general, is beyond the scope of this book. Suffice to say that application servers play an extremely important role in today's systems environment and database management personnel need to understand N-tier systems architecture.
Figure 8-4 depicts an N-tier system with a client, web server, application server, and DBMS server:
Oracle Database 10g is focused on another architecture, grid computing. The actual topology of the grid is not relevant to the discussion in this chapter, because the point of the grid is to provide an extremely simple user interface that transparently connects to a highly flexible source of computing power. In this way, the grid gives IT departments the ability to achieve the benefits of more complex architectures while not imposing undue complexity on users, and OLTP applications are deployed using grid computing resources.
We describe the topology and features of grid computing in some detail in Chapter 14.