SQL Server Architecture Explained: Named Pipes, Optimizer, Buffer Manager

[vc_row][vc_column][vc_column_text]MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts, processes and replies to the request with processed data. Let’s discuss in detail the entire architecture shown below:

As the below Diagram depicts there are three major components in SQL Server Architecture:

  1. Protocol Layer
  2. Relational Engine
  3. Storage Engine
SQL Server Architecture Diagram

Let’s discuss in detail about all the three above major modules. In this tutorial, you will learn.

Protocol Layer – SNI

MS SQL SERVER PROTOCOL LAYER supports 3 Type of Client Server Architecture. We will start with “Three Type of Client Server Architecture” which MS SQL Server supports.

Shared Memory

Let’s reconsider an early morning Conversation scenario.

MOM and TOM – Here Tom and his Mom, were at the same logical place, i.e. at their home. Tom was able to ask for Coffee and Mom was able it serve it hot.

MS SQL SERVER – Here MS SQL server provides SHARED MEMORY PROTOCOL. Here CLIENT and MS SQL server run on the same machine. Both can communicate via Shared Memory protocol.

Analogy: Lets map entities in the above two scenarios. We can easily map Tom to Client, Mom to SQL server, Home to Machine, and Verbal Communication to Shared Memory Protocol.

From the desk of configuration and installation:

For Connection to Local DB – In SQL Management Studio, “Server Name” Option could be