Client Server Architecture and Database Servers

Concept

Client server architecture splits a system into a process that provides a service and processes that consume it. In a database system the database server owns the stored data, the catalog, the locks and the log; the client owns nothing but the question it wants answered and the answer it receives.

Architecture

          CLIENTS                          SERVER
  +-------------------+            +----------------------+
  | request           |  connect   | listener             |
  | driver / library  |==========> | session per client   |
  | receives rows     |<========== | parser + optimiser   |
  +-------------------+   results  | execution engine     |
                                   | buffer pool          |
  +-------------------+            | lock manager         |
  | another client    |==========> | transaction manager  |
  +-------------------+            | log writer           |
                                   +----------+-----------+
                                              |
                                       +------v------+
                                       | data files  |
                                       | log files   |
                                       +-------------+

What the server is responsible for

What the client is responsible for

• Opening a connection and authenticating.
• Sending a request and receiving results.
• Presenting the result to a person or passing it to other code.
• Deciding when a transaction begins and ends.

Anything a client enforces is a suggestion, not a rule. Client side validation improves the user experience; server side constraints are what actually protect the data. Say both halves in an interview.

Connections, sessions and pooling

WITHOUT POOLING              WITH POOLING

2,000 users                  2,000 users
2,000 connections            20 pooled connections
2,000 sessions of memory     20 sessions of memory
connect + authenticate       connections already open,
on every request             so no setup cost per request

Opening a connection is expensive: a network round trip,
authentication, and server memory allocated for the session.
Pooling removes that cost from every single request.

Two kinds of client server split

Example

"Total fees collected this month"

FAT CLIENT approach
  client asks for every payment row this month
  server sends 240,000 rows across the network
  client adds them up
  -> slow, and the network did most of the work

THIN CLIENT approach
  client asks the server for the total
  server reads the rows locally, adds them up
  server sends ONE number
  -> the rule: move the question to the data,
     not the data to the question

Common mistakes

• Validating only on the client. Any other client, or a direct connection, bypasses it entirely.
• Opening a connection per request and never closing it. The server hits its connection limit and refuses everyone, including the administrator.
• Pulling rows to the client to filter or aggregate them. Filter and aggregate where the data is.
• Assuming client server means exactly two machines. It is a role split, not a machine count. One machine can host both.

Exam and interview questions

1. Draw the client server database architecture and label the server side components.
2. List five responsibilities that must stay on the database server, with reasons.
3. What is a connection pool and which specific cost does it remove?
4. Differentiate a fat client from a thin client.
5. Why is client side validation insufficient for data integrity?

Practice

1. List four pieces of state the server keeps for one session.
2. An application opens a connection per request and closes none. Describe what fails first and what error users see.
3. Rewrite the fat client fee example as a single server side request and explain the network saving.

Conclusion

The server owns the data, the catalog, the locks, the log and the rules; the client owns the question and the presentation. Keep enforcement and aggregation on the server, pool the connections, and most client server problems never appear.