Availability Group Listener Configuration
An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. The client connection string does not need to be modified to connect to the current location of the current primary replica.
An availability group listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener. The DNS name of the listener must also be unique in the domain and in NetBIOS. When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency. A client uses DNS to resolve the VNN into multiple IP addresses and then tries to connect to each address, until a connection request succeeds or until the connection requests time out.
#Create Availability Group Listener
--Listener configuration with single sub-net-- USE [master] GO ALTER AVAILABILITY GROUP [AG Name] ADD LISTENER N'Listener Name' (WITH IP ((N'192.192.0.14', N'255.255.255.0')) , PORT=1522); GO
Powershell:
#Creating AlwaysOn availability group Listener Import-Module SQLPS string[] $ServerList= "SQL03GOSGPRDSQL02" #SQL Instance Name on Availability Group is Online $Agname='GLBAGSQL0203' #Availability Group Name $Port='1522' #Mention Listener Port Number (By Default it listens on 1433) $ListenerName='GLBAGSQL0203LST' #Listener Name $StaticIP='192.192.0.14' #Static unique IP for Listener) $Subnet='255.255.255.0' #Listener IP Subnet $StaticSubnetIP=$StaticIP+'/'+$Subnet $serverObjects = @() $replicas = @() foreach ($Server in $ServerList) { write-host '' write-host '[ '$Server' ]' -ForegroundColor Yellow Write-Host "[-]Creating SMO Object for Server: $Server" `n $serverObject=New-Object Microsoft.SQLServer.Management.SMO.Server($Server) if ($serverObject) { Write-Host " |-SMO Object for Server: $Server created successfully" -ForegroundColor Yellow `n } #Create Availability Group Listener Write-Host "[-]Creating Availability Group Listener for : $Server" `n $Path='SQLSERVER:SQL'+$Server+'AvailabilityGroups'+$Agname Write-host $Path $sqagl=New-SqlAvailabilityGroupListener -Name $ListenerName ` -StaticIp $StaticSubnetIP ` -Path $Path if ($sqagl -eq $null) { Write-Host " |-Availability Group Listener $Agname created successfully" `n } Write-Host "[-]Setting Availability Group Listener for : $Server" `n $sqaglpath='SQLSERVER:SQL'+$Server+'AvailabilityGroups'+$Agname+'AvailabilityGroupListeners'+$ListenerName $saglp=Set-SqlAvailabilityGroupListener -Port $Port ` -Path $sqaglpath if ($saglp) { Write-Host " |-Availability Group Listener for : $Server configured successfully" `n } }