MS SQL Server Architecture 3 Layers – in a simple way

What is SQL Server?

SQL Server, also commonly referred to as MSSQL Server, is a relational database management system (RDBMS) developed and sold by Microsoft.

Here’s a breakdown of what that means:

• Relational database: This type of database organizes data in tables with rows and columns, allowing for relationships to be established between different tables.
• Management system: SQL Server provides tools and functionalities to manage these databases, including creating, storing, retrieving, manipulating, and securing data.

Essentially, SQL Server acts as a central hub for storing and managing structured data that can be accessed and used by various applications and software programs. It’s a popular choice for businesses of all sizes due to its reliability, scalability, and robust feature set. (MS SQL Server Architecture)

Read More: What is MS SQL SERVER?

SQL Server Version History and Editions

SQL Server has a rich history dating back to the late 1980s. Here are some key milestones:

• 1989: First version released for OS/2.
• 1993: Transitioned to Windows NT as the primary platform.
• 2000: Introduced important features like XML support and self-tuning.
• 2005: Significant performance improvements and integration with the .NET framework.
• 2008: Introduced In-Memory OLTP and introduced “R2” service pack model.
• 2012: Introduced columnstore indexes and enhanced security features.
• 2014: Focus on cloud integration with Azure and in-memory enhancements.
• 2016: Last version with service packs, focused on performance and security.
• 2017: Introduced container support and improvements in machine learning.
• 2019: Added big data clusters and introduced a lightweight “Express LocalDB” edition.
• 2022: Latest version with a focus on hybrid cloud, performance, and security.

SQL Server Editions:

SQL Server offers various editions with different features and functionalities to cater to diverse user needs. Here are the main ones:

• Express: Free, lightweight edition ideal for development and small applications.
• Standard: Good for general-purpose use, with features like replication and high availability.
• Enterprise: The most comprehensive edition, offering the highest performance, scalability, and advanced features like In-Memory OLTP.
• Developer: Free edition with most features of Enterprise, intended for development and testing purposes.
• Web: Free edition specifically designed for web applications with limitations on storage and processing power.
• Express LocalDB: Ultra-lightweight version that runs in user mode, ideal for local development without installation.

The Components of SQL Server Architecture

MS SQL Server is a client-server-based architecture. When a user sends a request, the MS SQL Server process starts with this request.
The MS SQL Server accepts this request, processes it, and replies to the requester with processed data.

As the diagram shows there are 3 major components in MS SQL Server Architecture:

1) Protocol Layer
2) Relational Engine
3) Storage Engine

1) Protocol Layer

The protocol layer in SQL Server acts as a translator and messenger, facilitating communication between client applications and the database server itself. It’s responsible for several key functions:

a) Establishing Connections

• Clients initiate connections by specifying the server address and protocol.
• The protocol layer negotiates and establishes the connection based on supported protocols and security configurations.

b) Data Transfer

• Clients send Transact-SQL (T-SQL) statements and other data to the server through the chosen protocol.
• The protocol layer packages the data into Tabular Data Stream (TDS) packets for efficient transmission.
• It also receives responses and result sets from the server and forwards them to the client application.

c) Supported Protocols

• Shared Memory: Used for local communication when both client and server reside on the same machine. It offers the fastest performance but lacks security and is not suitable for remote connections.
• Named Pipes: Enables communication over local area networks (LANs) using named pipes created on the server. It offers a balance of performance and security for LAN environments.
• TCP/IP: The most versatile protocol, allowing communication over any network using the TCP/IP protocol suite. It supports secure connections with Transport Layer Security (TLS) encryption.

d) Security Handling

• The protocol layer can enforce security measures like user authentication and authorization.
• It can also manage encryption using TLS to protect data confidentiality and integrity during transmission. (MS SQL Server Architecture)

e) Stream Handling

Besides T-SQL statements, the protocol layer can handle data streams for bulk inserts, backups, and restores. In summary, the protocol layer plays a crucial role in ensuring smooth and secure communication between client applications and SQL Server. By understanding its different components and functionalities, you can make informed decisions regarding connection protocols, security configurations, and overall database performance.

Protocol Layer Components

SQL Server Network Interface (SNI)

MS SQL Server Protocol Layer supports 3 – types of client-server architecture

a) Shared Memory

In shared memory protocol client and MS SQL Server run on the same machine. Both communicate via shared memory protocol.

Example: For practice purposes, you install the MS SQL Server on your laptop and access it with the SSMS. Here SSMS is your client which accesses MS SQL Server and both use your Laptop’s memory.

b) TCP/IP

In TCP/IP architecture, the client and MS SQL Server are remote to each other and installed on a separate host. And they interact via TCP/IP protocol.

MS SQL Server uses Port No. 1433 in TCP/IP for communication

Example: You are working from home, and you access your organization’s MS SQL Server via the internet with your laptop. Here your laptop and MS SQL Server connect and communicate with the TCP/IP protocol.

c) Named Pipes

In Named Pipes, the client and MS SQL Server are in connection via LAN (Local Area Network). Client and MS SQL Server communicate with the ‘Named Pipes‘ protocol. This option is ‘Disabled‘ by default and needs to be enabled by MS SQL Configuration Manager.

d) TDS: Tabular Datastream

• All 3 protocols use TDS packets. TDS is encapsulated in Network packets. This enables data transfer from the client machine to the server machine.
• TDS was first developed by Sybase and is now owned by Microsoft. (MS SQL Server Architecture)

Read More: Understanding the Different Status of SQL Database

2) Relational Engine

In SQL Server, the Relational Engine, also known as the ‘Query Processor‘, is the brain behind processing and executing user queries. It’s responsible for understanding what the user wants and then efficiently retrieving the relevant data from the database. Here’s a breakdown of its key functions:

a) Query Parsing and Compilation

• Receives user queries written in Transact-SQL (T-SQL) language.
• Parses the query to check for syntax and semantic errors.
• Converts the T-SQL statement into an internal representation, essentially a tree structure, for efficient processing.

b) Query Optimization

Analyze the query and choose the most efficient execution plan to retrieve the desired data. This involves considering factors like:

• Table sizes and relationships
• Available indexes
• Join methods
• Filtering conditions
• Cost estimation for different execution plans

c) Data Retrieval

• Based on the chosen plan, instruct the Storage Engine to access the relevant data pages on disk or in memory.
• Filters and processes the retrieved data according to the query’s WHERE clause and other conditions.

d) Result Assembly and Formatting

• Combines the processed data from potentially multiple tables into a single result set.
• Formats the result set according to the query’s SELECT clause, choosing the desired columns and applying any necessary formatting or calculations.

e) Returning Results

• Sends the final result back to the client application that submitted the query. (MS SQL Server Architecture)

It is also called ‘SQL OS’ ==> Interview question

Major components of the Relational Engine

a) CMD Parser

It is the first component of the Relation Engine to receive the query from the Protocol Layer. The principal job of the CMD Parser is to check the query for Syntactic and Semantic errors.

b) Syntactic Check

It will check the syntax of the query and language rule. If the query does not follow the rules or has the wrong syntax then it returns an error.

c) Semantic Check

This is performed by Normalizer. It checks whether the column name and table name being queried exist in the schema. If exists, it will bind it. This is called ‘Binding‘.

d) Create a Query Tree

This step generates different execution trees in which queries can be run.

e) Optimizer

It will create an execution plan for the query. The execution plan will determine how the query will execute. All queries are not optimized. Optimization is done for DML (Data Modification Language) like SELECT, INSERT, DELETE, and UPDATE. DDL (Data Definition Language) is not optimized but instead compiled into an internal form.

The query ‘COST’ is calculated based on factors like CPU usage, memory usage, and I/O needs. The optimizer’s role is to find the cheapest, not the best cost-effective execution plan. The goal is to minimize query run time. (MS SQL Server Architecture)

f) Query Executor

The Query Executor calls ‘Access Method‘. It provides an execution plan for data fetching logic required for execution. Once data is received from the Storage Engine, the result gets published to the protocol layer.

3) Storage Engine

The work of a Storage Engine is to store data in a storage system like a disk or SAN and retrieve data when needed.

Data files physically store data in the data pages. Each data page has a size of 8 KB, forming the smallest storage unit in the MS SQL Server.

• These data pages are logically grouped in the extents.
• The total size of extents is 64 KB (8 pages).
• The maintenance of the objects is done via extent.

Storage Engine has 3 Components

a) Access Method

The Access Method acts as an interface between the query executor and buffer manager/Transaction logs.

• The Access Method itself does not do any execution.
• The first action is to determine whether the query is:
• SELECT statement (DDL)
• Non-Select statement (DDL & DML)
• Depending upon the result, the Access Method takes the following steps:

1) If the query is DDL, SELECT statement, the query will be passed to the ‘Buffer Manager’.

2) If the query is DDL, Non-select statement, the query will be passed to the ‘Transaction Manager’. (MS SQL Server Architecture)

b) Buffer Manager

The core function of Buffer Manager is to manage the plan cache and data parsing.

c) Plan Cache

The Buffer Manager checks if the execution plan is there stored in the plan cache. If yes, then the query plan cache and its associate data cache are used.

If the first-time query is executing and it is complex, then it will be stored in the Plan Cache.

1) Data Parsing

The Buffer Manager provides access to the data required. There are 2 approaches possible depending on whether data exists in the data cache or not.

2) Soft Parsing

Buffer Manager looks for data in the buffer in ‘Data Cache’. If present, then this data is used in the query. This process is called ‘Soft Parsing’ because here there is no need to fetch data from the ‘Disk’ as data is available in the Buffer.

3) Hard Parsing

If data is not present in the Buffer, then required data search in data storage (Disk) and fetched from it and also stored in the data cache for future is. This process is called ‘Hard Parsing’.

4) Transaction Manager

The Transaction Manager is invoked when the Access Method determines the requested query is a Non-Select statement.

There are 2 components of the Transaction Manager

1) Log Manager

It keeps track of all updates done in the system via logs in the transaction log. Logs have a ‘Logs Sequence Number‘ (LSN) with ‘Transaction ID‘ and the ‘Data Modification Record‘. The log manager keeps track of ‘Transaction Committed‘ and ‘Transaction Rollback‘.

2) Lock Manager

During transactions, the associated data in data storage is locked by the Lock Manager. This process ensures ‘Data Consistency‘ and ‘Isolation‘.

Execution Process

When a query gets executed, the log manager starts logging and the lock manager locks the associate data.

1. Data’s copy is maintained in the buffer cache.
2. Pages that store the data are known as ‘Dirty Pages‘.
3. Checkpoint. This process runs and marks all pages from dirty pages to disk, but the page remains in the cache. Frequency is approx. 1 run per minute.
4. The page is first pushed to the data page of the log file from the buffer log known as ‘Write Ahead Logging’.
5. ‘Lazy Writer‘ The dirty page can remain in memory when MS SQL Server observes a huge load and buffer memory is needed for new transactions. It frees up dirty pages from the cache using the LRU (Least Recently Used) algorithm. (MS SQL Server Architecture)

---