Understanding Azure SQL Database Architecture

by May 2, 2023

This blog will give you a glimpse into the internal architecture of SQL Database and brings

to light some of the critical concepts that SQL Database is built on. This blog introduces you to Azure SQL Database, the Platform as a Service database service offered on the Microsoft Azure cloud. It has become a robust and highly available database platform wrapped as a service offering specialized security features. 

If you have ever used SQL Database, you already know how simple using this relational database platform is. Creating a database instance in SQL Database is as simple as pushing a few buttons, and within minutes you can have a fully supported, production-ready database infrastructure at your fingertips. However, you may wonder how this technology is put together? You may ask yourself how Microsoft can guarantee high availability, what the master database is in a SQL Database, or even how the built-in connection firewall is implemented behind the scenes at the server and database level. 

SQL Database Topology

Let’s start with the underlying infrastructure. SQL Database comprises a layer of routers, firewalls, servers, and services that provide a unique database service. When a connection request is sent to a SQL Database, it is handled by a proxy layer through a series of gateways that perform login validation, enforces security constraints such as firewall rules and denial of service attacks, and additional services like billing and provisioning.

Once the TDS (Tabular Data Stream) request is validated by the proxy layer, it is forwarded to the server containing the requested database. So, the client sending the original bid is not communicating directly with the server holding the database; it is going through the proxy layer that determines dynamically where the database is located at the time of the request by looking up an internal mapping table. This dynamic database routing mechanism allows the SQL Database infrastructure to move the database instances on other servers anytime to account for hardware failures and load distribution.

About Master

Because your SQL Database instances are not stored on the same machine, you may wonder where your master database resides and what you can do with it. The master database is, in fact, a virtual database; it is handled by the Gateway layer, where the general SQL Database Server security settings are defined. This explains why the master database is read-only in SQL Database. This also explains why you must connect to the master database (i.e., the Gateway layer) to create additional databases. 

The server-wide firewall settings of SQL Database are also stored in the master at the Gateway layer. This also explains why changing firewall settings takes effect relatively quickly because the Gateway layer is also responsible for enforcing the firewall rules. Simplified SQL Database Topology Last but not least, the Gateway layer also handles database provisioning, stores audit logs, and keeps summary information of bandwidth usage. As a result, the Gateway layer provides the billing details and can be visible by querying the master Database.

About SQL Database Instances

The current version of SQL Database is V12, a unique build of SQL Server 2016. When you create a new server, a DNS entry is made so that the server is available from the Internet (once the firewall is configured), and new databases can be created. Each SQL Database, referred to as a database instance, is a contained SQL Server database built on a server. This means that you can create users at the database level directly, making them more portable. These instances run in a shared hosting model with databases from other Azure tenants;. In contrast, you essentially share the hardware with other tenants, and each database instance is strongly isolated from a security standpoint.

Because SQL Database instances are replicated internally for high availability, some partitions also hold repeated cases. In the following figure, you see a representation of a SQL Database instance called TestDB; this instance is, in fact, a contained Database.

This architecture has a few implications for performance and management standpoint. The TestDB database instance shares system-stored procedures and internal tables with others on the SQL server. SQL Database limits access to sensitive internal system objects for security reasons preventing administrators from executing maintenance commands. However, many system-stored procedures and dynamic management views (DMV) allow you to troubleshoot performance problems on a SQL Database instance.

The following lists the DMVs currently available:

  • sys.dm_tran_active_transactions
  • sys.dm_tran_database_transactions

  • sys.dm_tran_locks

  • sys.dm_tran_session_transactions
  • sys.dm_exec_connections
  • sys.dm_exec_query_plan
  • sys.dm_exec_query_stats
  • sys.dm_exec_requests
  • sys.dm_exec_sessions
  • sys.dm_exec_sql_text
  • sys.dm_exec_text_query_plan
  • sys.dm_db_partition_stats

Load Balancer

The Gateway service also uses a load balancing mechanism that periodically evaluates the current load on the primary replica. The Gateway can upgrade a secondary replica to the new primary replica. This continuous shift in primary replica promotion allows SQL Database to respond quickly to increased workloads and better distribute server resources across the pool of servers available.

High Availability

SQL Database instances are copied to other partitions for high redundancy and availability. Each SQL Database instance, including its copies, is called a replica. The primary replica is the database instance you connect to and execute statements against. There are two secondary replicas for redundancy. When a database commit is issued against the primary replica, at least one of the other two secondary replicas must also confirm the commit operation before the transaction is considered committed. This is referred to as a quorum commit.

This replication architecture ensures 99.99% connection availability and is designed to fail over quickly to a secondary replica if the primary replica fails. The failover could occur for multiple reasons, including a failure on the primary replica, a failure on the server itself, or a failure on the rack that holds the server. The failover could also happen for other reasons that are not considered hard failures, such as during an upgrade of the SQL Database environment. The failover process could take a few seconds, disconnecting clients with active sessions to the primary replica at the time of the failure and preventing new connections until the new replica becomes available.

Backup and Geo-Replication

SQL Database offers advanced backup and geo-replication capabilities; geo-replication is available for backup recovery and asynchronous transactional replication. Point-in-time restores allow you to restore a database automatically backed up by Azure within 7 to 35 days, depending on your database tier (a database tier is a service level you choose when creating a database). You also have the option to use long-term retention for your backups for up to 10 years.

In addition to the point-in-time restores, SQL Database can geo-replicate backup files and replicate transactions to multiple databases asynchronously. Microsoft manages Geo-replicated backup files directly; the backup files are stored as blob storage in the paired database center. Microsoft also controls paired data centers; for example, the US East is paired with US West.

When using transactional replication, you can define up to four read-only destination databases in any Azure region. In other words, you can connect to any replicated database and issue SQL commands to access data. You can fail over to a replicated database and make it the new primary database. 

SQL Database Firewall

As mentioned previously, SQL Database implements a firewall mechanism to help you limit access to your database instances. The firewall is designed to keep a list of allowed IP ranges for which SQL Database can further authorize database credentials. In other words, if a client connects outside the allowed IP ranges, the connection request will be denied even if the credentials are valid.

You can manage two sets of firewall rules: server-level and database-level. Server-level rules are evaluated first; database rules are assessed if the server-level rules fail. A few stored procedures and system views are available to manage the firewall rules. For example, you use the sys.firewall_rules view to list the current rules. To address the rules, you use the sp_set_firewall_rule and sp_delete_firewall_rule stored procedures to add and delete firewall rules, respectively. You may need to consider the following regarding setting up firewall rules with SQL Database:

  • SQL Database is secured by default. You must enable the desired IP ranges before establishing a database connection.
  • The Azure management portal allows you to configure the IP range rules.
  • The IP ranges defined in SQL Database only work for public IP Addresses. To connect from a machine connected to the Internet through NAT (Network Address Translation), you must first determine its public IP Address.

Authentication and Authorization

SQL Database provides similar user authentication and authorization levels to SQL Server 2016 by allowing you to manage logins and database-level users, either as traditional server-scoped logins or the more recently contained database login model. If you have used SQL Database before, you might be interested to learn that it now supports Azure Active Directory Authentication. When using a server-scoped login, you must first create the login on the server (master) and the corresponding user in the desired database(s). When using the contained database login model, you only need to create the user in the selected database(s).

Encryption, Auditing, Data Masking, Compliance

In addition to the firewall, as mentioned earlier, configuration options, and user authentication and authorization security measures, SQL Database offers optional disk-level encryption at rest, auditing, data masking, and compliance certifications for more sensitive applications.

When enabled, the disk-level encryption feature (TDE) lets you specify that all data, including database backups, should be encrypted at rest. While TDE provides a significant security capability, you may still want to mask data access to specific fields. For example, you should hide data for a Social Security Number. Data masking allows you to define masking rules for detailed login accounts.

From an auditing standpoint, you can easily integrate with Microsoft Power BI to offer drill-down analysis of your logs. You can also configure email alerts of unexpected activities, such as SQL Injection detection, that can be sent to one or more email addresses. You can obtain additional information on the portal, such as the source IP Address and the actual T-SQL statement that caused the Alert.

Resource Limits and Throttling

Throttling is a term used to describe a usage boundary in SQL Database or other cloud resources in Azure that are enforced by delayed execution of queries or through a loss of connection. In SQL Database specifically, throttling is implemented on the server to prevent heavy loads from affecting other database tenants on the same server. If your query consumes fewer resources, such as CPU, I/O, or memory, your question may be delayed and executed when the resources become available.

Suppose your system exceeds one of the limits for your database tier, for example. If you attempt to create more than 300 concurrent sessions on a basic service tier, throttling may kick in, and your application may receive an error.

The rules used to determine whether throttling will occur and to which degree can be complex and may change over time. In addition, throttling can directly affect coding practices and how you design specific database components. For more information about throttling and general database connection management, check SQL Database Resource Limits.

Understanding Billing

Each database instance has two secondary replicas. You will be charged for databases used for geo-replication.

When you create a database server, you can create a single database at a time or an elastic pool. A flexible collection is a logical container that allows you to create many databases for managing performance and billing at the pool level. Elastic pools will enable you to control the overall allocation of CPU/IO/Memory resources across your databases.

The charge for database usage is measured in Database Transaction Units (DTUs) for single databases or elastic DTUs (eDTUs) for elastic pools. Databases are created in a performance tier (Basic, Standard, Premium), allowing you to choose the number of DTUs or eDTUs available to your databases.

Your invoice summarizes the charges consumed by your Azure services, including databases and data transfer. The entry-level cost (basic tier) for the first DTU is $0.0067 hourly, equivalent to $4.99 per month. You should note that pricing depends on your database’s tier and geographic region.

 

Idera provides robust solutions for SQL Server, Azure SQL Database, and Amazon RDS for SQL Server:

Additional Resources: