Configuring Always On availability groups - AWS Prescriptive Guidance

Configuring Always On availability groups

If you’re using native client libraries for SQL Server version 2012 and later, and .NET Framework 4.5 libraries, you can use the MultiSubnetFailover parameter to change the connection behavior. We recommend that you set this parameter to TRUE. This will enable faster failover with Always On availability groups.

Note

If you have legacy applications that cannot use the MultiSubnetFailover parameter, you can place a Network Load Balancer in front of your SQL Server instances. The balancer uses a health check that determines which SQL Server database is active and sends traffic to the instance that currently hosts that database. The load balancer spans one or multiple Availability Zones. You can use a dedicated port such as 59999 for the health check, and then modify the cluster group parameter to respond to that port. This enables you to reduce SQL Server failover time to approximately one minute without using the MultiSubnetFailover parameter. For detailed instructions, see the blog post Reduce failover times for SQL Server on Amazon EC2 instance using Network Load Balancer.

Two settings affect how the availability group listener is registered with DNS: RegisterAllProvidersIP and HostRecordTTL.

Set RegisterAllProvidersIP to true when using Always On availability groups

We recommend that you set RegisterAllProvidersIP to 1 (true). When the availability group listener is created with RegisterAllProvidersIP set to 1, all IP addresses for that listener are registered in DNS. When RegisterAllProvidersIP is set to 0 (false), only one active IP is registered.

In case of failover, when the primary replica moves from one subnet to the other, the old IP address is unregistered, and the new IP address is registered. The DNS is updated with the new IP when the availability group listener comes online. However, client systems won’t resolve the listener name to the new IP address until the currently cached entry expires.

Set HostRecordTTL to 60 or less when using Always On availability groups

The HostRecordTTL setting controls the Time to Live (TTL) for cached DNS entries. The default value is 1200 seconds. We recommend that you change HostRecordTTL to a much lower setting (60 seconds or less). This causes the cached value to expire sooner, so in case of failover, client systems can resolve the new IP more quickly.

Disable automatic failback for the Always On cluster group

Verify that automatic failback is disabled for the Always On availability groups in the Windows Cluster Manager.