Introduction to SQL Server Architecture

Introduction to SQL Server Architecture

Every day, we use a wide wide range of tools, gadgets, and devices. Every second, a tremendous amount of data is produced. As a result, we need databases to handle such data and this gives us the need to comprehend database management systems. MS SQL Server is one of the most well-known relational database management systems in the data world. This database management system employs a structure that enables users to locate and retrieve data in relation to other database items.

This article covers the fundamentals of the SQL Server architecture, as well as what SQL Server is, how it works, and more.

But let's start with the basics and establish some definitions.

Table of Contents

1. What is SQL Server?

2. Version History of SQL Server

3. SQL Server Architecture

• Protocol Layer
• Relational Engine
• Storage Engine

4. MS SQL Server as Client-Server Architecture

5. SQL Server Editions

6. Uses of SQL Server

1. What is SQL Server?

SQL Server is a relational database management system developed by Microsoft (RDBMS). It was designed mainly to compete with Oracle and MySQL databases. It enables a wide range of transaction processing, analytics, and business intelligence applications in corporate environments. One of the three market-dominating database technologies is Microsoft SQL Server, along with Oracle Database and IBM's DB2.

To manage databases and query the data they store, database administrators (DBAs) and other IT specialists use SQL, a Structured Query Language. Along with other RDBMS software, Microsoft SQL Server is built on SQL.

Transact-SQL (T-SQL), is a Microsoft implementation of SQL that enhances the standard language with a variety of proprietary programming extensions.

SQL Servers provide high-performance data storage. They manage massive data volumes on every system that is connected to a network. The primary difference between SQL Server and Windows SQL Server is what each is used for. Raw data is kept in SQL servers: Users can save prepared data such as spreadsheets, images, projects, and Word documents on Windows servers.

A collection of tools and programs is known as a relational database management system and it aids users in creating, maintaining, controlling, and communicating with relational databases. Tables are used to hold data in relational database management systems, and most of them work with the database using SQL.

2. Version History of SQL Server

• Microsoft and Sybase released version 1.0 in 1989.
• But the partnership between these two came to an end in the early 1990s.
• The copyright for SQL Server was still held by Microsoft.
• Since the 1990s, new releases of SQL Server have included versions 2000, 2005, 2008, 2012, 2014, 2016, 2017, 2019, and 2022.

Unlock the full potential of SQL Server development with our SQL Server Developer Training program.

3. SQL Server Architecture - How does it work?

On the server, SQL services are installed, and the database is located.

MS SQL Server is built on a client-server model. The MS SQL Server process begins when the client application submits a request. The request is then accepted, and after processing, the data is returned by the SQL Server. Let's go through each detail of the entire architecture of SQL Server displayed above.

The architecture of SQL Server is made up of three main components, which are the Protocol Layer, Relational Engine, and Storage Engine, as seen in the above diagram.

Let's take a closer look at each of the three essential components of SQL Server.

a) Protocol Layer

Firstly, let's talk about the layer that facilitates communication between SQL Server and outside environments i.e., the protocol layer (Server Network Interface ), which includes SQL Server protocols.

The following are the main protocols that are currently connected to SQL Server:

➔ Shared memory

➔ Named pipes

➔ TCP/IP

➔ TDS (Tabular Data Stream)

Further, another protocol named Virtual Interface Adapter (VIA) can be mentioned. Since it requires the deployment of additional hardware and is deprecated by Microsoft, now no longer supported in the latest versions of SQL Server. Let's now learn more in-depth about them.

➔ Shared Memory: A shared memory is the simplest protocol and is used by default for local connections when the client application and the SQL Server are installed on the same machine.

➔ Named Pipes: The named pipes protocol may be used if SQL Server and the client application are connected over a local area network (LAN). This protocol, which is by default deactivated, can be made active using the SQL Configuration Manager. In SQL Server, the named pipes' default port is 445.

➔ TCP/IP: The most crucial of the group, TCP/IP, is the primary protocol used to connect remotely to SQL Server using an IP address and a port number. This is required when the client application and SQL Server are installed on different workstations.

➔ TDS (Tabular Data Stream): The Tabular Data Stream application-level protocol is used to transmit requests and responses between client applications and SQL Server machines. Normally, a constant connection between the client and server is maintained. After establishing a connection, TDS messages are used to communicate between the client application and the database server. If a server chooses to act as a client, then it requires a separate TDS connection.

b) Relational Engine

Due to its role in memory management, thread, task, and buffer management, the relational database engine in SQL Server is also known as the "query processor." This is due to the fact that it manages buffers and threads in addition to controlling how queries are processed. To put it simply, it requests information from the storage engine, processes it, and then relays the outcomes to the user.

The three main components of the relational engine are

➔ Query Parser

➔ Query Optimizer

➔ Query Executor.

➔ CMD Parser (Query Parser)

The CMD Parser, often referred to as the Query Parser, is the component that accepts the query, examines it for syntactic and semantic errors, and ultimately produces a query tree.

In order to make sure that the user's input query is written correctly, the syntactic check is being used. Any time the query deviates from the SQL syntax, the Parser issues an error notice

The most basic semantic check examines to see whether the query's table and column exist in the database structure. If the check is successful, the query is connected to the table that was requested. If a table or column is missing, the Parser sends back an error message. The difficulty of the query determines the difficulty of the semantic check.

The last move of the parser is to create an execution tree for the query.

➔ Query Optimizer: The optimizer creates the shortest execution plan for the query in order to minimize the run-time of the query. Not all queries need to be optimized; in fact, only DML commands tagged for sending to the Optimizer, such as SELECT, INSERT, UPDATE, and DELETE, are subject to optimization. They are rated according to input/output needs, CPU, and memory usage, among other things.

The optimization process is divided into three phases.

Phase 0: Trivial Plan (a.k.a. pre-optimization)

Sometimes creating an optimized plan is not necessary, especially when the added cost would not reduce the run-time. Determining a simple plan is the Optimizer's first step. If none are accessible, the optimizer proceeds to the first step.

Phase 1: Transaction Processing Plan

Finding the best transaction processing plan is what this stage comprises. If one cannot be found, the optimizer searches for a more complex plan with many indexes per table. A simple plan with one index per table might be utilized as a starting point.

Phase 2: Parallel Processing & Optimization

If the aforementioned methods are unsuccessful in decreasing the query run-time, the Optimizer moves to parallel processing, which is reliant on the user's machine's configuration and processing capability. If it cannot be done, the final optimization begins. It looks for any alternative viable options in order to conduct the query as cheaply as possible.

➔ Query Executor

The Query Executor then calls the access method. At the protocol layer, the Server Network Interface is used to provide the needed data to the user after it has been retrieved from the storage engine. Everything is finished!

c) Storage Engine

In response to user requests, the storage engine stores and retrieves actual data. It also seems to interact with log files and data using a buffer manager and a transaction manager.

➔ Data File Types: Before going on to types, let's quickly review data files in general. SQL Server stores its data (such as user and system tables, indexes, and stored procedures) and SQL code in data files of various formats (such as stored procedures, views, and functions). Data files, the smallest data storage unit in SQL Server, actually contain data in pages that are 8KB in size. A page's header provides details about the page, including its kind, page number, and how much space is being used.

Data pages are logically organized into extents with eight pages each. There are three data files to be aware of.

Primary files

Each SQL Server database has one primary file, which often ends with .mdf. It contains all the necessary data about the database tables, views, triggers, and other objects.

Secondary files

There could be a lot of secondary files, or none at all. This type is optional and contains user-specific data and has a .ndf extension.

Log files

In log files with the.ldf extension, unwanted (and uncommitted) transactions are handled. They can also be used to address possible vulnerabilities and implement the necessary security measures as part of database hardening.

➔ Access Method

Now let's discuss the design of the SQL Server storage engine. We'll start by talking about the access method, which decides whether or not the user's query includes a SELECT statement. The query is sent to the buffer manager in the first case for processing. In the latter case, it is delivered to the Transaction Manager.

➔ Buffer Manager

The SQL Server Buffer Manager is in charge of the plan cache, data processing, and dirty pages.

➔ Plan Cache

The Manager first checks to see if the execution plan has been previously stored in the Plan Cache. If there is, it is used in conjunction with the pertinent data.

Keeping a query's execution plan in the Plan Cache increases the likelihood that it will be accessible sooner the next time your SQL Server receives the same query, so please be aware of this. This is particularly beneficial for tougher queries.

➔ Data Parsing

Through the buffer manager, access to the required data is made possible. If an execution plan exists in the Plan Cache, we have a case of soft parsing. Data that is stored in the Data Cache is used by the Query Executor. Performance is improved by using fewer I/O operations in this method.

When there isn't an execution plan in the Plan Cache, hard parsing takes place and data must be obtained from the data store.

➔ Transaction Manager

When the query contains a non-SELECT statement, the Transaction Manager is invoked. 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. In order to ensure compliance with the ACID characteristics, the latter locks the necessary data during each transaction.

4. MS SQL Server as Client-Server Architecture

The client is an application that sends requests to the MS SQL Server on a specific system. The server then processes input data depending on the request. After processing the output, the server finally returns the data.

This makes it simple to say that the client is the party making the request and the server is the party beginning the procedure that will finally fulfill it.

5. SQL Server Editions

The most popular SQL server editions/types are listed below:

• SQL Server Enterprise: The high-end, massive, and mission-critical firm uses SQL Server Enterprise. High-end security, advanced analytics, machine learning, etc. are all supported.
• SQL Server Standard: For mid-tier applications and data marts, SQL Server Standard is appropriate. Basic reporting and analytics are part of it.
• SQL Server WEB: Web hosts can use SQL Server WEB because of its low total cost of ownership. For small to large size web properties, it offers scalability, affordability, and manageability features.
• SQL Server Developer: For the non-production environment, it is comparable to an enterprise edition. Build, test, and demo are its primary uses.
• SQL Server Express: It’s free to use and designed for small-scale applications.

6. Uses of SQL Server

Since the modern world keeps producing new data at an astounding rate, we need databases to store the information and database management systems to help in maximizing the use and value of that information. As a database management system, SQL Server offers the following services:

• Creating databases
• Maintaining databases
• Analyzing data using SQL Server Analysis Services (SSAS)
• Creating reports via SQL Server Reporting Services (SSRS)
• Executing ETL (Extract, Transform, and Load) activities with SQL Server Integration Services (SSIS)

Conclusion: Data utilization has rapidly expanded, and the SQL server is essential for managing the rising data volumes. SQL can be used to access the data in Relational Databases. Companies have understood how important working with data is, and there is a great demand for SQL developers. In this article, we've covered all the details you need to know about SQL Server Architecture.

To get certified in the language and start a prosperous career, check out our SQL Server Developer Training. Happy Learning!