Microsoft SQL Server AOAG Contained AG in Multi-Subnet environment

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

Always Make sure while using the Contained Availability Group that your perspective should be Availability Group not the individual replica or instance as the system databases will be for that particular availability group only

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

Видео Microsoft SQL Server AOAG Contained AG in Multi-Subnet environment канала SQL Help Line