Configure AlwaysOn Availability group Listener Part – 2

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

image

image

image

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 
            } 
    }

Leave a Reply

Your email address will not be published. Required fields are marked *