SQL Server Architecture Overview

MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts, processes and replies to the request with processed data. In this blog post we will see the overview of SQL Server Architecture.

The Major components of SQL Server are:

• Relational Engine

• Storage Engine

• SQL OS

• Protocol Layer

The fallowing architecture shows all components of SQL Server.

Relational Engine

• It is also called as the “Query Processor”, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it.

• It manages the execution of queries as it requests data from the storage engine and processes the results returned.

• This is where real execution will be done. It contains Query parser, Query optimizer and Query executor.

Different tasks of Relational Engine:

• Query Parser (Command Parser) and Compiler (Translator) : This will check syntax of the query and it will convert the query to machine language.

• Query Optimizer : It will prepare the execution plan as output by taking query, statistics and Algebrizer tree as input.

• Execution Plan : which contains the order of all the steps to be performed as part of the query execution.

• Query Executor : This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.

Storage Engine

Storage Engine is responsible for storage and retrieval of the data on to storage system (Disk, SAN etc.). Lets see more details of storage engine below:

• Buffer Manager

• Access Methods

• Transaction Services

• Locking Manager

• File Manager

Buffer Manager:

• The buffer manager manages the functions for reading data or index pages from the database disk files into the buffer cache and writing modified pages back to disk.

• A buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache is divided into 8-KB pages.

Access Methods:

• SQL doesn’t directly retrieve from disk, it makes requests to buffer manager which serves up the page in cache before rendering out.

• Each component maintains its respective on-disk data structures – rows of data or B-Tree indexes.

• The allocation operations code manages a collection of pages for each database and keeps track of which pages in the db have already been used, for what purpose and how much space is available on each page.

Transaction Services:

• Provides support for Atomicity, Consistency, Isolation and Durability .

• Write-ahead logging ensures that the record of each transaction’s changes is captured on disk in the transaction log before a transaction is acknowledged as committed.

• SQL Server supports two concurrency models :

  1. Optimistic Concurrency – provides consistent data by keeping older versions of rows with committed values in version store. Readers do not block writers and writers do not block readers. Writers do block writers.

  2. Pessimistic Concurrency – guarantees correctness and consistency by locking data so it cannot be changed.

Locking Manager:

• To manage all types locks.

• Acquires and releases various types of locks:

  1. Share locks – reading

  2. Exclusive locks – writing

  3. Intent locks – taken at a higher granularity to signal a potential "plan" to perform some operation

  4. Extent locks – for space allocation

• Manages compatibility between the lock types, resolves deadlocks and escalates locks.

• Controls table, page, and row locks as well as system data locks.

File Manager:

• File manager to manage the file allocation space and file growth operations.

• SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.

• Pre-size data and log files well ahead of time and plan for the growth.

• Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.

SQL OS

• This lies between the host machine (Windows OS) and SQL Server.

• All the activities performed on database engine are taken care of by SQL OS.

• SQL OS provides various operating system services, such as

  1. Memory Management deals with buffer pool

  2. Log buffer and deadlock detection using the blocking and locking structure.

Protocols Layer

• The Internet Protocol (IP) is the principal communications protocol in the Internet protocol suite for relaying datagrams across network boundaries.

• A protocol is some set of rules that connect to end points communication.

• Its routing function enables internetworking, and essentially establishes the Internet.

• To connect to SQL Server Database Engine you must have a network protocol enabled.

• Microsoft SQL Server can service requests on several protocols at the same time.

• Clients connect to SQL Server with a single protocol.

• If the client program does not know which protocol SQL Server is listening on, configure the client to sequentially try multiple protocols.

• Use SQL Server Configuration Manager to enable, disable, and configure network protocols.

MS SQL SERVER PROTOCOL LAYER supports 4 Type of Client Server Architecture.

1. Shared Memory

2. TCP/IP

3. Named Pipes

4. Virtual Interface Adapter (VIA)

Shared Memory

• Shared memory is the simplest protocol to use and has no configurable settings.

• Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity.

TCP/IP

• TCP/IP is a common protocol widely used over the Internet.

• It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems.

• TCP/IP includes standards for routing network traffic and offers advanced security features.

• It is the most popular protocol that is used in business today.

Named Pipes

• Named Pipes is a protocol developed for local area networks.

• A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other.

• The second process can be local (on the same computer as the first) or remote (on a networked computer).

VIA

• Virtual Interface Adapter (VIA) protocol works with VIA hardware.

• The VIA protocol is deprecated from SQL Server 2012 version.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .