SQL Server Architecture Explained

What Is SQL Server?

SQL Server is a relational database management system (or RDBMS) developed by Microsoft. It was specially designed and set to compete with the MySQL and Oracle databases. SQL Server supports ANSI SQL, the standard SQL (Structured Query Language) language. However, SQL Server comes with its iteration of the SQL language, a Microsoft proprietary language called T-SQL (or Transact-SQL).

SQL Servers provide high-performance data storage. They manage massive data sets across every computer attached to a network. The chief difference between SQL Server and Windows SQL Server is their purpose. SQL Servers store raw data: Windows Servers let users store formatted data such as spreadsheets, projects, images, and Word documents. You can learn more about SQL by clicking here and watching the tutorial.

A relational database management system is a collection of capabilities and programs that help users create, administer, oversee, and interact with relational databases. Relational database management systems store the data in table form, most of them using SQL to work with the database.

Explaining SQL Server Architecture

Three primary components make up SQL Server architecture: Protocol Layer, Relational Engine, and Storage Engine. Here’s a diagram of Microsoft’s SQL Server architecture.

Now we turn our attention to the trio of primary SQL Server components. So brace yourself; we're going to get technical.

Protocol Layer

This layer supports three kinds of Client Server Architecture, plus a stream.

• Shared Memory. The client and SQL server run on the same machine and can communicate by a shared memory protocol.
• TCP/IP. This protocol allows the client and SQL server to interact even though they are installed on separate machines and are remote to each other.
• Named Pipes. This protocol lets the client and SQL server via a Local Area Network (LAN).
• TDS. All three protocols use Tabular Data Stream packets. These packets allow data transfers from the client machine to the server machine.

Relational Engine

It's also called the Query Processor, and it contains the SQL Server components that determine precisely what a query must do and how to best accomplish it. The relational engine executes user queries by requesting data from the storage engine and processing the returned results. The engine has three major components:

• CMD Parser: The CMD Parser’s chief purpose is to check the query for Semantic and Syntactic errors, then generate a Query Tree. The Parser is the first Relational Engine component to receive Query data.
• Optimizer: The Optimizer works on built-in exhaustive and heuristic algorithms to ultimately minimize query run times and create an execution plan. Note that the Optimizer finds the cheapest plan, not the best one.
• Query Executor: The Executor creates the data fetching logic’s execution plan. When the Executor receives the data from the Storage Engine, the result is published to the Protocol layer. After the results are published, the resulting data goes to the end-user.

Storage Engine

Storage Engine stores the data in a storage system such as SAN or a disk and retrieves it when needed.

• File Types. The Storage Engine has three files: the Primary, Secondary, and Log files.
• Access Method. This component interfaces between the query executor and the buffer manager and transaction logs.
• Buffer Manager. This component manages the core functions for the following three modules:
  • Plan Cache. The buffer manager looks for the existing execution plan stored in the Plan Cache.
  • Data Parsing. The buffer manager then provides access to the needed data.
  • Dirty Pages. These pages hold the Transaction Manager’s processing logic data.
• Transaction Manager. The Transaction Manager activates when there are Non-Select Transactions and manages these transactions using Log and Lock Managers.

SQL Server Version History and Editions

SQL Server has been around for over three decades and has been through a few versions.

• 1989. Microsoft and Sybase released version 1.0
• 1993. Microsoft and Sybase end their partnership, but Microsoft keeps SQL Server’s rights
• 1998. Microsoft releases SQL Server 7.0, a major rewrite
• 2000. Microsoft releases SQL Server 2000
• 2005. Microsoft releases SQL Server 2005
• 2008. Microsoft releases SQL Server 2008
•  2010. Microsoft releases SQL Server 2008 R2, adding new services and a master data management system
• 2012. Microsoft releases SQL Server 2012
• 2014. Microsoft releases SQL Server 2014
• 2016. Microsoft releases SQL Server 2016
• 2017. Microsoft releases SQL Server 2017, including Linux support
• 2019. Microsoft releases SQL Server 2019, introducing Big Data clusters

SQL Server comes in the following popular editions:

• SQL Server Enterprise. Designed for high-end, large-scale, mission-critical business operations. It offers advanced analytics, high-end security, and Machine Learning, to name a few
• SQL Server Standard. Best suited for mid-tier applications and data marts and includes basic reporting and analytics
• SQL Server WEB. Designed to give Web hosts a low total cost-of-ownership option. It offers affordability, scalability, and management capabilities for small to large-scale Web properties
• SQL Server Developer. Like the Enterprise edition but designed for non-production environments and chiefly used for builds, tests, and demos
• SQL Server Express. An open-source, entry-level offering designed for small-scale applications

MS SQL Server as Client-Server Architecture

The client is an application that gives requests to the MS SQL Server, which resides on a specific machine. The server can process input data based on the request. At last, the server responds with processed output data.

Therefore, it’s a simple matter to say that the client is the party making the request, and the server is the party that initiates the process that eventually meets that request.

The Components of SQL Server Architecture

MS SQL Server consists of a series of services and components:

• Database Engine: This component is responsible for storage, securing data, and rapid transaction processing.
• SQL Server: This service starts, stops, pauses, and continues Microsoft SQL Server instances (which we’ll cover later). Its executable name is sqlservr.exe.
• SQL Server Agent: This agent assumes the role of task scheduler and triggers either with any event or on-demand. Its executable name is sqlagent.exe.
• SQL Server Browser: This browser listens to incoming requests and connects them to the needed SQL server instance. Its executable name is sqlbrowser.exe.
• SQL Server Full-Text Search: This search allows users to run full-text queries against character data residing in SQL Tables. Its executable name is fdlauncher.exe.
• SQL Server VSS Writer: This component allows data file backup and restoration when the SQL server isn’t running. Its executable name is sqlwriter.exe.
• SQL Server Analysis Services (SSAS): This service provides data analysis, data mining, and Machine Learning functions. SQL Server is integrated with the R and Python programming languages for advanced analytics purposes. Its executable name is msmdsrv.exe.
• SQL Server Reporting Services (SSRS): This service provides reporting features and decision-making capabilities, including Hadoop integration. Its executable name is ReportingServicesService.exe.
• SQL Server Integration Services (SSIS): Finally, this service provides extract-transform and load capabilities of different data types between sources. In short, it converts raw information into useful information. Its executable name is MsDtsSrvr.exe.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

The Breakdown of SQL Server Instances and Their Importance

We mentioned instances before, so it's only fair to explain them. SQL Server lets users run multiple services simultaneously, each with its own databases, ports, logins, etc. Instances come in two types: primary and named.

The primary service is the default, while the remaining services are named. Users access the primary service either via its IP address or using the server’s name, while named services require appending a backslash and the instance name.

Here Are the Advantages of SQL Server Instances:

• You Can Install and Run Different Versions on One Machine

First, you can have various versions of SQL Server running on a single machine, and each installation works independently of the others.

• You Can Reduce Costs

Instances help reduce the costs of operating SQL Server, especially when you’re purchasing an SQL Server license. Users get different services from different instances, so there’s no need to buy one license for all services.

• You Can Maintain Development, Production, and Test Environments Separately

If you have many SQL Server instances on a single machine, you can assign different instances to development, production, and test tasks.

• You Can Reduce Temporary Database Problems

If you have all your services running on a single SQL Server instance, there’s a greater chance of recurring problems. But when your services run on different instances, you avoid these problems.

• You Can Separate Security Privileges

You can pay closer attention to securing an instance running the most sensitive service, even when you have different services running on other SQL Server instances.

• You Can Maintain a Standby Server

If a SQL Server instance fails, it can result in a service outage. But if you have a standby server, you’re covered if the current server goes down. You can easily achieve this service level by using SQL Server instances.

Uses of SQL Server

The modern world continues to generate new data at a breathtaking rate, so we need databases to hold the information and database management systems to help get the most use and value out of that information. As a database management system, SQL Server's services include:

• Creating databases
• Maintaining databases
• Analyzing data through SQL Server Analysis Services (SSAS)
• Creating reports with SQL Server Reporting Services (SSRS)
• Carrying out ETL operations (Extract, Transform, and Load) with SQL Server Integration Services (SSIS)

You Can Become a SQL Server Expert

Simplilearn offers aSQL Certification Course trains you to begin working with SQL databases and taking full advantage of databases and SQL Server architecture in your applications. You will learn how to correctly structure a database, author efficient SQL statements and clauses, and manage your organization's SQL database for scalable growth.

This course is the perfect resource for ambitious professionals who want to upskill, increase their value in their organization, or maybe even take the first few steps to a new career elsewhere. So check out Simplilearn today, and expand your future career prospects!