SQL Server High Availability and Disaster Recovery on Azure

  • Articolo

The latest release of Azure has a focus on scalability and high availability in the cloud along with some new mobile features, However, one new feature that stands out for me is the AlwaysOn SQL Server 2012 functionality now available on Azure VMs. High availability and disaster recovery are essential components of any strategy to move database infrastructure to the cloud, but this new functionality provides solutions to use cases that will allow companies to not only build scalable and reliable cloud database architectures but to also extend their existing on premise infrastructure to the cloud for the purpose of reporting/BI or disaster recovery.

For a number of years companies have seen the Cloud as a new form of IT and maybe even a threat to traditional on premise IT, but with these new use cases being catered for it is vital that IT departments consider the benefits of the cloud as an extension to their existing infrastructure.

SQL Server Always On

SQL Server 2012 supports the concept of AlwaysOn availability Groups, which is Microsoft’s premier solution for enabling both high availability and disaster recovery with SQL Server 2012.  It supports multi-database failover, multiple replicas (5 in SQL Server 2012, 9 in SQL Server 2014), readable secondary replicas, configurable failover policies, backups on secondary replicas, and easy monitoring. This functionality is now available within Azure Virtual Machines. NOTE: enterprise edition of SQL server is required for the AlwaysOn features.

FMI: https://msdn.microsoft.com/en-us/library/jj870962.aspx and https://www.microsoft.com/en-us/sqlserver/editions.aspx

Once an availability group is create a single client connection string can be created using an availability group listener giving your applications a  single point of entry to your database group.

This is a new option in the HA story for SQL Server, but it’s important to understand how this fits into the complete story as there are numerous options for HA and disaster recovery for SQL Server on Azure Iaas.

High Availability and Disaster Recovery Options in Azure VMs for SQL Server

  1. DB Mirroring: This method allows you to mirror a database synchronously (for high availability) or asynchronously (for disaster recovery). For the disaster recovery option you can mirror to a separate data centre using certificates, however for HA the two databases and a witness server must use  a windows domain which cannot span multiple data centres, so must be within the same data centre. Another problem with mirroring is that there is only one mirror, and this cannot be read while mirroring is setup. You can use a snapshot, but this is a point in time. In other words you couldn’t leverage the mirror db for offline reporting. FMI: https://msdn.microsoft.com/en-us/library/jj870961.aspx
  2. Blob Storage: You can create schedules to backup your database and store it in BLOB storage, in a different data centre. This is a very simple technique and should be used as an absolute minimum. FMI: https://msdn.microsoft.com/en-us/library/dn133143.aspx
  3. AlwaysOn: This new capability allows for two synchronous and two asynchronous replicas, providing both a disaster recovery and high availability solution in one. This solution does require a windows domain and relies on enterprise edition of Windows and SQL Server. Another key advantage is that read-only replicas can be taken and used for reporting solutions without having to create snapshots. All of this can be managed for multiple databases within a group rather than just one at a time. FMI: https://msdn.microsoft.com/en-us/library/dn249504.aspx

This new feature presents a really exciting use case for Windows Azure as a DR solution for your on premise database infrastructure. Imagine you use AlwaysOn internally for high availability, so you have automatic failover between a primary and a secondary server on premise, you can now connect this on premise infrastructure to Azure using the Virtual Networking tools and create a read replica on azure. You now have a DR solution that reduces the need to extend your on premise, ships the replica offsite to a different data centre and allows you to take advantage of an Opex commercial model.

For a full description of all the new features you can checkout Scott Guthrie’s blog here:

https://weblogs.asp.net/scottgu/archive/2013/08/12/windows-azure-general-availability-of-sql-server-always-on-support-and-notification-hubs-autoscale-improvements-more.aspx