SQL Server Architecture: Components & Functions

SQL Server has a set of parts that work as one system. Each part has a task and connects to other parts to complete tasks.

Main Components of SQL Server Architecture

SQL Server has the Database Engine, the SQL OS, the SQL Agent, and the Service Broker. Each part does a job inside the system and moves data between areas.

The Database Engine runs queries and stores data. It also controls access and handles user requests. It writes data to files on the disk.

The Relational Engine reads queries and checks syntax. Also, it builds execution plans. The Storage Engine reads and writes pages on disk and manages transactions.

SQL Server takes data pages from disk and holds them in memory buffers. It controls how long each page stays in memory and when it writes changes to disk.

SQL Server first parses a query and checks its plan. It then asks the Storage Engine for data pages. The Storage Engine gives pages back, and the Relational Engine processes them. The result then returns to the user.

Examples of SQL Server Architecture

Create a Simple Database:

CREATE DATABASE SchoolDB;

This example shows how to make a new database in SQL Server. It gives a name to the database and tells the engine to make files on disk to store data.

Use Execution Plan to View Query Steps:

SET SHOWPLAN_TEXT ON;
SELECT * FROM Students WHERE Grade = 'A';
SET SHOWPLAN_TEXT OFF;

This example turns on text plans and runs a select query on the Students table. It lets you see how the Relational Engine builds and runs a plan before it touches data.

Control Memory with Resource Governor:

CREATE RESOURCE POOL LimitedPool WITH (MAX_MEMORY_PERCENT = 25);
ALTER RESOURCE GOVERNOR RECONFIGURE;

This example creates a pool with a memory limit inside SQL Server. It sets a rule so heavy tasks cannot take all the memory and lets other tasks still run.

Use Service Broker to Send a Message:

BEGIN DIALOG CONVERSATION @handle
FROM SERVICE Initiator
TO SERVICE 'TargetService'
ON CONTRACT [Contract1]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @handle
MESSAGE TYPE [RequestMessage]
(@MessageBody);

This example starts a dialog between two services inside SQL Server and sends a message. It shows how Service Broker can move data between parts inside one server without outside code.

Wrapping Up

You saw the main parts of SQL Server and how they handle data with examples that show steps from basic setup to advanced control.

You now have a base view of how SQL Server parts work together inside one system.

FAQs

Similar Reads