SQL Server Always On availability group on Azure virtual machines

Hey everyone, Recently I have explored and prepared a demo in my lab environment with the help of Microsoft Docs. Please note that on this page I have listed the required steps to Install Always On availability groups on Azure virtual machines. Additionally, for better understanding, I mentioned in brackets which step should be executed on which server/portal. You have to follow mentioned links and steps one by one without fail to configure Always On AG on Azure successfully.

Image description: SQL Server Always On availability groups on Azure – diagram
Image source: Microsoft Docs

Note: As per the diagram we have to use 5 windows servers. But for my demo, I used 3 servers. One server for DC (Domain Controller), two servers for SQL Servers and domain controller used as an FSW server. So that I’ve avoided two servers from my Installation.

Servers List :
Total servers used: 3

Domain Controller:

  • Operating system: Windows Server 2016 Datacenter edition
  • Server Roles: DNS, ADDS
  • FSW  (File share Witness)

SQL Server1:

  • Operating system: Windows Server 2016 Datacenter edition
  • Server Roles: Failover cluster
  • Software’s Installed: SQL Server 2017 and SSMS 201

SQL Server2:

  • Operating system: Windows Server 2016 Datacenter edition
  • Server Roles: Failover cluster
  • Software’s Installed: SQL Server 2017 and SSMS 2017

Let’s start… All the best……

Part1 :

Microsoft Docs Link: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-prereq

Step1: Create a resource group (Azure Portal)

Step2: Create the network and subnets (Azure Portal)

  • For production, you must have to create two subnets. One for Active Directory Servers and One for SQL Servers.
  • For the demo, not required to two subnets.

Step3: Create availability sets (Azure Portal)

  • For production, you must have to create two availability sets. One for Active Directory Servers and One for SQL Servers.
  • For the demo, not required to two availability sets.

Step4: Create domain controllers (Azure Portal)

  • *In my demo, I used only one domain controller

Step5: Configure the domain controller (Domain Controller Server)

  • Install Active Directory Domain Services and DNS Server roles from Server manager
  • Create SQL Service and SQL Installation accounts (Ex: SQLService, SQLInstall and etc)
  • Add SQL Server login account to Domain Admins group

Step6: Note the IP address of the domain controller/primary domain controller (Azure Portal)

  • Note the Private IP Address of Domain Controller/Primary Domain Controller
  • Add the Active Directory server private IP address in DNS Settings
  • Restart Active Directory Server

Step7: *Create and configure the SQL Server VM’s (Azure Portal)

Note: In Microsoft Docs they have used “SQL Server 2016 SP1 Enterprise on Windows Server 2016” virtual machine from azure gallery to avoid SQL Server Installation. In my demo, I’ve used “Windows Server 2016 Data Center” virtual machine, so additional steps included with respect to SQL Server Installation below.

  • Create two Windows Servers

Step8: Join the servers to the domain (SQL Server1 and SQL Server2)

Step9: Add Failover Clustering features to both SQL Server VM’s (SQL Server1 and SQL Server2)

Step10: Configure the firewall on each SQL Server VM’s (SQL Server1 and SQL Server 2)

  • SQL Server VM:
    Port 1433 for a default instance of SQL Server
  • Azure load balancer probe:
    Any available port. Examples frequently use 59999
  • Database mirroring endpoint :
    Any available port. Examples frequently use 5022

Step11: *Add a cluster quorum file share (Domain Controller Server)

  • Here I have used Active Directory server as a Witness server. I have not created a separate server for Witness. In other means it’s Just a file share.
  • Windows cluster uses a file share to create a cluster quorum
  • Created a Shared folder on Domain Controller

Part2 :

Microsoft Docs Link: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial

Step1: Configure cluster quorum (SQL Server1)

Step2: Set the cluster IP address (SQL Server1)

  • Identify free/not used IP address and assign for cluster

Step3: Add the other SQL Server to cluster (SQL Server1)

Step4: Add a cluster quorum file share (Domain Controller)

Step5: Configure cluster quorum (SQL Server1)

Step6: *Install SQL Server and Management Studio on both SQL servers (SQL Server1 and SQL Server2)

Step7: Enable Availability Groups (SQL Server1 and SQL Server2)

Step8: Create a database on the first SQL Server (SQL Server1)

Step9: Create a backup share  (SQL Server1)

Step10: Take a full backup of the database (SQL Server1)

  • Without full backup, we cannot add databases to Availability groups

Step11: Create the Availability Group (SQL Server1)

Step12: Check the Availability Group (SQL Server1)

Image description: Databases are joined into availability group for high availability

Image description: Availablity group dashboard

**If you like this post, please Like and comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: