How Microsoft SQL Server Is Built: A Simple Guide

What is SQL Server?

A database is a place where programs store information in an organised way. Microsoft SQL Server is a database system, which is the software that runs databases. Websites and apps send it questions, and it sends back answers.

Think of it as a big library. The library has a front desk, a team of librarians, and shelves full of books. SQL Server is made of similar parts.

The big picture

SQL Server has two main layers. One layer talks to clients. The other layer handles the data on disk.

1. The protocol layer

This is the front desk. A client is any program that connects, like your website. It reaches SQL Server over the network. The protocol layer receives the request and passes it on. Clients commonly connect on port 1433 by default. A port is like a numbered door on a computer.

2. The relational engine

This is the brain. It checks the request, plans the best way to answer, and runs the plan. It has these parts:

• Parser: reads the SQL and checks for mistakes.
• Optimizer: picks the fastest way to find the data, like choosing the shortest road.
• Query executor: follows the plan and asks the storage engine for data.

3. The storage engine

This is the librarian who fetches books from the shelves. It reads and writes data on disk. It also handles:

• Locks: they stop two people from changing the same row at the same time.
• Transactions: a group of steps that succeed together or fail together. Like moving money, you do not want to take it from one account and lose it before adding it to the other.
• Buffer pool: a memory area that keeps recently used data, so it is quick to reach.

4. SQLOS

SQLOS is a small operating layer inside SQL Server. It manages memory, tasks and scheduling. It is like the library manager who hands out rooms and time.

How data is stored on disk

Data inside files is split into pages of 8 KB each. Eight pages in a row make an extent. SQL Server reads and writes whole pages at a time.

System databases

• master: keeps server-wide settings and the list of all databases.
• model: a template for new databases.
• msdb: stores jobs and schedules.
• tempdb: a scratch pad for temporary work. It resets when the server restarts.

A request, step by step

1. A client sends a question.
2. The protocol layer receives it.
3. The parser and optimizer plan it.
4. The executor runs the plan.
5. The storage engine fetches the data, using memory first.
6. The answer goes back to the client.

Quick recap

• SQL Server has a protocol layer, a relational engine and a storage engine.
• The optimizer chooses the fastest way to get data.
• Data lives in .mdf, .ndf and .ldf files made of 8 KB pages.
• System databases such as master and tempdb keep the server running.