SQL Server Systems

3 4

A SQL Server system can be implemented as a client/server system or as a stand-alone desktop system. The type of system you design will depend on the number of users that will be accessing the database simultaneously and on the kind of work they will be doing. We'll examine both types of systems in this section.

Client/Server System

The client/server system can have either a two-tier setup or a three-tier setup. Regardless of the setup, the SQL Server software and databases reside on a central computer called the database server. Users have separate computers called clients. Users access the database server through applications on their client computers (in a two-tier system) or through applications running on a separate computer known as the application server (in a three-tier system).

Specifically, in two-tier systems, the clients run an application that accesses the database server directly via the network. The client therefore runs the business logic code and the code for presenting the results to the user. This type of client is known as a thick client because it performs these two operations. Figure 1-1 shows an example of a two-tier system. A two-tier setup can be useful when there is a relatively small number of users because each user connection requires system resources such as memory and locks. As a larger number of users connect, the system performance will degrade because of resource contention, in which case you might want to consider a three-tier solution.

Figure 1-1. A two-tier client/server system.

As mentioned, a three-tier setup involves a third computer called the application server. In this type of system, the client computer's only tasks are running the code to request functions from the application server and then displaying the results. This type of client is called a thin client. The application server runs an application to perform the business logic; this application is multithreaded, meaning many users can access it simultaneously. The application server makes connections to the database server to access data and returns the results to the client. An example of this type of system is shown in Figure 1-2.

The advantage of a three-tier system is that you can allow the application server to organize all client connections to the database server, instead of letting each client make its own connection, which wastes resources on the database server. This concept is called connection pooling. Connection pooling means that client requests are put into a pool (literally, a queue) to wait for the next available connection. As a connection becomes free, it can be used for the next request in the queue. In a sense, connection pooling allows you to throttle the amount of work being done on the database server by configuring the number of connections that are in the pool and thus the number available to perform user work. (The number of connections is configurable through software.) It eliminates the need for a large number of user connections that can quickly use up resources and cause slow performance. You can implement connection pooling by using Microsoft Internet Information Server and connection-pooling software such as COM+, which is a component service provided with the Microsoft Windows 2000 operating system. We will not get into the details of how to use these products, because application programming is beyond the scope of this book.

Some enterprise systems and Web sites need more processing power than that provided by just one server. SQL Server 2000 provides the ability to partition tables across multiple servers, which share the data-processing load. See the section "Distributed Partitioned Views" later in this chapter for more information.

Figure 1-2. A three-tier client/server system.

Desktop System

SQL Server can also be used as a stand-alone database server that runs on a desktop computer or a laptop. This is referred to as a desktop system. The client applications run on the same computer that stores the SQL Server engine and the databases. Only one computer is involved in this system. Therefore, no network connection is made from client to server; the client makes a local connection to its local installation of SQL Server.

The desktop system is useful in cases in which a single user accesses the database or in which a few users who share a computer access the database at different times. For example, in a small store with one computer, several employees might access the store's database to insert customer and sales information, but they must take turns accessing the system. Also, as in this example, desktop systems are useful when the database or databases are small.