Part three of this excerpt series covers configurations for using SQL, two and three tier architecture on a LAN, n-tier architecture on the Web, mainframe and terminals, and mainframe to mainframe.
Connecting the Front and Back Ends
Having established a front end and a DBMS back end, we then need to connect them through some
type of software. The connection functions include: defining which datastore to use, how to transfer
SQL statements and results, and a host of settings that control the above processes. For example, in VB
we may want to work with data in an Access database. We need to establish a connection to pass SQL
statements to Access. We will want to establish how to handle multiple-user conflicts as well as whether
to read the data as fast as possible or with more flexibility. Once these parameters are set for the data
communication, we then need a way of actually sending our SQL statement to the DBMS.
By analogy, if the front and back ends are like telephone handsets, then the connection is like the
telephone company equipment and wires. When starting a call we first establish a connection by dialing
the other party and waiting for the phone company to connect the correct wires. Then we can begin
using English across the connection. In the same way, we create a connection between our front- and
back-end software, then we use SQL statements to communicate across that connection.
In this section we present an overview of four connection methods. We then move on to demonstrate
the actual code for creating a connection (with tricks and traps) in Chapter 2.
An older and lower-level connection is ODBC (Object Database Connectivity), which was a result of
collaboration throughout the industry in the early 1990s. Although not optimized for any particular
database, it has enjoyed widespread use up until the late 90s. ODBC is not well suited to non-relational
data. ODBC uses a driver for each type of datastore to hold communication specifics.
Chronologically next came OLEDB, a set of COM (Component Object Model) interfaces that can
interact with most data stores.
COM objects are packages of code that have a standard interface for interacting with
other code. Instead of thousands of programmers writing and troubleshooting
duplicate code to achieve the same objectives, the task can be solved once by some
experts and the result encapsulated into a COM object. The object can then be used
by anyone that buys the object. You can think of COM objects as mini-applications
that are available to programmers.
COM depends on an Application Programming Interface (API) optimized for talking with C++.
OLEDB uses providers to connect directly to the datastore. Alternatively, OLEDB can connect through
the older ODBC and use the ODBC Drivers.
ODBC uses Drivers while OLEDB uses Providers.
Both are "middleware" that contain instructions for how to talk to a specific kind of
Now Microsoft offers the ActiveX Data Objects (ADO), which are COM objects for talking with data. In
a sense, ADO encapsulates OLEDB into an object-based model. Instead of writing lengthy (and
difficult) code in OLEDB, a programmer can simply instantiate one of the ADO objects and then set its
properties to make a connection. An ADO object can send a SQL statement through that connection
and receive back results.
For more information on ADO see the ADO 2.6 Programmers Reference (ISBN 186100463x) by David Sussman from Wrox Press.
Another popular technique to connect front and back ends is using the JDBC (Java Database
Connectivity) object. As with ADO, experts have encapsulated into a set of classes and interfaces all the
Java code needed to pass SQL statements from a front end to a back end. JDBC allows developers to
create connections entirely within a pure Java API. With a JDBC solution you not only have the ability
to use SQL to access any back end, but you write in Java so you can run the code on most front ends.
Other software vendors have solutions for connecting the front and back ends. Allaire's Cold Fusion
uses a proprietary set of HTML tags. Some DBMS vendors have developed specialized connections
specific to front ends and DBMS that they offer.
The techniques discussed in this section offer many connection options in order to satisfy a wide range
of users. Some situations justify writing a custom coded connection. Although expensive and difficult to
create, these custom connections can gain in performance by eliminating features that are not used.
Configurations for Using SQL
Before we move on we want to demonstrate some typical installations that use SQL. These drawings
demonstrate a range of situations which you might encounter (or design), from a simple desktop to a
complex multi-server environment.
How to Study These Configurations
Note that each section has a SQL requester, a back end and a connection. In many cases the requester is
a front end (user interface) but not always. Consider the case where there are several servers between
the user and the DBMS. In those scenarios, an intermediate server is the SQL requester.
Note the difference between a front end and a SQL requester. The front end deals with humans. The
SQL requester is software that generates a SQL statement. Generally the requester uses information
from the front end, such as which records to read or what data to write.
In most cases the back end is a DBMS, but not always. Datastores like Microsoft Exchange can also
speak the SQL Language.
For most of these scenarios there are certain tasks that must be performed:
- user interface
- business logic (which may produce the SQL statement)
- data storage
- physical connections and
- logical connections
However, in each scenario these functions are divided differently across machines and locations.
Having a clear image in your mind of where various tasks are performed is particularly useful in
troubleshooting. It is useful to be able to follow mentally the path of information from the user all the
way through to the data store and back out again. Frequently the first step in solving problems is to
isolate which step in the process is creating the problem.
Access, as we have discussed, has objects that function as parts of a front end (forms and reports) and
table objects that function as a back end. In a sense Access query objects function like SQL statements,
as they are one way to communicate between the front and back end objects. In addition to the query
objects, any Access object property that accepts a SQL statement for a value is a SQL Requester, as is
Access VBA code that executes a SQL statement. Unlike the other scenarios, Access is able to perform a
connection internally between a form and a table without any external coding such as ADO or OLEDB.
Two Tier Architecture on a LAN
A very simple system for smaller offices involves just user's desktop PCs, a server and a Local Area
Network. The server performs the services of managing network traffic, security and holding the data.
For example an office of 20 people may have an Intel-based server running Windows 2000 Server and
SQL Server 2000. The front end could be an application created in Visual Basic, which runs on the
user's desktop. In response to the user entering some parameters and clicking on a button, the VB code
creates a request containing a SQL statement and sends it to the server via a connection such as ADO.
The server receives the request and routes it to the DBMS. The DBMS executes the statement and then
returns the data to the VB application for display.
Three Tier Architecture on a LAN
As the complexities and security requirements of a company increase the IT department will move to a
three (or more) tier architecture. The front end will still be the user's desktop PCs. But now the server
function has been divided into two (or more) parts.
- A data server that runs the DBMS or other data store.
- A departmental server that usually routes traffic, controls security and does some business processing.
One of those business processes is the generation of SQL statements to communicate with the data
server. Now, when the request is received by the departmental server it is sent on to the data server
through a physical link between the machines, most probably a 100 Mb or 1 Gb Ethernet LAN.
As we will see in an example on the Web below, the three-tier model can expand to many more levels,
called n-tier. Each additional machine is specialized to perform one part of the business process. Adding
to the base of a departmental server and a data server, common additional servers include a security
server and a server to handle the processing of the business logic.
Note that not all requests will be routed through all the servers. For example, a security server may
perform some initial authentication and then the actual SQL requests may then be permitted to flow
directly to the data server.
Two-Tier Architecture on the Web
Here we have the user's PC running a browser, which is connected via the web to a server. This is a
lightweight website because the web server, the script processor and the database software are all held
on one machine. Thus we have a two-tier system: PC/Browser and Web Server. In this case, the
connection is between the script interpreter and the database.
Note that the way we use the word "connection" in this scenario is not the connection from the
browser to the web site. In a two-tier system both the SQL requester and the datastore ends of the
connection are on the same machine. The connection here is only a logical channel through the
server's memory; there is no need for an external physical connection as in some other scenarios.
Three-Tier Architecture on the Web
As with a web server, the datastore may be on the same server or a separate machine(s). This
architecture is the logical next move when the two-tier system (above) becomes overloaded. Just as with
the 3-tier LAN system, we have moved the DBMS to another physical machine and have thus been able
to optimize the hardware and OS to support reliability, security and scaling of a data store. The web
server can also then be re-optimized for serving pages and executing scripts without having to
accommodate a data service.
The front end shifts further from the SQL requester in a three-tier system. The front end (point of user
interface) becomes the PC running a Browser. The SQL requester is the business logic software on the
web server, not the user's PC. The web server will then pass information back and forth to the end
n-Tier Architecture on the Web
An n-tier system has the server tasks divided among more servers than the three-tier system. Now we
have divided the web server into three parts, one to handle the web services, one to handle the sessions
and a third to perform the business logic (run the bulk of the scripts). Our connection now links the
business logic layer and the data store. As in every case in a "tiered" system (2-, 3-, and n-tier) the
connection runs between the data server (DBMS) and the next layer up.
(Click image for full size)
In the graphic below, the hardware and software is the same, but we see that more than one server in an
n-tier system can establish connections and pass SQL statements to the Data Server (DBMS). Here the
session server passes and receives data via SQL statements. Thus within one site we may have two or
more servers creating connections and passing SQL statements to a DBMS. We expand further on this
idea in the section titled Data Warehousing below.
(Click image for full size)
Mainframe and Terminals
A mainframe/terminal system resembles the two-tiered system described earlier. However, the
terminals and mainframe are usually in one facility and thus the connection is normally through
dedicated wiring and not through the Internet. Second, the terminal has a minimum of processing
power, so it is controlled by the mainframe. As of 2001, this system is mostly limited to older
installations or high security systems.
(Click image for full size)
Mainframe and Terminals with a Separate Datastore
A mainframe can split off the data server in the same way that our LAN and website examples did when
moving from two- to three-tier architectures. In this scenario we see that the mainframe handles the
business logic but for data it sends a SQL statement out to a data server running a DBMS. The
connection is from the business logic mainframe to the Data Server.
Mainframe to Mainframe
In each of the scenarios above there is a person involved. An employee (hopefully smiling and fully
vested) sits at the desktop PC or a customer (hopefully with a loose wallet) sits at the other end of the
Internet on a PC with a browser. But SQL can also handle communications about data between
machines with no person involved. In fact SQL is a common language to establish communication
between legacy systems with completely different hardware, operating systems and software (and
probably incompatible administrators to boot).
In this graphic we see that an IBM mainframe is able to get information on customers from a VAX
mainframe. Although there is a SQL Requester there is no front end because there are no humans