Hi fellows,

Over the past few days, I dealt with a client request to configure SQL AlwaysOn cluster in a hybrid environment. At the first look, that should have been a simple task, especially because I have configured AlwaysOn in dozens of projects. Unfortunately, project brought me a lot of problems during configuration, but I finally won 🙂 In this post, I will explain how you can expand your AlwaysOn SQL server by adding the Azure SQL VM to existing cluster.

Before you start a project like this, you need to know what are the prerequisites that allow you to run the entire process smoothly.

  • Domain environment
  • SQL Server Standard 2016 or newer. Older SQL Server versions support AlwaysOn only in Enterprise edition
  • Installed and configured Failover Clustering feature on SQL Servers
  • Azure SQL VM
  • Site-to-site VPN tunel
  • Azure Internal Load Balancer (ILB)

Let’s start.

Step 1 – Prepare domain environment

You need to have a configured domain environment. Although it’s possible to install and configure Failover Cluster, that is one of prerequisites for AlwaysOn, in the Workgroup environment, with the domain environment the complete process will be much simpler. Of course, when you establish a hybrid between the on premise datacenter and Azure, it is recommended to install additional domain controller in Azure.

Step 2 – Install and configure SQL Server on premise

To configure AlwaysOn on SQL Server 2016, in addition to the installed SQL Server, you must install Failover Cluster feature on each SQL Server. Then you need to configure cluster with SQL Servers as a cluster nodes. Each SQL Server that will be part of AlwaysOn must have the same setup. In one of my previous posts, you can find steps how to Install SQL Server.

Step 3 – Provision and configure Azure virtual network and VPN

Before you provision and configure Azure VM with SQL Server 2016, you need to prepare Azure environment for hybrid. The first, and most important step in almost all Azure projects, is creating Virtual Network and configuring Site-to-site VPN tunnel with on premise datacenter. Complete process of creating virtual network and site-to-site VPN tunnel is explained and described in one of my previous posts.

Step 4 – Create Azure Internal Load Balancer as a listener for AlwaysOn

Listener is one of the most important parts in AlwaysOn infrastructure. If you have AlwaysOn cluster configured on premise, listener is virtual computer object (VCO) with IP address from private IP address range. But, if you have AlwaysOn in Azure SQL VMs or in hybrid environment, you cannot simply use one of IP addresses for listener. To create functional listener for AlwaysOn, where both or just one of SQL Servers are Azure VM, you need to configure Azure Load Balancer in Internal mode. Of course, you still need VCO. In one of my previous posts, you can learn how to create Azure Load Balancer. Same procedure can be used in this step with following parameters:

  • Internal load balancer with static IP address
  • Select previously created virtual network and subnet for virtual machines

  • Backend pool needs to have SQL Server virtual machine
  • Health probe needs to check SQL Server port 1433
  • Load Balancer rule needs to use previously created backend pool and health probe, with forwarding from frontend SQL port to backend SQL port 1433. Floating IP must be enabled.

Once you have created Azure ILB, you can go to next step.

Step 5 – Install and configure SQL Server on the Azure VM

The process of SQL Server 2016 installation on the Azure virtual machine is identical as previously described for on premise. The process of creating the Azure VM was described in one of my previous posts. During provisioning Azure VM for SQL Server, you need to:

  • Place VM in virtual network that is previously created and configured to communicate with on premise datacenter using VPN Gateway
  • Configure Availability Set for Azure VM. With availability set, you will be ready if you need to move complete AlwaysOn cluster later in Azure

Step 6 – Configure failover cluster and AlwaysOn on SQL Servers

The cluster creation process on SQL Servers doesn’t differ from creating a cluster in the standard Windows scenario. Complete process is described in one of my previous posts. You just need to follow provided steps and once you have configured cluster, you can start with preparing SQL Servers for AlwaysOn.

Process of configuring AlwaysOn Availability groups is described in details in one of my previous posts. If you want to configure AlwaysOn Availability Groups in hybrid environment, process is almost the same, with few differences. You need to perform following steps:

  1. Create Failover Cluster with two nodes, on premise SQL Server and Azure SQL Server VM
  2. Enable AlwaysOn feature in SQL Servers
  3. Create virtual computer object (VCO) for AlwaysOn listener or delegate permission to cluster account to create VCO
  4. Create and prepare database for adding to AlwaysOn Availability group

Once you meet all prerequisites, you can start with creating AlwaysOn Availability Group. All steps are the same, as you create availability group from on premise nodes, except listener configuration. During creating and configuring listener, you need to add two IP address, one from on premise subnet and second one from Azure subnet. Because Azure doesn’t support adding virtual IP (VIP), like as on premise networks, you need to add IP address of previously configured Azure ILB.

 Once you finish configuration, result is be successfully configured AlwaysOn Availability Groups in hybrid environment 🙂

See you soon!

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

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