Evaluate the HADR of the possible database offering
In this we will learn about the business continuity and HADR for SQL Server on Azure Virtual Machines.
Business continuity means continuing your business in the event of a disaster, planning for recovery, and ensuring that your data is highly available. However, SQL Server on Azure Virtual Machines can help lower the cost of a high-availability and disaster recovery (HADR) database solution.
Most SQL Server HADR solutions are supported on virtual machines (VMs), as both Azure-only and hybrid solutions. In an Azure-only solution, the entire HADR system runs in Azure. However, in a hybrid configuration, part of the solution runs in Azure and the other part runs on-premises in your organization.
Deployment architectures
Azure supports SQL Server technologies for business continuity:
Azure only: High-availability solutions
You can have a high-availability solution for SQL Server at a database level with Always On availability groups. Moreover, you can also create a high-availability solution at an instance level with Always On failover cluster instances. For additional protection, you can create redundancy at both levels by creating availability groups on failover cluster instances.
Free DR replica in Azure
If you have Software Assurance, you can implement hybrid disaster recovery (DR) plans with SQL Server without incurring additional licensing costs for the passive disaster recovery instance.
For example, you can have two free passive secondaries when all three replicas are hosted in Azure:
Or you can configure a hybrid failover environment, with a licensed primary on-premises, one free passive for HA, one free passive for DR on-premises, and one free passive for DR in Azure:
Important considerations for SQL Server HADR in Azure
Azure VMs, storage, and networking have different operational characteristics than an on-premises, non-virtualized IT infrastructure. However, a successful implementation of an HADR SQL Server solution in Azure requires that you understand these differences and design your solution to accommodate them.
High-availability nodes in an availability set
Availability sets in Azure enable you to place the high-availability nodes into separate fault domains and update domains. The Azure platform assigns an update domain and a fault domain to each virtual machine in your availability set. However, this configuration within a datacenter ensures that during either a planned or unplanned maintenance event, at least one virtual machine is available and meets the Azure SLA of 99.95 percent.
High-availability nodes in an availability zone
Availability zones are unique physical locations within an Azure region. Each zone consists of one or more datacenters equipped with independent power, cooling, and networking. However, the physical separation of availability zones within a region helps protect applications and data from datacenter failures by ensuring that at least one virtual machine is available and meets the Azure SLA of 99.99 percent.
Further, to configure high availability, place participating SQL Server virtual machines spread across availability zones in the region. There will be additional charges for network-to-network transfers between availability zones. For more information, see Availability zones.
High availability for Azure SQL Database and SQL Managed Instance
The goal of the high availability architecture in Azure SQL Database and SQL Managed Instance is to guarantee that your database is up and running minimum of 99.99% of time without worrying about the impact of maintenance operations and outages. Azure automatically handles critical servicing tasks, such as patching, backups, Windows and Azure SQL upgrades, as well as unplanned events such as underlying hardware, software, or network failures. When the underlying database in Azure SQL Database is patched or fails over, the downtime is not noticeable if you employ retry logic in your app. SQL Database and SQL Managed Instance can quickly recover even in the most critical circumstances ensuring that your data is always available.
There are two high availability architectural models:
- Firstly, Standard availability model that is based on a separation of compute and storage. It relies on high availability and reliability of the remote storage tier. This architecture targets budget-oriented business applications that can tolerate some performance degradation during maintenance activities.
- Secondly, Premium availability model that is based on a cluster of database engine processes. It relies on the fact that there is always a quorum of available database engine nodes.
Basic, Standard, and General Purpose service tier locally redundant availability
The Basic, Standard, and General Purpose service tiers leverage the standard availability architecture for both serverless and provisioned compute. The following figure shows four different nodes with the separated compute and storage layers.
However, the standard availability model includes two layers:
- Firstly, a stateless compute layer that runs the sqlservr.exe process and contains only transient and cached data. They are TempDB, model databases on the attached SSD, and plan cache, buffer pool, and columnstore pool in memory.
- Secondly, a stateful data layer with the database files (.mdf/.ldf) that are stored in Azure Blob storage. Azure blob storage has built-in data availability and redundancy feature.
General Purpose service tier zone redundant availability
Zone redundant configuration for the general purpose service tier utilizes Azure Availability Zones to replicate databases across multiple physical locations within an Azure region. By selecting zone redundancy, you can make your new and existing general purpose single databases and elastic pools resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes of the application logic.
However, Zone redundant configuration for the general purpose tier has two layers:
- Firstly, a stateful data layer with the database files (.mdf/.ldf) that are stored in ZRS PFS (zone-redundant storage premium file share. Using zone-redundant storage the data and log files are synchronously copied across three physically-isolated Azure availability zones.
- Secondy, a stateless compute layer that runs the sqlservr.exe process and contains only transient and cached data. They are TempDB, model databases on the attached SSD, and plan cache, buffer pool, and columnstore pool in memory.
The zone redundant version of the high availability architecture for the general purpose service tier is shown in the following diagram:
Reference: Microsoft Documentation, Documentation 2