Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Client-Server Architecture
3.
SQL Server Network Interface
3.1.
Shared Memory
3.2.
Named Pipe
3.3.
TCP/IP
3.4.
Tabular Data Stream
4.
Database Engine
4.1.
Relational Engine
4.1.1.
Optimiser
4.1.2.
Query Parser
4.1.3.
Query Executor
4.2.
Storage Engine
4.2.1.
Data File Types
5.
Frequently Asked Questions
5.1.
What is the task of Buffer Manager in the architecture of Microsoft SQL Server?
5.2.
What is the Transaction Manager's task in the Microsoft SQL Server architecture?
5.3.
How can we locate the architecture of our SQL Server?
6.
Conclusion
Last Updated: Mar 27, 2024

Architecture of Microsoft SQL

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

A system's architecture describes its structure in terms of its individually defined components and their interrelationships. Similarly, the architecture of Microsoft SQL tells about its significant features and their working.

This article will discuss the main concepts of the Client-server architecture of Microsoft SQL and its key elements.

Client-Server Architecture

Due to the client-server Database architecture of Microsoft SQL Server, the process usually starts with the client application sending a request to the server. From there, it proceeds as the SQL Server receives and processes the request before returning the desired result.
 

Let's use a diagram to describe the critical elements of the SQL database design. The following three stand out:

  • Network Interface for SQL Server (Protocol Layer)
  • Relational Engine
  • Storage Engine

                                           Client-Server Architecture of MS SQL

Let's take a closer look at each component now.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

SQL Server Network Interface

Let's start with the layer that enables the communication between Sql Server and external environments. Its name is SQL Server Network Interface, and SQL Server protocols are contained within. The principal protocols currently connected to SQL Server are as follows:

  • Shared memory
  • Named pipes
  • TCP/IP
  • TDS (Tabular Data Stream)

We might also discuss the Virtual Interface Adapter protocol (VIA). It is not included in the most recent versions of SQL Server because it is deprecated by Microsoft and needs the setup of additional hardware.

Let's now discover more about them in-depth for a better understanding of the architecture of Microsoft SQL.

Shared Memory

When both the client program and the SQL Server are running on the same system, shared memory is the most straightforward protocol and is used by default for local connections.

Named Pipe

If the client application and SQL Server are connected over a local area network(LAN), the named pipes protocol may be utilised. This protocol is by default turned off; however, you can enable it by using the SQL Configuration Manager. In SQL Server, the named pipes' default port is 445.

TCP/IP

TCP/IP is the primary protocol to establish remote connections to SQL Server utilising an IP address and a port number. It is beneficial when the SQL Server and the client application are installed on different workstations. Although SQL Server uses TCP port 1433 by default, it is advised to modify the port after installation.

Tabular Data Stream

Between client applications and SQL Server computers, requests and responses are transferred using the application-level protocol known as Tabular Data Stream. Usually, the client and server maintain a steady connection. TDS messages are used to communicate between the client application and the database server once the connection is made.

Database Engine

It is the central part of the SQL Server architecture and is used to store and process data.

The database engine in the architecture of Microsoft SQL consists of two main components:

  1. Relational Engine
  2. Storage Engine

The relational engine handles query processing, whereas the storage engine handles data storage and retrieval from storage systems. Let's now take a detailed look at the architecture of the SQL Server engines for the two engine types.

Relational Engine

Because it controls query execution as well as memory management, thread and task management, and buffer management, SQL Server's relational database engine is also referred to as the query processor. In a nutshell, it asks the storage engine for data, processes the information, and then sends the results back to the user.

The three main components of the Relational Engine are:

  1. Optimiser
  2. Query Parser
  3. Query Executor

Optimiser

The optimizer reduces the query's run-time as much as possible by creating the cheapest execution plan. Be aware that not all queries require optimization; in essence, optimization only applies to DML commands tagged for sending to the Optimizer, such as SELECT, INSERT, UPDATE, and DELETE. Their cost is calculated depending on input/output requirements, CPU and memory utilisation, and other factors.

Query Parser

The component that takes the query, examines it for syntactic and semantic issues and ultimately produces a query tree is the CMD Parser (sometimes referred to as the Query Parser).

The syntactic check evaluates the user's input query to see if it is written correctly in terms of syntax. The Parser returns an error message if the query does not adhere to SQL syntax.


At its most basic level, the semantic check determines whether the table and column in question are present in the database structure. The checked table is linked to the query if the check is successful. If the table or column is missing, the Parser gives an error. The query's complexity affects how complex the semantic check is.

                                        Phases of Query Parser

Query Executor

The Query Executor then calls the access method. Once the storage engine has obtained the necessary data, it is delivered to the user via the Server Network Interface at the protocol layer.

Storage Engine

The storage engine stores and retrieves actual data in response to user requests. According to the inquiry, a buffer manager and a transaction manager interact with data and log files.

Data File Types

Let's first take a quick look at data files in general before moving on to the types of Data Files stored in the architecture of Microsoft Server. Data files of various types are where SQL Server stores its data (such as user and system tables, indexes, and stored procedures) and SQL code (such as stored procedures, views, and functions). 

The smallest data storage unit in SQL Server, data files physically store data in pages with a size of 8KB each. A page has a page header that lists information about the page, such as the page type, page number, the amount of used and accessible space, a pointer to the previous and following pages, etc. A header is 96 bytes in size.

With eight pages per extent, data pages are logically arranged into extents.

                                           8 Pages in one extent

Three data files should be noted.

Primary Files

There is just one primary file per SQL Server database, which commonly ends in .mdf. All the essential information about database tables, views, triggers, and other objects is kept.

Secondary Files

Several secondary files may exist, or there may be none at all. This is an optional type with a .ndf extension and user-specific data.

Log Files

The handling of undesirable transactions takes place in log files with the .ldf extension. As part of database hardening, they can also be used to address potential vulnerabilities and put in place the appropriate security measures.

                                               Different types of files in SQL Database

Check out Microsoft Interview Experience to learn about their hiring process.

Frequently Asked Questions

What is the task of Buffer Manager in the architecture of Microsoft SQL Server?

The plan cache, data processing, and dirty pages are handled by the SQL Server Buffer Manager.

  • The Manager determines whether an execution plan is already saved in the Plan Cache.
  • We have an instance of soft parsing if an execution plan is stored in the Plan Cache.
  • Data must be collected from the data store in a hard parsing scenario if there is no execution plan in the Plan Cache.

What is the Transaction Manager's task in the Microsoft SQL Server architecture?

The Transaction Manager is called when a non-SELECT statement is present in the query. The Lock Manager and the Log Manager are used to managing the transaction. The former uses transaction logs to keep track of all system updates. The latter locks the relevant data during each transaction to ensure conformity to the ACID characteristics.

How can we locate the architecture of our SQL Server?

  • Access your SQL server.
  • The Microsoft SQL Server Management Studio should be opened.
  • Start->All programs->Microsoft SQL Server 2008 R2->SQL Server Management Studio.
  • With your user, log in.
  • Select "New Query" from the menu.
  • Run the following query:  SELECT SERVERPROPERTY(‘edition’).

Conclusion

This article discussed the architecture of Microsoft SQL Server. We discussed many vital concepts:

  • The Client-Server architecture of Microsoft SQL.
  • SQL SNI(Server Network Interface) and principal protocols.
  • The database engine of the architecture of Microsoft SQL Server and its main components.

 

We hope this blog increased your knowledge regarding the architecture of Microsoft SQL Server. We recommend you to visit our articles on different topics of Microsoft SQL, such as:

 

If you liked our article, do upvote our article and help other ninjas grow.  You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingSystem Design, and many more!

Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences, interview bundle, follow guided paths for placement preparations, and much more!!

We wish you Good Luck! Keep coding and keep reading Ninja!!

Previous article
How to install the Microsoft SQL server?
Next article
What is Microsoft SQL Management Studio?
Live masterclass