• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/SQL Server Architecture
Popular Search:Oracle, SQL Server, MongoDB

SQL Server Architecture

595 views 0 August 30, 2019 June 5, 2020 admin

SERVER ARCHITECTURE IS CATEGORIZED INTO THREE PARTS.

1. NETWORK PROTOCOLS
2. DATABASE ENGINE
a. QUERY PROCESSING ENGINE
b. STORAGE ENGINE
3. SQLOS

1. NETWORK PROTOCOLS : THESE ARE SOFTWARE PROGRAMS USED TO COMMUNICATE BETWEEN CLIENT (SSMS TOOL) & SERVER (SQL SERVER INSTANCE)
***** * TCP : TRANSMISSION CONTROL PROTOCOL OR INTERNET PROTOCOL
USED FOR CLIENT – SERVER COMMUNICATION OVER INTERNET (EX: CONNECTION TO OUR ONLINE LAB)
* NAMED PIPES : USED FOR CLIENT – SERVER COMMUNICATION OVER LAN (LOCAL AREA NETWORK)
* SHARED MEMORY : USED FOR CLIENT – SERVER COMMUNICATION WITH THE SAME OS. (EX: YOUR LOCAL SERVER ACCESS)* VIA : VIRTUAL INTERFACE ADDAPTOR [APPLICABLE FOR SQL SERVER 2014 AND OLDER VERSIONS]
USED FOR CLIENT – SERVER COMMUNICATION OVER INTERNET. LESS SECURE.

2. DATABASE ENGINE:

(a.) QUERY PROCESSING ENGINE
STEP 1: THE QUERY SENT FROM CLIENT IS “PARSED” AND “COMPILED” USING A SQL SERVER COMPONENT “PARSER”
PARSING : A MECHANISM TO READ AND VERIFY SQL QUERIES. IDENTIFY KEYWORDS, NAMES, ETC.. (TOKENS)
COMPILATION: A MECHANISM TO CONVERT FROM HIGH LEVEL SQL LANGAUGE CODE TO MACHINE LEVEL CODE

STEP 2: THE COMPILED CODE IS SENT TO “QUERY OPTIMIZER” (QO).
THIS COMPONENT IS USED TO FIND THE BEST WAY TO EXECUTE THE QUERIES. THIS GENERATES “QUERY PLAN”.

STEP 3: THE CONTROL GOES TO “SQL MANAGER”. USED TO VERIFY THE VALIDITY OF TABLES, COLUMNS AND OTHER KEYWORDS USED IN THE QUERY. RESERVE & LOCK QUERY RESOURCES (MEMORY, PROCESSOR)

STEP 4: THE CONVERTED QUERY IS NOW SENT TO “DATABASE MANAGER”. USED TO LOCATE DATABASE TABLES AND COLUMNS

STEP 5: FINALLY, THE QUERY STARTS TO EXECUTE BY USING “SQL EXECUTOR” OR “QUERY EXECUTOR” COMPONENT

(b.) DATABASE ENGINE – STORAGE ENGINE
* TRANSACTION MANAGER – TO CONTROL THE SQL OPERATIONS (TRANSACTIONS)
* FILE MANAGER – TO IDENTIFY DATABASE FILES (DATA FILES, LOG FILES, FILESTREAM FILES)
* BUFFER MANAGER – TO ALLOCATE REQUIRED MEMORY TO THE QUERIES. EACH SQL QUERY TAKES 1 MB OF MEMORY.
* LOCK MANAGER – TO LOCK OR RESERVE DATABASES, TABLES & COLUMNS FOR USER ACCESS

————————
ABOVE MANAGER COMPONENTS CAN BE ACCESSIBLE TO FILES, EXTENTS, PAGES, ROW ID [RID] AND INDEXES
ABOVE MANAGER COMPONENTS CAN BE CONTROLLED BY USING BULK OPERATIONS (IMPORT/EXPORT), DBCC, BACKUPS
DBCC MEANS : DATABASE CONSISTENCY CHECK COMMAND

3. SQLOS [SQL OPERATING SYSTEM]
THIS INVOLVES OPERATING SYTEM [WINDOWS OR LINUX] COMPONENT USED TO RUN SQL QUERIES.
* MEMORY MANAGER: USED TO ALLOCATE MEMORY TO SQL SERVER QUERIES
* BUFFER MANAGER: USED TO “BUFFER” OR “CACHE” THE DATABASE DATA. FOR FASTER DATA ACCESS TO USERS
* LOCK MANAGER: USED TO ALLOCATE LOCKS ON PAGES AND FILES. FOR EFFICIENT DATA STORAGE AND ACCESS
* IO MANAGER: USED TO ALLOCATE IO SIGNALS TO ACCEPT INPUT VALUES AND PRODUCE OUTPUT TO CLIENT
* TASK SCHEDULER: USED TO “SCHEDULE” / “AUTOMATE” ANY SQL SERVER OPERATION = “SQL SERVER JOBS”

MDAC: MICROSOFT DATA ACCESS COMPONENT. AUTO INSTALLED WITH OS.
USED FOR CLIENT – SERVER COMMUNICATION OF TDS PACKETS WITH SSMS TOOL.

CLR: COMMON LANGUAGE RUNTIME. THESE ARE PREDEFINED LIBRARY FILES.
USED TO CONNECT AND OPERATE ON SQL SERVER USING .NET, PYTHON, RUBY, PERL, NODE JS, ETC..

Tags:Architecture

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
Leave A Comment Cancel reply

MS SQL Server
  • SQL Server Architecture
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org