Diagnose Unexpected Failover or Availability Group in RESOLVING State

AlwaysOn availability groups use Windows Cluster to 1) detect the health of the SQL Server process that hosts the primary replica, and 2) to fail over the availability group resource, if configured to do so. During the time a health issue is detected, the availability group primary replica transitions from PRIMARY to the RESOLVING role…


Large Transaction Interrupted by Failover, Secondary Database Reports REVERTING

If a large transaction in an availability database on the primary replica is interrupted by a failover of the availability group, once failover has occurred, the database state on your secondary (old primary replica) reports to be in a NOT SYNCHRONIZING or REVERTING state for a long period of time. For example, consider a primary…


Troubleshooting the Add Azure Replica Wizard in SQL Server 2014

SQL Server 2014 introduced a new feature that automates the creation of a new AlwaysOn replica hosted in Microsoft Azure. This replica is added to an existing availability group in your environment and connected to your existing AlwaysOn deployment via VPN.     Index: Introduction: Pre-requisites: Known issues: Add Azure Replica Wizard – Virtual Network…


Determine Availability Group Synchronization State, Minimize Data Loss When Quorum is Forced

When Windows Cluster quorum is lost either due to a short term network issue, or a disaster causes long term down time for the server that hosted your primary replica, and forcing quorum is required in order to quickly bring your availability group resource online, a number of circumstances should be considered to eliminate or reduce data…


Enhance AlwaysOn Failover Policy to Test SQL Server Responsiveness

The AlwaysOn failover policy for AlwaysOn availability groups monitors the health of the SQL Server process hosting the primary replica. For example, one health check mechanism ensures that SQL Server is responsive. The SQL Server Resource DLL establishes a local ODBC connection and SQL Server responds to the session within the availability group’s HEALTH_CHECK_TIMEOUT setting, which is…


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…


Connection Timeouts in Multi-subnet Availability Group

THE DEFINITION One of the issues that generates a lot of call volume we see on the AlwaysOn team is dealing with connectivity issues to the availability group listener in multi-subnet environments. A “multi-subnet” environment is defined when the OS cluster used as the backbone for AlwaysOn has server nodes that are located in multiple,…


IaaS with SQL AlwaysOn – Tuning Failover Cluster Network Thresholds

Symptom When running Windows Failover Cluster nodes 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…


Create Azure Listener fails with "Unable to save property changes for <cluster resource name> The parameter is incorrect"

Symptom When creating a listener for an availability group hosted in Windows Azure virtual machines, the following error may occur. Unable to save property changes for <cluster resource name> The parameter is incorrect   Cause Creating an availability group listener for an availability group hosted in Azure virtual machines requires special steps for creating the…


Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener

The AlwaysOn availability group listener is supported in Windows Azure virtual machines (VMs), but requires unique configuration steps. For more information on deploying an AlwaysOn availability group listener in Windows Azure virtual machines, see the following link. Tutorial: Listener Configuration for AlwaysOn Availability Groups in Windows Azure http://msdn.microsoft.com/en-us/library/windowsazure/dn376546.aspx Configure Read-Only Routing with a Public Load…