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.