次の方法で共有

Hybrid IT: SQL Always on Availability Group Replica Node in Azure IaaS

  • [アーティクル]

Hybrid IT: SQL Always on Availability Group Replica Node in Azure IaaS

 

Greetings!

Lately, I came across to Azure IaaS offerings in a casual conversation with a cloud savvy acquaintance. Site-To-Site and Point-To-Site VPN offerings as part of IaaS triggered obvious thoughts of extending an on-premise environment for different purposes like DR (Disaster Recovery), Dev, Test etc. Azure Express Route offering is likely option for enterprises but Site-To-Site and Point-To-Site VPN options are significant for smaller or lower environments and/or to facilitate remote sites, user(s) connections access to applications. 

First thing that sounds interesting is to prepare a DR environment in Azure IaaS. An extension of on-premise deployment by adding replica domain controller (DC), and SQL Always On Availability Group (AG) replica node. After a couple of weeks spent consuming different documentations, videos, thoughts and importantly playing in Lab environment, I could get a Hybrid SQL Always Availability Group functional. Hybrid because two out of three SQL Nodes are on-premise Hyper-v VM and third one is Azure IaaS VM, same way there is an additional domain controller running on Azure VM. It’s really lengthy configuration process to cover step by step (click by click) here but I am describing an overview of each configuration milestones with drill down links to materials/documentation for each milestone.

Here is a diagram of the topology as I planned and configured. My on-premise environment is running on Hyper-v whereas Azure VMs are under typical 30 day trial open for all. RRAS server is configured as a gateway for on-premise systems and a VPN device forming an IPSec VPN tunnel from on-premise to the Azure gateway.

OS and Server Software versions:
SQL 2012 Sp1 on Windows server 2008 R2 sp1
DC on Windows Server 2008 R2 sp1
RRAS on Windows Server 2012 R2

Topology Diagram:

I am skipping the detailed steps for Windows OS, Domain Controller, WSFC Cluster, SQL Server, Always On installation/configuration as its well described in other resources available on internet. However, here is a listing for sequence reference:

  1. Three Hyper-v VMs running Windows Server 2008 R2 sp1 (DC, SQL Nodes)
  2. A Hyper-v VM running Windows Server 2012 R2 (RRAS for Site-to-Site VPN to Azure Gateway)
  3. Domain configuration by promoting first DC
  4. Join member servers to Domain (RRAS, SQL Node 1, SQL Node 3)
  5. Verify/Install hotfix mentioned in this support KB on all cluster nodes
  6. Install Failover Clustering on both nodes, create a cluster by consuming an unused static IP from on-prem subnet
  7. Install SQL Server on both nodes as standalone default instance, Enable Always On
  8. Create Always On Availability Group, Add a test DB to Availability Databases, A Listener pointing to unused on-premise IP address for Availability Group. Here is a screen capture video of end to end process.
  9. Test connecting by AG Listener name from either nodes, or ODBC connection from DC

Now let’s start at this state when on-premise SQL Always On AG is configured and working fine.

 

Site-To-Site VPN:

First step is to setup a VPN tunnel to connect on-premise network and Azure virtual network. Azure IaaS supports IPSec VPN tunneling and can be configured against various devices from Cisco, Juniper, and F5 etc. I used Windows Server 2012 Routing and Remote Access Service (RRAS) as VPN device in my test lab. Here is the link to document I followed, this well describes step-by-step method to configure VPN for RRAS and has reference to links talking about other devices.

 

Additional DC on Azure VM:

With Site-to-Site VPN in action, Azure IaaS is visible as an extension of Hyper-v virtual network. Depending on Windows Firewall configuration, one can verify/test connectivity by pinging VM on Azure from on-premise VM. After this connectivity is established, its matter telling your AD Domain about this new site from Sites and Service. Post this step Azure VM can be promoted as an additional domain controller on Azure VM. Detailed steps are outlined in this Azure documentation.

 

Add Azure VM as node to WSFC cluster and replica to SQL Always On Availability Group:

SQL 2014 introduces a pretty cool feature “Add Azure Replica wizard”, an extension to original “Add Replica” wizard of SQL Always On. This wizard can come in play once the site-to-site VPN is in place. This wizard takes care of creating VM in Azure, joining Domain, Adding as node to cluster and finally adding node as replica to Always On Availability Group. This “Add Azure Replica wizard” is SQL 2014 feature but can be used with SQL 2012 as well by installing management tools. Here is a post that walks through the wizard in detail.

However, I decided not use this wizard and took the approach to manually configure what wizard does. Below are the steps that worked smoothly to add my Azure SQL Node as a replica to existing Availability Group that I created on-premise.

  1. Create a VM in could from gallery image "Windows Server 2008 R2 SP1" under same cloud service and storage account where Azure DC is residing
  2. Verify/Install hotfix mentioned in this support KB
  3. Join to domain
  4. Install Failover Clustering
  5. Add the VM as a node to cluster, now AZSQL3(SQL AG Node 3) should be visible as 3rd node in on-premise Cluster
  6. Install SQL 2012 as standalone default instance, enable SQL Always ON
  7. Add an unused IP address from Azure VLAN subnet to existing AG listener
  8. Add node as replica for existing Availability Group
  9. Test connecting by listener name
  10. Test Failover to and from SQL Node on Azure VM.

 

Point-To-Site VPN:

Azure virtual network can also be configured to support Point-To-Site VPN so it allows connections from clients on open internet. Here is a detailed document describing step by step process to configure it. However, if you want to use the same Virtual Network on which you have already configured Site-To-Site VPN then you can start with enabling Point-To-Site connection on Azure Virtual network page, add address range and resume above document from “Create your certificates” section.

 

Observations:

A DNS record is created for cluster name pointing to IP specified while creating cluster. Listener name has two DNS records, one tied to on-prem subnet IP and other pointing to Azure virtual network IP.

What I described here as a solution is just an example or can be called a POC. Depending on environmental and business requirements there could be additional resources and configurations to support multiple combinations and permutations.

 

Important call out is the list of supported Microsoft Server Software, here is a support KB that announces supported MS products to run on Azure virtual machines. If your application is custom server application then it may require to pass through typical test cycle. Azure Status Dashboard is a handy utility that gives a niche service status view for each Azure services.

I am keen to uncover possibilities with IaaS and other MS Server products. Looking forward to spend some more time in near future and share the experience.

 

Hope this helps! Cheers!

Bhavesh Shastri

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed
  • Anonymous
    January 01, 2003
    great article. thanks
  • Anonymous
    September 16, 2014
    Excellent Article !! Well done
  • Anonymous
    October 20, 2015
    Microsoft Dynamics CRM announced support for CRM deployment on Azure IaaS Virtual Machines. Here is the