Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have set up an AlwaysOn with a primary and secondary replica on different subnets but I have been forced to use Registerallprovidersip set to 1. This causes the listener to only have 0 IP address in DNS which means I am at the mercy of DNS replication when a failover event occurs as it will take sometime for the listner's IP to update.
Has anyone in a similar situation found ways to improve failover time?
You should modify the registerallprovidersip setting in conjunction with changing the time to live (TTL) for the records.
Without modifying the default TTL, the registration lives too long. Sadly there is still a delay but dramatically reducing the TTL helps.
Ideally you should see what you can do to get the client connections to specify multi subnet failover and not have to do this workaround. But with the TTL being lowered, you should be fine, I've done that with several clients and it all works out alright. It still won't instantaneous.
You can see more about these settings working in conjunction with each other here.
I am in similar situation ... wherein we are at the mercy of DNS update which my company has a global policy of 20 mins. We have [HostRecordTTL] = 300 and [RegisterAllProvidersIP] = 0 due to legacy application.
Below script will generate commands for all the primary replicas (primary after failover) :
##### Author: Kin Shah ##############################################################
#run below tsql to update the cluster network name when the failover happens.
# In my company the global DNS cache updates every 20 mins ..
# This was used to re-register / force the Listener device register with the local DC
# registers the Network Name resources of the local cluster with a DNS server and does not interrupt cluster availability.
#CommandToRun_PowerShell column in the script output will provide the command to be run on the primary replica (the primary after failover) !
SELECT
'Get-ClusterResource ' +AGC.name+'_'+AGL.dns_name + ' | Update-ClusterNetworkNameResource' as CommandToRun_PowerShell-- this is the meat !!
, RCS.replica_server_name
, ARS.role_desc
, AGL.dns_name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
