Azure SQL Database Architecture

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and provides high availability without user involvement.

Overview of Azure SQL Database Architecture

Azure SQL Database Architecture Overview Diagram

Conceptual diagram of Azure SQL Database architecture.

Key Components

Compute Layer: This layer is responsible for executing queries, managing connections, and enforcing transactional integrity. Azure SQL Database runs on a cluster of Windows Server machines.
Storage Layer: This layer handles data persistence, recovery, and replication. Data is stored in the cloud and managed by Azure's robust storage infrastructure.
Networking Layer: This layer manages network traffic, security, and connectivity to the database. It includes features like firewalls, private endpoints, and service endpoints.

Architecture Models

Azure SQL Database offers two primary deployment models:

Single Database: This model provides a dedicated, isolated database with its own set of resources. It's ideal for new cloud applications or for migrating existing standalone databases.
Elastic Pool: Elastic pools allow you to manage multiple databases with varying usage demands that share a set of resources. This is cost-effective for applications with fluctuating workloads.

Key Architectural Concepts

Compute and Storage Separation: Unlike traditional on-premises databases, Azure SQL Database separates compute and storage, allowing for independent scaling and improved resilience.
Resource Governance: Azure SQL Database uses a sophisticated resource governance system to ensure fair resource allocation and prevent noisy neighbor issues. This is achieved through a combination of Windows Server features and internal Azure SQL Database mechanisms.
High Availability and Disaster Recovery (HA/DR): Azure SQL Database provides built-in HA/DR capabilities, including automatic failover and geo-replication, to ensure business continuity.

Note: Understanding the underlying architecture helps in optimizing performance, managing costs, and designing resilient applications on Azure SQL Database.

Internal Architecture Breakdown

At a lower level, Azure SQL Database architecture can be visualized as follows:

Azure SQL Database Internal Structure

Detailed view of Azure SQL Database internal components.

Guest OS: Each logical SQL server instance runs on a dedicated virtual machine managed by Azure.
Fabric Controller: Manages the lifecycle of SQL Server instances and ensures high availability.
Compute Node: Hosts the SQL Server engine, memory management, and query processing.
Storage Node: Manages data files, log files, and backups.
Log Shipping: A mechanism for replicating transaction logs to ensure data durability and enable quick recovery.

Connectivity

Connections to Azure SQL Database are established through standard SQL Server connection methods, utilizing TCP/IP protocols. Azure provides secure and reliable connectivity options, including:

Public endpoint with firewall rules.
Private Endpoint for secure access within a virtual network.
Service Endpoint for enhanced security and routing.

Security Features

Azure SQL Database incorporates a comprehensive set of security features:

Authentication and Authorization (Azure AD, SQL Authentication).
Encryption (Transparent Data Encryption, Always Encrypted).
Network Security (Firewalls, VNet integration).
Threat Detection and Vulnerability Assessment.