AlwayOn | Cloud and Datacenter Management Blog

SQL Always-On Cluster in Azure

Before we start with building this SQL Always-On Cluster we already have some Azure SDK Components Active in the Azure Subscription to work with:

Virtual Network VNET-001 is already installed
Subnet-SQL and Subnet-Domaincontrollers
Network Security Groups (NSG) with the right rules active
Two domain controllers
Azure Keyvault ( for disk Encryption)

We deployed three Virtual Machines in an Availability Set :

Primary SQL Node VM01
Secondary SQL Node VM02
Witness Server

The deployment was with ARM Template :

VM Deployment

Copy and paste the JSON in your template editor like Visual Studio Code for Example or in the Azure Portal Template builder.

Visual Studio Code

Azure Portal Template

You also can create a Private or Public Repository on GitHub and store your ARM Templates there in a Library.

GitHub Learning Lab
Learn new skills by completing fun, realistic projects in your very own GitHub repository. Get advice and helpful feedback from our friendly Learning Lab bot.

Create a SQL Server 2014 Always On Availability Group in an existing Azure VNET and an existing Active Directory instance via GitHub :

https://github.com/Azure/azure-quickstart-templates/tree/master/sql-server-2014-alwayson-existing-vnet-and-ad

Configure Always On Availability Group in Azure VM manually :

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

Important Tip :

Don’t forget to get the right connectivity between Azure Load Balancer and the SQL Always-On Listener :

## Get the Cluster Resource Information:
Clear-Host
Get-ClusterResource `
| Where-Object {$_.ResourceType.Name -like “IP Address”} `
| Get-ClusterParameter `
| Where-Object {($_.Name -like “Network”) -or ($_.Name -like “Address”) -or ($_.Name -like “ProbePort”) -or ($_.Name -like “SubnetMask”)}

#############################################################

## Set Cluster Parameters:
$ClusterNetworkName = “Cluster Network 1” # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = “IPlistener” # the IP Address resource name
$ListenerILBIP = “10.x.x.x” # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
[int]$ListenerProbePort = 80

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{“Address”=”$ListenerILBIP”;”ProbePort”=$ListenerProbePort;”SubnetMask”=”255.255.255.255″;”Network”=”$ClusterNetworkName”;”EnableDhcp”=0}

############################################################

Before you start with your SQL workloads from On-premises Datacenter on Microsoft Azure, have a look if PaaS Azure SQL Managed Instances is something for your Organization with all the benefits.

What is Microsoft Azure SQL Managed Instance?

Managed instance is a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for on-premises SQL Server customers. The managed instance deployment model allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, the managed instance deployment option preserves all PaaS capabilities (automatic patching and version updates, automated backups, high-availability ), that drastically reduces management overhead and TCO.

Read here more on Microsoft Docs about Azure SQL Services

Conclusion :

When you have a lot of SQL workloads and want to go to Microsoft Azure Cloud Services, analyze your existing workloads well and have a look first at Microsoft Azure SQL Managed Instances. With this Azure PaaS Service, you don’t have to manage the Complete Infrastructure like in a SQL Always-On Cluster (IaaS).