Azure SQL Database

Microsoft Azure SQL Database is a managed cloud database-as-a-service based. It shares the SQL Server 2017 codebase. It is compatible with SQL Server 2014 and 2016 and supports most of the features available in SQL Server 2017.

The Azure SQL Database service has three different deployment options. Azure SQL DB can be deployed as a single instance with isolated resources. It can also be deployed as a set of databases on a shared elastic pool of resources that can be scaled up and down. The last option for deployment entails managed instances that also involves shared resources across databases that provide an easy transition from on-premise SQL Server.

History

Microsoft Azure SQL Database in 2009 was announced as a derivative of Microsoft SQL Server that is optimized and built for deployment on cloud environments. The cloud service went live in 2010 and has continued to support every version of SQL Server ever since. It released the standard and business service tiers in 2014 and released the hyperscale tier in 2018.

Foreign Keys

Supported

Same as in (Microsoft SQL Server).

Indexes

B+Tree Hash Table

Refer to (Microsoft SQL Server). Along with the indexes provided from the SQL Server codebase, Azure SQL Database produces recommendations for ADD/DROP INDEX in order to optimize its plans. It does this by monitoring the performance of queries across all SQL Database instances on the Azure cloud. It performance statistics of queries across all databases to develop models to estimate performance gains on a non-existent index for a certain query in a database instance. These estimates are used to create recommendations for adding and dropping indexes and the application can choose to automatically have these recommendations applied.

Logging

Physical Logging

Same as in (Microsoft SQL Server).

It is important to note that in service tiers such as Hyperscale that rely on logically boundless Azure Storage for persistent long-term storage, log-truncation happens much less frequently than in SQL Server.

Query Execution

Tuple-at-a-Time Model

Same as in (Microsoft SQL Server).

Query Interface

SQL

Like in (Microsoft SQL Server), Azure SQL DB supports a subset variant of the SQL extension, T-SQL.

Storage Architecture

Hybrid

Same as in (Microsoft SQL Server).

Storage Organization

Log-structured

Same as in (Microsoft SQL Server).

Stored Procedures

Supported

Same as in (Microsoft SQL Server).

System Architecture

Shared-Disk

There are three types of compute-storage high-level architecture for this cloud database service.

Standard service tier: This service tier entails a simple compute layer backed by a data hosting layer in Azure Blob storage which has built-in reliability.

Business critical tier: This service tier is one level below Hyperscale. This service tier essentially consists of replicated sets of singular SSD-enabled nodes that entail compute and storage activity. It consists of a primary node that handles read-writes and also keeps the rest of the replicas synchronized with it. The rest of the replicas are used for backup and read-only endpoints.

Hyperscale: The hyperscale service tier has support for database sizes of up to 100TB. It is meant to support rapid scaling and recovery. As per standard cluster compute models these days, the hyperscale architecture involves a synergy of compute and storage nodes. The compute node tier caches disk pages in its buffer pool based on SSD and is replicated a few times. Read-only transactions go to these replicas while read-write transactions go to the primary compute node. The compute tier gets its buffer pages from a page server tier whose nodes contain around 1 TB of cached disk pages in them. Azure Blob Storage is used for long term storage. The primary compute node forward log records to a log service tier whose nodes push out the changes to the compute replicas, page servers, and the long-term Azure Storage. It is important to note that each of these tiers is scalable and have high availability on Azure as the replicas are only used for reads.