SQL Server High Availability


Introduction & Overview
Course Summary
2m 56s
Start course

High availability and disaster recovery are key to ensuring reliable business continuity. While SAP workloads are mainly confined to Azure's infrastructure layer, it is still possible to utilize many Azure functions and features to enhance system reliability with relatively little effort. This course looks at when, where, and how to use Azure's built-in infrastructure redundancy to improve system resiliency and how various database high availability options are supported.

Learning Objectives

  • Understand the key aspects of high availability and disaster recovery
  • Learn about availability and availability zones
  • Learn about Azure Site Recovery and how to implement it through the Azure portal
  • Learn how to set up an internal load balancer in the context of SAP workloads
  • Understand the Azure support options for Pacemaker and STONITH
  • Learn how to implement Data Guard mirroring via the Azure CLI
  • Set up Windows Failover Cluster and SQL Server Always On through the Azure portal

Intended Audience

This course is intended for anyone who wants to use Azure's built-in infrastructure redundancy to enhance the reliability and resiliancy of their SAP workloads.


To get the most out of this course, you should be familiar with Azure, Azure CLI, SAP, SQL Server, and STONITH.


In this demonstration, I'll set up SQL Server Always On functionality that utilizes Windows Failover Cluster to provide a Windows-based high availability database solution. While this technology predates Azure, the cloud implementation, in my opinion, is substantially easier to implement. My starting point is two Windows Server 2016 virtual machines running SQL Server 2017. They are deployed in an availability set and joined to an Azure Active Directory domain. You can also deploy the servers in an Availability Zone configuration, but you'll need to ensure the machines are running the IaaS SQL Agent extension in full management mode. 

To configure the IaaS SQL Agent, go into your subscription and then Resource Providers and check Microsoft.SqlVirtualMachines is registered. If not, register it. If the extension isn't installed, there'll be a message on the overview page of the SQL virtual machine asking if you want to install it. You can view the extension status through the SQL configuration link or go to the Repair page and check on the agent's provisioning state. To check the extension's mode, you can query the SQL VM's management type via PowerShell.

I've also created a storage account in the same resource group to be used as a cluster witness. I don't have a load balancer in place, as I'll create it when I create the always-on availability group.

First off, we need to create the Windows Server Failover Cluster. I'll do that through the SQL virtual machine sqlsvr1 under High Availability. Add a new windows server failover cluster, giving it a name of 14 characters or less. Select a witness storage account or create one. The SQL service account for the cluster credentials must be a domain account with admin permissions, or at least sufficient permissions. The user must belong to the local machines' administrator group. When you create the SQL Service Account user, make sure the password you give them is at least 12 characters long, or you won't pass the validation here. I'll use the same account for everything. Next, select the machines to be in the cluster, click apply, and yes to proceed.

With the failover cluster in play, we can add the always-on availability group. Give the group a name and configure the listener. Select the subnet with the VMs and enter a private IP address within the subnet. The address doesn't automatically update when you change the subnet. Here I'll create a new load balancer and click apply. Next, add the replicas. SqlSvr1 is already selected as the primary, and I'll add sqlsvr2 in a secondary role and check automatic failover, sync commit set both to readable secondaries, and click apply. Then click apply again to create the availability group.

In SQL Server management studio on sqlsvr1, I'll create a new database and back it up. This is required before adding a DB to an Availability group. Once backed up, I can add it to the availability group via the add database to availability group wizard. Logging on to sqlsvr2, we can see the database has replicated to the server and is synchronized. In the portal, after refreshing, the synchronization health is good. Stopping the SQL server instance on sqlsvr1 will cause a failover, and sqlsvr2, the secondary, becomes the primary.

About the Author
Learning Paths

Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a  Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.