SQL Server Architecture

MS SQL Server is a client-server based architecture and it starts the process with the client application which will be sending a request. The SQL Server accepts the request, processes it and replies to the request with processed data.

MS SQL Server architecture consists of mainly two components:

  • Database Engine
  • SQLOS

Database Engine:

This one is a core component in MS SQL Server architecture. There are two engine which are Relational Engine and Storage Engine

Relational Engine:

Relational engine process the queries while storage engine manage database files, indexes and files. Database engine also process triggers, views, stored procedures and tables.

Relational engine is also known as query processor which determine best way to execute different queries in MS SQL Server. It send the request with query to storage engine  and process the result based on inputs with queries. It also manages the memory

Different tasks are performed by database engine which are processing the queries with inputs, thread and memory management, distributed query processing, tasks and buffer management etc...

Storage Engine:

Disks and SAN are the main storage systems which are managed by Storage engine. Storage engine retrieve the data from storage systems by the help of relational engine which process the query with inputs.

SQLOS:

SQL Server Operating System which is known as SQLOS. This operating system is under both the engine which are relational and storage engines. SQL operating system provides services like synchronizations of different services, memory management, I/O management and exception handling.

Services and tools in SQL Server

In MS SQL Server, Microsoft provides different data management services and BI (Business Intelligence) tools together.

SSIS: SQL Server Integration Services

SQL Server Integration Services is also known as SSIS and it is a component of the Microsoft SQL Server database software which is used to perform a wide range of data migration tasks. It is a platform for the data integration and workflow applications. It provide a different features for a data warehousing tool used for data extraction, transformation, and loading.

DQS: SQL Server Data Quality Services

SQL Server Data Quality Services is a knowledge-driven data quality product, it help you to perform a critical tasks in SQL Server. DQS enables you to discover, build, and manage knowledge about your data. For information about installing DQS, visit Install Data Quality Services.

SQL Server Master Data Services

Microsoft SQL Server Master Data Services is a Master Data Management product from Microsoft that ships as a part of the Microsoft SQL Server relational database management system. It  provides a Web service interface to expose the data, as well as an API, which internally uses the exposed web services, exposing the feature set, programmatically, to access and manipulate the data.

SSDT: SQL Server Data tools

SQL Server Data Tools (SSDT) is a latest development tool which helps for building SQL Server relational databases, Analysis Services data models, databases in Azure SQL, Reporting Services and Integration Services packages. View more about it and Download SQL Server Data Tools (SSDT) for Visual Studio.

SSMS: SQL Server Management Studio

SQL Server Management Studio is an integrated development environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. It provides tools to configure, monitor, and administer objects/instances of databases and SQL Server. Download SSMS: Download SQL Server Management Studio.

SSAS: SQL Server Analysis Services

Analysis Services is an analytical data engine called "VertiPaq" which is used in decision support and business analytics. SQL Server Analysis Services provides enterprise-grade semantic data models for complex business reports and client applications. For example Power Builders(Power BI), Microsoft Excel, SQL Reporting Services reports, and other tools for the data visualization.

SSRS: SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is a software system which is server-based. It is part of a suite of Microsoft SQL Server services, including SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS). It is administered via a web based interface, and it helps to prepare and deliver a variety of interactive and printed reports.