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..