Microsoft SQL Server Architecture

• MS-SQL Server is a Client-Server Architecture.
• MS-SQL Server process starts with the client application sending a request, The SQL Server accepts, process and replies to the request.

SQL server Architecture contains the following major components they are

1. Protocol Layer.
2. Relational Engine.
3. Storage Engine.
4. Databases (Files).

Protocol Layer

This supports three type of client-server architecture 

        1. Shared Memory: SHARED MEMORY PROTOCOL 

• Here MS-SQL server provides and supports shared memory protocol here CLIENT and MS-SQL server are on the same machine.
• To get connected to the default instance on the machine we can use SSMS (SQL Server Management Studio) and to connect use any of the type in SSMS connection method ".", "localhost", "127.0.0.1", "Machine_name\Instance_name"

        2. TCP/IP: 

• The client and MS-SQL server is on different physical location and different machines, which are connected using TCP/IP protocol with 1433 port,
• The connection to the SQL server is made via SSMS and server name is "Machine_name\Instance_name"

        3. Named Pipes:

• The Client and MS-SQL server is on same physical location but different machines which are connected through LAN using intranet network.
• This option is disabled by default can be enabled by SQL Server configuration manager.

What is TDS?

• TDS is called Tabular Data Stream, all three protocols use TDS packets, TDS is encapsulated in network packets this enables data transfer from client machines to Server machine. 
• TDS was first developed by Sybase and is now owned by Microsoft.

Relational Engine

• This is also called as Query processor, it has some SQL server components that determine what the query needs to do and how it can be executed,
• Its responsible for the execution of user queries by requesting data from the storage engine and processing the results and returning.
• This Engine consists of three types of major components they are:

        1. Query/CMD Parser:

• Data/Query once received from protocol layer is then passed to relational engine,
• CMD parser is the first component of Relational engine,
• The main job of this component CMD parser is to check the query for Syntactic and Semantic Error and finally generates the Query Tree

1. Syntactic: Syntactic checking verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement(grammar check).
2. Semantic: This is performed by Normalizer, it checks whether Column name, Table name being queried exist in Schema. And if it exists, bind it to Query. This is also known as Binding.
3. Query Tree: This step generates different execution tree in which query can be run. Note that, all the different trees have the same desired output.

        2. Optimizer or planner:

• The work of the optimizer is to create an execution plan for the user's query. This is the plan that will determine how the user query will be executed.
• Optimization is done for DML commands like SELECT, INSERT, DELETE, and UPDATE. Such queries are first marked then send to the optimizer.
• DDL commands like CREATE and ALTER are not optimized, but they are instead compiled into an internal form,
• The query cost is calculated based on factors like CPU usage, Memory usage, and Input/ Output needs.

"Optimizer's role is to find the cheapest, not the best, cost-effective execution plan"

3. Query Executor:

• Query executor calls Access Method. It provides an execution plan for data fetching logic required for execution. 
• Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

Storage Engine

• The work of the Storage Engine is to store data in a storage system like Disk and retrieve the data when needed, Lets see how data stored in database, 
• Datafiles: 
• Primary files - Only one primary file per database, stores all important data related to tables, views, triggers, etc (.mdf extension)
• Secondary files - Database may or May not contain secondary files, its optional and contains user specific data, can contain one or more files (.ndf extension)
• Log files - also know as write ahead logs, used for transaction management (.ldf extension)
• Extent: These data pages are logically grouped together to form a extent, The maintenance of the object is done via extents; 8 Pages = 1 Extent
• Pages: Page is the smallest storage unit of SQL Server,
  The page has a section called the Page Header with a size of 96 bytes,
  Carrying the metadata information about the page like the Page Type, Page Number, Size of Used Space, Size of Free Space, and Pointer to the next page and previous page, etc; 1 Page = 8 KB 

• The main components of storage engine are:

        1. Access Methods:

• It acts as an interface between query executor and Buffer Manager/Transaction Logs.
• The first action is to get to know weather the query is SELECT Query(DDL) or NON SELECT Query(DML).
• If its a DDL the query is passed to buffer manager for further processing, if its a DML the query is passed to Transaction manager. 

2. Buffer manager:

• Plan cache - 
• Existing Query plan: Buffer manager checks if the execution plan for the query  is there in the stored plan cache or not if its present then that plan cache and its data cache is used.
• First Time Cache Plan: If execution plan for the query is not present in stored plan cache, the execution plan is stored in plan cache this is used for next time when the same query is fired.

• Data parsing: Buffer cache(Soft parsing) and Data Storage(Hard Parsing) -
• Buffer Cache (Soft Parsing): Buffer manager looks for data in buffer in data cache when the execution plan is fount in plan cache, the data is used by query executor, this improves the query performance when compared to data fetching form storage.
• Data Storage (Hard Parsing): If data is not present in Buffer Manager than required Data is searched in Data Storage. If also stores data in the data cache for future use.

• Dirty Page - 
• Pages which stores the data is also known as dirty pages.

3. Transaction manager:

• When access method determines the query fired is a non select query like DML's then those queries are routed to transaction manager.
• Log Manager - Log Manager keeps a track of all updates done, Logs have Logs Sequence Number with the Transaction ID and Data Modification Record,
  This is used for keeping track of Transaction Committed and Transaction Rollback.
• Lock Manager - During Transaction, the associated data in Data Storage is in the Lock state. This process is handled by Lock Manager.
  This process ensures data consistency and isolation. Also known as ACID properties.
• Execution Process - 
• Log Manager start logging and Lock Manager locks the associated data, 
• Data's copy is maintained in the Buffer cache, 
• Copy of data supposed to be updated is maintained in Log buffer and all the events updates data in Data buffer, 
• Pages which store the data is also known as Dirty Pages.
• Checkpoint and Write-Ahead Logging: This process run and mark all the page from Dirty Pages to Disk, but the page remains in the cache. Frequency is approximately 1 run per minute. But the page is first pushed to Data page of the log file from Buffer log. This is known as Write Ahead Logging.
• Lazy Writer: The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. It operates on LRU – Least recently used Algorithm for cleaning page from buffer pool to disk.