Recommendations and Best Practices When Deploying SQL Server AlwaysOn Availability Groups in Microsoft Azure (IaaS)
Introduction
Microsoft Azure virtual machines (VMs) with SQL Server can help lower the cost of a high availability and disaster recovery (HADR) database solution. Most SQL Server HADR solutions are supported in Azure virtual machines, both as Azure-only and as hybrid solutions.
There are important considerations and unique configurations for successfully deploying AlwaysOn availability groups in an IaaS environment. This blog lists the key considerations that should be addressed when deploying availability groups in Windows Azure.
Windows and Cluster in IaaS Best Practices
Heartbeat Recommendations
IaaS with SQL AlwaysOn - Tuning Failover Cluster Network Thresholds
When running Windows Failover Clustering in IaaS with SQL Server AlwaysOn, changing the cluster setting to a more relaxed monitoring state is recommended. Cluster settings out of the box are restrictive and could cause unneeded outages. The default settings are designed for highly tuned on premises networks and does not take into account the possibility of induced latency caused by a multi-tenant environment such as Windows Azure (IaaS).
Note: There is a known issue with Cluster Heartbeat settings being reset to default. To resolve this issue apply the following hotfix:
For Windows 2012 R2 and 2008 SP2
For Windows 2012
Changed cluster properties revert to default values on cluster nodes that run Windows Server 2012
Quorum Recommendations
WSFC Quorum Modes and Voting Configuration (SQL Server)
SQL Server AlwaysOn Availability Groups takes advantage of Windows Server Failover Clustering (WSFC) as a platform technology. WSFC uses a quorum-based approach to monitoring overall cluster health and maximize node-level fault tolerance. A fundamental understanding of WSFC quorum modes and node voting configuration is very important to designing, operating, and troubleshooting your AlwaysOn high availability and disaster recovery solution.
Quorum vote configuration check in AlwaysOn Availability Group Wizards
In this blog we dig deeper into the guidelines of adjusting the quorum voting in the Windows Server Failover Cluster (WSFC) for the availability groups and explain the reasons behind them with a specific example.
Failover Clustering and AlwaysOn Availability Groups (SQL Server)
AlwaysOn Availability Groups, the high availability and disaster recovery solution introduced in SQL Server 2012, requires Windows Server Failover Clustering (WSFC).
The overall health of a WSFC cluster is determined by the votes of quorum of nodes in the cluster. If the WSFC cluster goes offline because of an unplanned disaster, or due to a persistent hardware or communications failure, manual administrative intervention is required. A Windows Server or WSFC cluster administrator will need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration.
Static IP for DIP / VIP
Network Isolation Options for Machines in Windows Azure Virtual Networks
Application isolation is an important concern in enterprise environments, as enterprise customers seek to protect various environments from unauthorized or unwanted access. This includes the classic front-end and back-end scenario where machines in a particular back-end network or sub-network may only allow certain clients or other computers to connect to a particular endpoint based on a whitelist of IP addresses. These scenarios can be readily implemented in Microsoft Azure whether client applications access virtual machine application servers from the internet, within the Azure environment, or from on-premises through a VPN connection.
Storage Account
Storage Spaces - Designing for Performance
Storage spaces can provide high performance storage in Azure when preparation and configuration is done using best practices. However, there are variables to be aware of that can impact the performance of the disks deployed in a storage space.
For example, performance can become degraded if the number of highly used VHDs for standard tier virtual machines approaches 40. For more information review the Configuring Azure Virtual Machines for Optimal Storage Performance.
SQL Server best practices also should be referenced for guidance on optimizing storage and increasing IOPS performance by implementing multiple disks. For more information see the sections 'Windows Azure virtual machine disks and cache settings' and 'Data disks performance options and considerations' in Performance Guidance for SQL Server in Azure Virtual Machines . Also review the section 'I/O performance considerations' of Performance Best Practices for SQL Server in Azure Virtual Machines.
Also, consider using the script available at Automate the creation of an Azure VM preconfigured for max storage performance to create a Microsoft Azure virtual machine optimized for maximum storage performance.
SQL Server in IaaS Best Practices
Deploying SQL Server AlwaysOn Availability Groups in Windows Azure
Offerings The following range from step by step configuration to deploy AlwaysOn availability groups to completely automated deployment.
Use the new SQL Server 2014 AlwaysOn gallery.
Tutorial: AlwaysOn Availability Groups in Azure (PowerShell)
Tutorial: AlwaysOn Availability Groups in Azure (GUI)
Best Practices for SQL Server in IaaS
Performance Best Practices for SQL Server in Azure Virtual Machines
Review the Best Practices check list to ensure you implement SQL Server tuning options when running in Microsoft Azure (IaaS) environments.
Performance Guidance for SQL Server in Azure Virtual Machines
Review the Performance Guidance whitepaper for an in-depth analysis and recommendations for optimizing SQL Server performance when running in Microsoft Azure (IaaS) environments.
When deploying AlwaysOn availability groups, it is recommended that SQL Server is deployed using the Performance Guidance for SQL Server in Azure Virtual Machines whitepaper and the Performance Best Practices for SQL Server in Azure Virtual Machines guidelines in order to maximize the performance AlwaysOn availability groups running on .
Create Azure Availability Group Listener
Tutorial: Listener Configuration for AlwaysOn Availability Groups
AlwaysOn availability group listeners require unique configuration steps. Since Microsoft Azure virtual machines have static ip address restrictions, the availability group listener must be configured to implement your cloud service ip address for connectivity to your availability group databases.
Use the link Tutorial: Listener Configuration for AlwaysOn Availability Groups for step by step instructions on configuring the Azure availability group listener.
Testing your Azure Availability Group Listener
Once properly configured, an Azure availability group listener can be connected to from anywhere on the internet.
IMPORTANT: Testing the Azure listener on a workstation or server within the same cloud service hosting the SQL Server hosting the primary replica is not a valid test and will fail. The Azure listener is designed for connectivity from an application running in a different cloud service or connecting across the internet.
For example, on a workstation running in a different cloud service than the availability group, and on which SQL Server client tools are installed, use the following command line to test the connection to the SQL Server hosting the primary replica of your availability group:
sqlcmd –S "<CloudServiceDNSName>,<EndpointPort>" -d "<DatabaseName>" -Q "select @@servername, db_name()" -l 15
The DNS name can be found by viewing the dashboard of your cloud service hosting the AlwaysOn availability group using the Azure portal.
The EndpointPort is the public port you specified in step eight of the during setup of the Azure listener in Tutorial: Listener Configuration for AlwaysOn Availability Groups.
Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener
Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener
The Azure listener can be configured to route read intent connection requests. It requires unique configuration steps when deployed in Microsoft Azure. For more information and step-by-step instructions on how to configure ReadIntent routing using the Azure listener, review the Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener to properly configure your Azure Listener for read-only routing.
Comments
- Anonymous
April 07, 2015
Awesome all what i need :) - Anonymous
August 26, 2016
What are the recommendations for networking and heart beat? can you have two nick cards to separate that traffic?