Back

TechnologyFeb 22, 2016

Disaster Recovery With SQL AlwaysOn Availability Groups and Azure Site Recovery

Bryan Sakowski

In a previous blog post, I discussed the ability to integrate VMware-based infrastructure with Azure Site Recovery (ASR) for Business Continuity and Disaster Recovery (BCDR) planning. That, in addition to bare-metal and Hyper-V server replication, provides a flexible and powerful tool for protecting server infrastructure. When working with a multi-tiered application, ASR can quickly and easily accommodate web and application servers with this model. However, when it comes to the database tier, we frequently utilize configurations like SQL Server AlwaysOn Availability Groups (AGs) across multiple servers for high availability and added performance. ASR now natively supports SQL AlwaysOn failover orchestration to address this scenario.

Picture1

Consider a typical enterprise application such as SharePoint or Dynamics CRM. As is the case with many other first- and third-party enterprise apps, Microsoft SQL Server provides the database tier, and it allows high availability deployment in the form of AlwaysOn Availability Groups as the best-in-class high availability option to keep those apps running in the event of a failure. However, site failover in a disaster recovery scenario introduces a problem—all AG replicas going offline and coming up in another site breaks quorum in the process, and a multi-tiered application needs to be brought up in a certain sequence to fulfill dependencies and make sure all services start correctly. Simply adding AG replica servers as VMs in a recovery plan would produce unpredictable results at best. Alternatively, with ASR coordinating failover to an AG Replica in Azure, the database infrastructure stays in line with Microsoft best practices, and the rest of the application components can be brought online in the correct order.

prerequisites

The scenario under consideration is protection of an on-premises SQL Server AlwaysOn Availability Group to Azure. Please note that the feature is currently in preview, and items are subject to change. To lay the groundwork, you will need:

  • A SQL AlwaysOn Availability Group configured in an on-premises datacenter

  • An Azure virtual network, with either a site-to-site VPN or ExpressRoute connection to the on-premises network

  • An Active Directory Domain Controller replica in Azure, extending your AD DS domain into your Azure environment

  • At least one Azure IaaS VM with SQL Server Enterprise installed (you can deploy from an Azure Marketplace template, such as this SQL Server 2014 SP1 Enterprise image), added to the AG as an asynchronous replica

  • SQL PowerShell module installed on all SQL servers

  • VM Agent installed on Azure VMs

  • A SQL AG listener configured with an Azure internal load balancer (ILB), with the app tier configured to connect to the database tier using this listener

Additional details are available here and here. Your next steps will depend on whether your environment is managed by System Center Virtual Machine Manager (VMM) or not.

implementation

vmm environments

When working with a VMM-managed environment, you can manage SQL protection directly within the ASR vault in the Azure portal. At the time of this writing, you must administer this through the classic Azure Management portal. From here, you will be able to add SQL servers, SQL AlwaysOn AGs, and create recovery plans against your VMM environment for BCDR orchestration.

When building your recovery plans, you will need to include your VMM server as the source and Azure as the target. Next, you can add your web and application tier servers as applicable, as well as your SQL AG(s). Finally, you can customize the sequence of operations for recovery, typically initiating your SQL AG failover first to make your database tier available to your web and application tiers.

Failover and failback options include:

  • Planned Failover

    • A no data loss option, achieved by setting the AG availability mode to Synchronous before triggering failover, that allows for simple failback to the former primary on-premises SQL AG replica (a planned failover in the opposite direction).

  • Unplanned Failover

    • A failover initiated in asynchronous availability mode, which requires a reverse replication process prior to a planned failover in the opposite direction to restore the on-premises primary replica.

Modernize applications and support business initiatives with Microsoft Azure

Explore Our Microsoft Consulting Services  →

other environments

Without VMM integration, you can still use scripting and Azure automation runbooks to coordinate SQL AG failover with ASR. Through a combination of SQL PowerShell scripts on Azure blob storage, Azure automation runbook workflow, and Azure VM custom script extensions, automated SQL AG failover can be added as a scripted step to a recovery plan. Sample scripts are available here as a starting point for an unplanned failover.

Failback in this scenario would require reverse replication from the new SQL primary replica in Azure to the on-premises replica(s), along with a corresponding reverse recovery plan.

meeting business objectives

The integration of SQL AlwaysOn Availability Groups is an exciting addition to Azure Site Recovery that adds a huge amount of flexibility. It is now possible to meet ever-more-demanding RTOs, even with complex, multi-tier on-premises applications. When analyzing the impact of your critical SQL-backed systems, consider how integrating ASR into your BCDR planning can reduce downtime and improve the bottom line for your business.

Do you want to explore options for extending your infrastructure to the cloud? Credera has extensive experience in designing, planning, and implementing cloud solutions. If you have questions about this blog post, points of view, or IT infrastructure, please leave a comment below, tweet us @CrederaIT, or contact us online.