Multi-Tier Architecture & Its Layers: Introduction to SQL

In this architecture of SQL server, it is possible to keep the UI-centric processing on a computer, near the client. In addition, database developer may keep the data-centric processing components on another computer near the database server, so that you get significant performance benefits.

Three-tier architecture is used till the manageable size of business tier. As the business complexities increases, the business tier became larger and unmanageable. This led to the evolution of N-tier architecture, where the business services model was divided into smaller manageable units. N-tier architecture is also called as multi-tier architecture.

The N-tier architecture consists of the following layers:

• Client tier
• UI-centric processing components
• Data-centric processing objects
• Database server

A banking application, when further expanded, can depict an example of n-tier architecture. The client tier would deal with the user interface, which would include the user interface controls, such as forms, menus, and toolbars. The server tier would comprise data-handling including saving data to the database server.

The business logic would include the rules and guidelines for different types of accounts, Interest rates, fixed deposits, ATMs, and overdrafts. All these would form the middle tier. However, there would be some rules that need to be implemented on the user interface and on the database. You can place these rules either on the UI-centric processing components of data-centric processing components, based on the functionality.

Applications that follow multi-tier architecture can be used across various locations. For Example, in Web applications, the application is stored on the Web server. The clients access the application from any location through a browser software. The clients make requests and receive response from the Web server.


The Web server transfers the request for data to a database server, as shown in the following figure.

In above diagram, all the computers are web clients, remains are specified. Depending on the relevance of the business rules, they can be implemented on any of the tiers, such as the Web clients, Web server, or the database server.

To provide support to applications where users can send requests simultaneously, the database server needs to be a fast, reliable, and secure. SQL Server 2005 is one such comprehensive database platform that provides a fast, reliable, and secure RDBMS. It also helps in data analysis with integrated tools.

Here BI application is an application that is used by the top management of an organization for data analysis to make future decisions. BI tools are the tools that help in creating reports that enable data analysis.

SQL Server Components

Database Architectures used by Database Developer: Introduction to SQL

According to earlier discussion about employee’s details storage in database, any business application can have some major elements as Single-tier Architecture, Two-tier architecture, Three-tier Architecture and at the last N-tier Architecture in context of SQL server. A database developer can easily use this concepts in sql by following below description.

Single-Tier Architecture

In single-tier architecture, all elements of a business application are combined as a single executable unit. This unit is installed on all the computers that the users need to work on. It is required to recompile and redistribute the entire application to all the computers, after any modification. That’s why it becomes complicated to modify the application or to fix any bugs.

Two-Tier Architecture

This architecture makes entry to address the problems in modifying an application faced in a first scenario. In two-tier architecture, the application is divided into two manageable parts; one part handles the data, while the other provides the user interface. The two parts can be located on a single computer or on separate computers over a network.

The part that handles the user interface (UI) is called the client tier. The part that Implements the application logic and manages the input data based on the business rules is called the server tier, as shown in the following connection.

Client Computers<---------------------------------->Database Server

Two-tier architecture is also called client-server architecture. Most RDBMSs, such as Microsoft Access, SQL Server, and Oracle adhere to the client-server architecture. RDBMS provides centralized functionality required while supporting many users.

Three-Tier Architecture

When implementing complex business solutions in case of a two-tier architecture, the tier on which the business logic is implemented becomes over loaded. As a result, it takes more time to execute. Therefore, to provide further flexibility, the two-tier architecture can be split into three tiers. In three-tier architecture, the first tier is the client tier, the second or middle tier is called the business tier and the third tier is called the server tier. The server tier contains a database server that manages the data.

Client Computers<-------->Business Tier <-------->Database Server

The business tier consists of all the business rules. It consists of the application logic that Implements rules and checks the data. The advantage of a three-tier application is that it allows you to change the business rules without affecting the other two tiers.

For instance in a banking application for loans, the user tier is the front-end, used by the customer to specify the loan details. The server tier can consist of an RDBMS in which the data is stored. The business tier lies between the other two tiers and consists of business rules, such as the loan limit and the interest rate charged to a customer. If there is a change in the rate of interest, only the middle tier component needs to be modified.

N-Tier Architecture