Just over a year Microsoft announced the support of SQL Server AlwaysOn Availability Groups (AAG) on Microsoft Azure IaaS. Last month, Microsoft announced the support of SQL AAG between Azure Regions. This is a great news for great technology like SQL Server 2012 for highly available and disaster recovery scenario. SQL AAG released in SQL 2012 and enhanced in SQL 2014. AAG will detect anomaly which will impact SQL availability. When We will discuss how to do this in two blog posts:
- Part1: Design SQL 2012 AAG across Azure VNET and How to create Microsoft Azure VNET to VNET peering
- Part2: SQL, WSFC, Configure Quorum and Voting (SQL) and Configure AAG
Part1 SQL 2012 AAG across Azure VNET SQL 2012 AAG is designed to provide high availability for SQL database and Azure IaaS is great place where this technology can live. There are few benefits using Azure IaaS for this scenario:
- Security features from Azure as Cloud Provider. The security whitepaper can be found here
- Azure VM Security Extensions which means we can rest assure when VM is deployed, it is protected from day 1. Details can be found here
- Azure ILB to provide load balancing solution
- 99.90% SLA for VNET connectivity (VPN). This feature is backed up by two “hidden” Azure VMs in active-passive configuration
- Express Route (MPLS) for higher bandwidth requirement – we won’t discuss and use this feature in this blog posts
The architecture components for this scenario: 2 VNET on two different regions to avoid single point of region failure. We will call this VNET: SEVNET (Southeast Asia Region) and USVNET (US West Region). These VNETs will be peered. DC on each VNET to provide AD and DNS service. First DC on Southeast Asia region will be used as File Share Witness. 3 SQL Servers in AAG which 2 SQL will be at SEVNET on Azure Availability Set and 1 SQL will be at USVNET. The constraints for this scenario:
- Cloud Service cannot span across Azure VNET. For this scenario two Cloud Service will be used for SQL VMs
- Azure Availability Set (AS) cannot span across VNET and Cloud Service. Two SQL AS will be deployed
- Azure ILB cannot span across VNET. Only Primary and Secondary SQL will be load balanced on SEVNET
Diagram below illustrates the architecture:
Diagram above shows SQL AAG is configured across two Azure VNET. This configuration will give resiliency from full Azure region failure. AAG will be configured with 2 replicas (Primary at SEVNET, one replica at SEVNET for automatic failover and the other replica across region at USVNET configured for manual failover and disaster recovery in case of region failure at SEVNET). The listener will be configured o SEVNET which configured to route connections to primary replica. The scenario above also allows offloading read workloads from the Primary replica to readable secondary replicas in Azure region that are closer to the source of the read workloads (For example: reporting/ BI / backup purpose) Microsoft Azure VNET to VNET Peering Now let’s create 2 VNET on Southeast Asia region and US West region. Below is the configuration:
- SEVNET | Southeast Asia Region | Storage: sevnetstor | Address Space: 10.0.0.0/20 | DNS: 10.0.0.4
- USVNET | US West Region | Storage: usvnetstor | Address Space: 192.168.0.0/20 | DNS: 10.0.0.4
We will use Regional Virtual Network instead of using Affinity Group for this scenario which will enable us to use ILB for the future use. My colleague Scott Scovell wrote a blog about this a while ago. Create 2 Storage Accounts:
Create DNS Server 10.0.0.4 – I registered with DC name AZSEDC001 Create VNET Peering We will use Azure GUI to create VNET-VNET peering Create first VNET at SE Asia Region: Go to NEW>Network Services>Virtual Network>Custom Create> Enter VNET name: SEVNET and select Region Southeast Asia > Next > select DNS Servers: AZSEDC001 > check Configure a site-to-site VPN > On Local Network choose Specify a new Local Network > Next
Enter the name of local network as USVNET and specify the address space. On VPN Device IP Address just use temporary one and we will replace that with the actual Public IP Address of the Gateway.
Next – we will configure the IP range of SEVNET . The important bit: Click on Add Gateway Subnet
Next We need to configure the USVNET with the same way. Configure site-to-site connectivity with the local as SEVNET using its address space. Both VNET will be like below:
Next: We will need to create Dynamic Routing VPN Gateways for both VNET. Static Routing is not supported.
Once completed, get the Gateway IP address for both VNET and replace the temporary VPN IP Address on Local Networks with the actual Gateway IP address we just obtained.
The last step: Set the IPsec/IKE pre-shared keys for both VNET. We will use Azure PowerShell for this configuration. Firstly we will get the Pre-Shared keys to be used on our PowerShell script.
Please ensure You are on the right subscription. Always good habit to use select-azuresubscription -default cmdlet before executing Azure PowerShell script.
And That’s it! We should see the successful VNET to VNET Peering :
Part 2 we will deep dive on how to configure the SQL AAG on across both VNET