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:
- Relational Engine
- 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:
- Optimiser
- Query Parser
- 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.
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.
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.
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 Algorithms, Competitive Programming, System 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!!
8+ registered 