We’re excited to announce that AlwaysOn Availability Groups are now fully supported on Windows Azure Infrastructure Services. Today we updated our official documentation indicating that we now support Availability Group Listeners for SQL Server 2012 on Windows Server 2012.
Availability Groups and Listeners on Windows Azure Infrastructure Services
Availability Groups, released in SQL Server 2012, is our flagship solution for high availability and disaster recovery. It offers many advantages over Database Mirroring: multi-DB failover, multiple replicas (5 in SQL Server 2012, nine in SQL Server 2014 CTP1), readable secondary replicas (to offload reporting and business intelligence applications), configurable failover policies, backups on secondary replicas, and easier monitoring.
Until now, client applications wanting to connect to the primary replica of an availability group on Windows Azure Infrastructure Services only had the option of specifying a Failover Partner (similar to Database Mirroring). This restricted the configuration of availability groups to only two replicas (one primary and one secondary) and these couldn’t be configured as readable when acting as secondaries. The new support for Availability Group Listeners removes this limitation.
Client applications can now connect to the primary replica using a single name, irrespective of the number of replicas in the Availability Group or whether these are configured as readable when acting as secondaries.
That is, the client application only needs to specify the Listener name of the Availability Group, instead of the server name, and connections will be routed to the primary replica (even after automatic of manual failovers).
For example: Server=tcp:ListenerName,1433;Database=DatabaseName;
Let’s look at two scenarios for Availability Groups and Listeners: High availability of a SQL Server running in Windows Azure Infrastructure Services and Disaster recovery of a SQL Server running in Windows Azure Infrastructure Services.
High availability of a SQL Server running in Windows Azure Infrastructure Services
In this scenario, an availability group is configured to ensure high availability of SQL Server in Windows Azure Infrastructure Services. This allows for detecting failures at the SQL Server level (e.g. service being down or hung) and reduces downtime during Windows Azure’s Service Healing and Upgrade operations, or patching of the guest OS.
The scenario is depicted in the figure below. An availability group has been configured with five replicas (each in its own Windows Azure VM). two replicas (primary: P and secondary: S1) are configured for automatic failover to guarantee high availability. In addition, three more secondary replicas (S2, S3, and S4) are configured as readable secondaries to offload some reporting applications.
An OLTP application connects to the primary replica of the availability group using the availability group Listener. Its connections are routed to the primary replica, even if there is an automatic failover to the secondary replica S1 or a manual failover to any of the other secondary replicas.
The reporting applications could connect directly to the readable secondary replicas; however, a more scalable approach is to leverage the Windows Azure Load Balancer to round robin connections between them. This distributes read workloads evenly across the replicas.
Disaster recovery of a SQL Server running in Windows Azure Infrastructure Services
In this scenario, an on-premise availability group is expanded with one or more secondary replicas on Windows Azure Infrastructure Services to support recovering from a disaster. The disaster may range from a gateway hardware failure to a catastrophe, like the flooding caused by Hurricane Sandy in New York last year. In that case, companies who had access to a disaster recovery site, failed over there (some using SQL Server Availability Groups). However, maintaining a disaster recovery site is expensive (site rent, site maintenance, hardware, and operations people).
A much cheaper alternative is to add one or more secondary replicas to on-premise Availability Group on any of the regions where Windows Azure Infrastructure Services is available (currently West US, East US, East Asia, Southeast Asia, North Europe, and West Europe).
Before you can add a secondary replica on Windows Azure, you’ll need to configure a site-to-site Windows Azure Virtual Network between your on-premise network and Windows Azure.
The scenario is depicted in the figure below. An on-premise availability group is configured with two replicas (P and S1) for automatic failover to guarantee SQL Server’s high availability. An OLTP application connects to the primary replica of the availability group using the availability group Listener. Its connections are routed to the primary replica, even if there is an automatic failover to the secondary replica S1.
The availability group has been extended with one more secondary replica (S2) on a Windows Azure VM. S2, like S1, continuously synchronizes transactions from the primary replica.
In case of a disaster impacting the on-premise replicas, it’s possible to manually failover to the secondary replica on Windows Azure, making this the primary replica of the availability group.
As part of the failover, the availability group Listener is automatically updated to route connections to the new primary replica on Windows Azure.
Besides disaster recovery, the secondary replica(s) on Windows Azure can be used to offload reporting applications and backups. This is valuable for companies that require maintaining backups outside of the data center for compliance reasons.
- Official Documentation: High Availability and Disaster Recovery for SQL Server in Windows Azure Virtual Machines
- TechEd 2013 presentation: SQL Server High Availability and Disaster Recovery on Windows Azure VMs