by In this lesson, we would examine the architecture of SQL Server. The three components of the SQL Server architecture would be covered:
- Protocol Layer
- Relational Engine
- Storage Engine
The complete architecture is presented in Figure 1 below:
1. The Protocol Layer
This Layer specified the communication between the client and the database server. MS SQL server support three types of client-server protocols
- Shared Memory
- Named Pipes
- TCP/IP
These can be see in the SQL Server configuration manager windows shown below:
Shared Memory: For this protocol, both the client and the server runs on the same machine
Named Pipes: Here, the client and the server reside in the same local network.
TCP/IP: Here, the client and the server communicate via TCP/IP protocol. This could be over the internet or on a local network. The client and the server could be on different machines or locations.
2. Relational Database Engine
This is also known as the Query Processor or simply the Relational Engine (Relational Layer in Figure 1).
This component determines what operations would be executed by a query. It also handle how to improve performance of the query. When necessary, it request data from the data storage engine, processes and sends back the result to the client.
There are three components here:
CMD Parser – This is the first component that receives the query. It checks to make sure that the query is correct (check error in syntax and semantics). It then generates the syntax tree.
Query Optimizer – This creates and execution plan for the query. The execution plan specifies how the query would be executed.
Query Executor – This actually executes the query by first calling the Access Method and providing an execution plan for fetching data.
3. Storage Engine
This is core storage area of the architecture. The function of the Storage Engine is to store data in some storage. For instance local disk or SAN. It then provides the data when required. The types of files stored by the storage engine includes transaction log files (.ldf ) and data files(mdf). These are explained below:
Data Files (.mdf): This is also called the Primary File. This is the file that stores the database objects: tables, views, stored procedures etc. It normally has the extension .mdf.
Transaction Log Files(.ldf): These files are used for transaction management. They help to recover the database in case of failure. Transaction logs are also called write-ahead logs.
Secondary Files (.ndf): This is an optional file that holds user-specific data. They normally have the extension .ndf.
Let’s now talk about two more components: Transaction Manager and Buffer Manager
Transaction Manager manages non-select transaction with the help of log manager and lock manager. Once the access method determines that the query is non-select, the Transaction Manager is invoked.
Buffer Manager manages functions such as: Plan Cache, Data Parsing and Dirty Page.
