Availability Groups is a great solution for providing both high availability and disaster recovery for SQL Server 2012 and 2014 deployments. But for many, the cost of a secondary site can be a roadblock to providing disaster recovery resiliency.

With Microsoft Azure, we can leverage the efficiencies of the cloud to avoid a lot of those costs associated with having our own secondary site but still have a robust disaster recovery solution that integrates with our on-premises, high availability solution.

Microsoft Azure allows us to easily spin up a SQL Server VM in a Microsoft data center and join that VM to an on-premises Availability Groups deployment. This provides us with a copy of our databases in Azure along with a SQL Server instance that is ready to take over in the event our primary site experiences a complete failure.

A simple, on-premises Availability Groups deployment typically consists of a domain controller and two servers configured in a Windows Server Failover Cluster (WSFC) with each node hosting a SQL Server 2012/2014 stand-alone instance. WSFC is required as the foundation for an Availability Groups deployment but the solution does not require shared storage like a typical Failover Cluster does.

Availability Groups replicates the databases and data changes from the primary replica to each secondary replica which eliminates storage as a single point of failure. In a two-node solution, a file share would also be added to provide quorum to allow for automatic failover between the synchronous replicas residing on-premises which acts as our high availability solution.

Below is a depiction of this typical configuration on-premises. For detailed step-by-step instructions for setting up an on-premises Availability Groups deployment see here.

davidpugh1

Building on this local high availability solution, we can now extend our Availability Groups configuration to Azure, which will provide protection against failure of the on-premises site. We can begin by using the Azure VM Image Gallery to provision and deploy a SQL Server VM within an Azure data center in just minutes. During setup, we need to choose a storage account, subnet and cloud service for the VM as well as open the appropriate endpoints for the SQL Server instance.

In order to allow our new SQL Server instance in Azure to communicate with the on-premises servers, we need either a VPN connection configured or an Azure ExpressRoute connection. Check here for step-by-step instructions on creating a site-to-site VPN between on-premises and Azure.

Once the VPN is configured, the Azure VM can be joined to the on-premises domain and joined to the Windows Server Failover Cluster.

With this foundation in place, the SQL Server instance in Azure can be added to the Availability Groups configuration and begin receiving the replicated databases. Replication could be configured for synchronous or asynchronous replication. But to avoid any performance-related latency issues, the typical configuration would be asynchronous replication when traversing sites.

Below is a depiction of the on-premises configuration extended to Azure.

davidpugh2

Lastly, for application connectivity we will configure the Availability Group Listener to support application redirect after failover occurs automatically or manually. The Listener acts as the virtual network name for the Availability Group allowing for applications to connect via the Listener DNS name and be routed to the primary replica no matter which node is acting as the primary at that point in time.

In a single site deployment, the Availability Group Listener configuration is a fairly straightforward process as you are only dealing with a single subnet. But when extending the solution to Azure, there are a few additional steps to support failover between sites. Here is a step-by-step guide for configuring the Listener properly in this type of hybrid deployment.

Keep in mind, in the event of a complete site failure; we would also need to have a domain controller and DNS server in Azure to provide authentication and routing if the on-premises site were lost.

If you are interested in exploring SQL Server Availability Groups and Azure further, I encourage you to test it out for yourself using the free SQL Server Evaluation and Azure Trial. Please feel free to leave any questions in the comments section or reach out to your CDW Account Manager to coordinate a deeper discussion.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>