Microsoft SQL Server AOAG Failover Failback in Multi-Subnet environment

#mssqlserver #mssql #microsoft #giveback #community #hadr

If you create AG with Primary Replica only, the endpoint is not created. You have to use the mentioned below Script to create the AG.

CREATE ENDPOINT [Hadr_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

If you create Listner with TSQL or with Availability Group Wizard the RegisterAllProvidersIP will be set to 1 by default and if you create the listener with Client Access Point option using Failover Cluster Manager then the RegisterAllProvidersIP will be set to 0 by default.

Use the below PowerShell script to get and set the RegisterAllProvidersIP to 0 and HostRecordTTL to 300 (5 minutes) so that the application can keep the DNS record cache for 5 minutes and not the default 20 mintues.

Get-ClusterResource
Get-ClusterResource “AG” | Get-ClusterParameter
Get-ClusterResource “AG” | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource “AG” | Set-ClusterParameter HostRecordTTL 300