• Category: Microsoft

SQL 2014 Availability Group with Windows 2016 Cloud Witness

What this guide covers

This configuration will include 3 SQL replicas across two sites\subnets.  2 will be located in the primary location where 'automatic failover' will be configured.  This will allow the primary node to automatically fail over to the secondary node at the primary site much like a mirror does.  The third node will reside in the secondary location in the case of site failure and will require a manual failover.

Drawing

 

Introducing Cloud Witness

In today's world most enterprises leverage a secondary site for disaster recovery in some fashion and believe it should be a no-brainer for SQL as well.  With SQL mirroring, this was relatively simple by splitting the mirror and the SQL witness across the two locations for a quick DR configuration. But, in the last couple of years Microsoft has been pushing users to SQL availability groups that would piggy-back off of Windows failover clustering.  

Windows clustering is much different from mirroring since it utilizes a quorum to keep the cluster up and running vs a dedicated witness server.  If the cluster loses enough members it will stop, causing SQL to stop running accross all nodes. In a single site scenario this would be fairly easy to protect by adding a fileshare witness that would allow for a single node failure.  The big limitation came in when adding a second site since the fileshare would live in one of the two physical sites.  And in a quorum configuration, if the site that included the fileshare witness would go down it would bring the entire cluster down!  

To protect quorum the fileshare needs to reside in a third location which many enterprises don't have readily available.  One possibility is to use the cloud to host the fileshare but this would require infrastructure such as a VPN tunnel back to the provider.  This requires a lot of planning and work for a tiny file but extremely critical to keep the DB up an running.  To resolve this headache Microsoft introduced 'Cloud Witness' in Server 2016 which allows the fileshare to reside in Azure, but without any of the other infrastructure.  Just throw in the Azure API key and Server 2016 will take of the rest for you! No VPN needed! This guide will go over the steps to deploy a Windows Server 2016 failover cluster along with a SQL Enterprise 2014 availability group.

Requirements

  • 3 Windows Standard or Datacenter 2016 Servers (2 will also work)
  • SQL 2014 Enterprise (2 will also work)
  • 2 IPs per subnet (For this guide you will need 4 in addition to the server IPs)
  • Account that has access to create computer objects and modify domain permissions
  • Azure access
  • Service account to run SQL

Configure Azure Access

To create the cloud witness an Azure account and API key is required.

  1. Login to your Azure portal
  2. Create a new 'storage account'.  Feel free to adjust to reflect environment.
    azure 01
  3. After the account is created copy the API key needed for the next steps
    azure 02