Tuesday, February 18, 2014

SQL Server Architecture

Alright, everyone's got their version of SQL Server Architecture. I've read a lot of stuff to understand what's the design n architecture of SQL. Below diagram puts together almost everything that there is in SQL Server.
I'll keep it short n crisp.


  1. External Protocols
  2. Database Engine
  3. SQLOS API
  4. SQL OS
1.      External Protocols: Every request to SQL comes from one the four protocols.
  •  Shared memory: Connects to SQL instance on the same machine.
  • TCP/IP: Connects to SQL Instance over the network - This is widely used protocol today.
  • VIA (Virtual Interface Adapter): Customized network and it varies from vendor to vendor. This is however deprecated.
  • Named Pipes: It is an IPC object more like a TCP socket, All that i understand with named pipes is that, it needs more traffic to transmit data than does TCP. Also Named Pipes is limited to local subnet.
2.      Database Engine: Database Engine has two parts:
  •  Storage Engine: As seen in the diagram, storage engine holds responsible for the integrity of the data and manages all data access, both through transaction-based commands and bulk operations such as bulk insert, and certain DBCC (Database Consistency Checker) commands.
  •    Relational Engine: Understands the user's query, checks for syntax and symantics of the query and requests the Storage engine for data to processes the user's query. Communication between the relational engine and the storage engine is generally in terms of OLE DB row sets.
3.      SQLOS API:  This sits in between Database Engine and SQLOS. It provides necessary Libraries and the environment to execute SQL Statements.

4.       SQLOS
The SQLOS layer handles activities that are normally considered to be operating system responsibilities, such as thread management (scheduling), synchronization primitives, deadlock detection, and memory management, including the buffer pool.




No comments:

Post a Comment

New Features in SQL server 2016

This summary is not available. Please click here to view the post.