Components of SQL Server

  • Database Engine: Core service for storing, processing, and securing data.
  • sqlservr: starts, stops, pauses, and continues an instance of Microsoft SQL Server from a command prompt. The executable name is sqlservr.exe.
  • SQL Server Agent: It works as task scheduler. The executable name is sqlagent.exe.
  • SQL Server Browser: It listens to the incoming connection requests and connects to desired SQL Server instance. This is especially helpful when more than one SQL Server instance is installed on the same machine. The executable name is sqlbrowser.exe
  • SQL Server Full Text Search: lets users and applications run full-text queries against character-based data in SQL Server tables.
  • SQL Server Writer Service: This allows backup and restore of data files through the Volume Shadow Copy Service (VSS) framework.
  • SQL Server Analysis Service (SSAS): Provides data analysis, data mining and data machine learning capabilities. SQL Server is integrated with R and Python language for advanced analytics. The executable name is msmdsrv.exe.
  • SQL Server Reporting Service (SSRS): Provides reporting features and decision making capabilities. It includes integration with Hadoop. The executable name is ReportingServicesService.exe.
  • SQL Server Integration Service (SSIS): Provides ETL (Extract, Transform and Load) capabilities for different data types from source to target. The executable name is MsDtsSrvr.exe.

SQL Server Instances

  • Primary Instance: This is the default instance. Only 1 default instance can be there.
  • Named Instance: Up to 50 instances can run concurrently on same server.

Each instances run independent of each other. This allows you to have different versions of SQL Server on the same machine.

This can also reduce overall hardware cost since you can have multiple instances on same machine acting as development, test and production environments.

SQL Server instances can act as standby for disaster recovery situations.

Connection Protocols

The protocol layer (SNI) supports three types of protocols:

  • Shared Memory: Client and Server are on the same machine.
  • TCP/IP: Client communicates to Server using TCP/IP protocol.
  • Named Pipes: Client and Server are connected to each other via same LAN. Communication happens through intra network. Note that connection via named pipes is disabled by default and needs to be enabled via SQL Configuration Manager.

Tabular Data Stream

Commonly referred as TDS, Tabular Data Stream packets are used by all three connection protocols discussed above. It is encapsulated in network packets facilitating data transfer from client machine to server machine.

Relational Engine a.k.a Query Processor

Relation Engine is the query processor component of SQL Server. It has three main components as listed below:

  • CMD Processor: It checks query for syntactic and semantic errors.
  • Optimizer: It is responsible to find the cheapest and cost effective execution plan for the query.
  • Query Executor: It provides execution plan for data fetching logic required for execution. It is responsible for calling Access Method.

Storage Engine

It is responsible for storing data in the disk and retrieve when needed. Smallest storage unit in SQL Server is data page with a size of 8 KB. These data pages are logically grouped to form extents.
The physical storage unit is called data files which physically stores data in form of data pages.

There are three types of data files:

  • Primary File: Every database has one primary file that stores important data about tables, triggers etc. It usually has extention .mdf, although you can choose a different extension if required.
  • Secondary File: This contains user specific data. The extension usually is .ndf
  • Log File: Also known as write ahead logs has extension .ldf. This is used for transaction management, rollback of uncommitted transactions and point in time recovery.

Components of Storage Engine

  1. Access Method
  2. Buffer Manager
  3. Transaction Manager

Access Method acts as an interface between query executor and Buffer Manager/Transaction Manager. It checks if the query is a SELECT (DDL) statement or a NON-SELECT (DDL/DML) statement. A SELECT statement is sent to Buffer Manager for processing. A NON-SELECT statement, usually an update statement, is sent to Transaction Manager for processing.