The Azure SQL Database Architecture
Azure SQL Database is a highly scalable multi-tenant and a highly available Platform-as-a-Service (PaaS) or Database-as-a-Service (DBaaS) offering from Microsoft.
Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.
Azure SQL Database has the following deployment options:
- Single
- Elastic pool
- Managed instance
Azure SQL Database allows users to focus only on managing data, and is divided into four layers that work together to provide users with relational database functionality, as shown in the following diagram:
Figure 1.1: The four layers of Azure SQL Database
Note
If you were to compare it to the on-premise SQL Server architecture, other than the Service Layer, the rest of the architecture is pretty similar.
Client Layer
The client layer acts as an interface for applications to access a SQL database. It can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between a SQL database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, and Java to easily connect to Azure SQL Database without any additional requirements.
Service Layer
The service layer acts as a gateway between the client and platform layers. It is responsible for:
- Provisioning a SQL database
- User authentication and SQL database validation
- Enforcing security (firewall rules and denial-of-service attacks)
- Billing and metering for a SQL database
- Routing connections from the client layer to the physical server hosting the SQL database in the platform layer
Platform Layer
The platform layer consists of physical servers hosting SQL databases in data centers. Each SQL database is stored on one physical server and is replicated across two different physical servers:
As shown in Figure 1.1, the Platform Layer has two other components: Azure Fabric and Management Services. Azure Fabric is responsible for load balancing, automatic failover, and the automatic replication of a SQL database between physical servers. Management Services takes care of an individual server's health monitoring and patch updates.
Infrastructure Layer
This layer is responsible for the administration of the physical hardware and the OS.
Note
Dynamic routing allows us to move a SQL database to different physical servers in the event of any hardware failures or for load distribution.
Azure SQL Database Request Flow
The following diagram shows the Platform layer:
Figure 1.2: Platform layer – nodes
The application sends a TDS request (login, DML, or DDL queries) to the SQL database. The TDS request is not directly sent to the Platform layer. The request is first validated by the SQL Gateway Service at the Service layer.
The Gateway Service validates the login and firewall rules, and checks for denial-of-service attacks. It then dynamically determines the physical server on which the SQL database is hosted and routes the request to that physical server in the Platform layer. Dynamic routing allows the SQL database to be moved across physical servers or SQL instances in the event of hardware failures.
Note
Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.
Dynamic routing refers to routing the database request to the physical server that hosts an Azure SQL database. This routing is done internally and is transparent to the user. If one physical server hosting the database fails, dynamic routing will route the requests to the next available physical server hosting the Azure SQL database.
The internals of dynamic routing are out of the scope of this book.
As shown in Figure 1.2, the Platform layer has three nodes: Node 1, Node 2, and Node 3. Each node has a primary replica of a SQL database and two secondary replicas of two different SQL databases from two different physical servers. The SQL database can fail over to the secondary replicas if the primary replica fails. This ensures the high availability of the SQL database.