Azure SQL Database – the database engine that cannot die

[JovanPop]

First published on MSDN on Apr 25, 2018
Azure SQL Database is highly available database Platform as a Service that guarantees that your database will be up and running 99.99% of time, without worrying about maintenance and downtimes. This is a fully managed SQL Server Database Engine process hosted in Azure cloud that ensures that your SQL Server database is always upgraded/patched without affecting workload. Azure SQL Database could quickly recover even in the most critical circumstances ensuring that your data is always available.



Azure platform fully manages every Azure SQL Database and guarantees no data loss and high percentage of data availability. Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades and other maintenance tasks. SQL Server engineers have implemented the best-known practices in SQL Server that is running in Azure cloud, ensuring that all the maintenance operations are completed in less than 0.01% time of your database life. This architecture is designed to ensure that committed data is never lost and that maintenance operations are performed without affecting workload. There are no maintenance windows or major downtimes that should require you to stop the workload while the database is upgraded or maintained. Built-in High Availability in Azure SQL Database guarantees that database will never be single point of failure in your software architecture.

There are two high-availability models applied in Azure SQL:

• Standard/general purpose model that provides 99.99% of availability but with some potential performance degradation during maintenance activities.


• Premium/business critical model that provides also provides 99.99% availability with minimal performance impact on your workload even during maintenance activities.

Azure upgrades and patches underlying operating system and SQL Server Database Engine transparently with the minimal down-time for end users. Azure SQL Database runs on the latest stable version of SQL Server Database Engine and Windows OS, and most of the users would not notice that the upgrades are performed continuously.

Some connection interruptions might occur during the maintenance, but it is advised to use drivers with retry logic or implement retry logic in database access code.

In the following sections will be explained differences between standard and premium availability models.

Standard availability

Standard availability refers to 99.99% SLA that is applied in Standard/Basic tiers in Azure SQL database and General Purpose tier Azure SQL Managed Instance. Availability is achieved by separation of compute and storage layers. In the standard availability model we have two layers:

1. Stateless compute layer that is running sqlserver.exe process and contains only transient and cached data (for example – plan cache, buffer pool, column store pool). This stateless SQL Server node is operated by Azure Service Fabric that initializes process, controls health of the node, and performs failover to another place if necessary.


2. Stateful data layer with database files (.mdf/.ldf) that are stored in Azure Premium Storage. Azure Storage guarantees that there will be no data loss of any record that is placed in any database file. Azure Storage has built-in data availability/redundancy that ensures that every record in log file or page in data file will be preserved even if SQL Server process crashes.

Whenever database engine or operating system is upgraded, or if some critical issue is detected in Sql Server process, Azure Service Fabric will move the stateless SQL Server process to another stateless compute node. Data in Azure Storage layer is not affected, and data/log files are attached to newly initialized SQL Server process. This process guarantees 99.99% availability, but it might have some performance impacts on heavy workload that are running due to transition time and the fact the new SQL Server node starts with the cold cache.

Premium availability

Premium availability is enabled in Premium/Business Critical tiers of Azure SQL Database and it is designed for intensive workloads that cannot tolerate any performance impact due to the ongoing maintenance operations.

In the premium model, Azure SQL database has integrated compute and storage on the single node. Both SQL Server Database Engine process and underlying mdf/ldf files are placed on the same node with locally attached SSD storage providing low latency to your workload.

High availability is implemented using standard Always ON Availability Groups . Every database is a cluster of database nodes with one primary database that is accessible for customer workload, and few secondary processes containing copies of data. Primary node constantly pushes the changes to secondary nodes in order to ensure that the data would be available on secondary replicas if primary node accidentally crashes. Failover is handled by SQL Server Database Engine – one secondary replica is becoming primary node and new secondary replica is created instead of the falling ones in order to ensure enough nodes in the cluster. Workload is redirected to the new primary node that is ready to continue serving requests.

Conclusion

Azure SQL Database is a fully managed, highly available Sql Server Database Engine process that guarantees almost no downtime for your workload even the during maintenance operation. With Azure SQL you are getting highly available database that is always patched and upgraded with minimal downtime for your workloads. If you need a database that cannot die even in the most critical situation and should be always available – Azure SQL Database might be the right solution for you.

You can find more information about the High availability in Azure SQL on official documentation .