SQL Server Architecture

Understanding SQL Server's internal architecture helps you design better databases, write more efficient queries, and troubleshoot performance issues. This lesson covers instances, system databases, the storage engine, and memory management.

---

Instances

An instance is a running copy of the SQL Server Database Engine:

• A single server can host multiple instances
• Each instance has its own system databases, memory, and configuration
• The default instance is accessed by server name; named instances use ServerName\InstanceName

Physical Server
├── Default Instance (MSSQLSERVER)
│   ├── master, msdb, model, tempdb
│   └── User databases
├── Named Instance (DEV01)
│   ├── master, msdb, model, tempdb
│   └── User databases
└── Named Instance (TEST01)
    ├── master, msdb, model, tempdb
    └── User databases

Tip: Named instances are useful for running different SQL Server versions on the same machine, for example during migrations.

---

System Databases

Every SQL Server instance has four system databases: