Art of the DBA Rotating Header Image

A Month of SQLPS: AG Listeners

In order to properly communicate with an Availability Group, we need an AG Listener. Straight from the Microsoft documentation, a listener is a Virtual Network Name that represents the AG, allowing applications to connect to it instead of directly to the SQL instance. The listener will handle routing to the appropriate replica within the AG and allow for connections to be consistent regardless of failovers and node changes.

To create a listener, we need a unique name within the domain and an IP address for each subnet the AG lives in. Creating it in your SQL Server Management Studio is just a matter of entering the information when creating a new listener. The GUI is under the Availability Group Management section, where you can add a new listener. Now, there’s a bit of a gotcha with creating the listener. It doesn’t really create SQL Server objects, but instead cluster resources. This means that while can create it in SSMS, after it is created we have to manage it through the Failover Cluster Manager.

Anyway, we’re here to talk about how to manage this using the SQLPS cmdlets, right? There are three cmdlets for listeners:

Get-Command -Module SQLPS *SqlAvailabilityGroupListener*

SQLPS-19-1

We’ll look at New-SqlAvailabilityGroupListener first, used to create a new listener. For this cmdlet we’ll need either the provider path for the Availability Group or the Smo.AvailabilityGroup object. Additionally, we also need the name, IP address, and TCP port for the listener. The name is easy enough, it can be any unique name, but we have options for the IP address: declare the address to be acquired via DHCP or give it a static IP address combined with a subnet mask. Personally, I prefer static IP addresses for my listeners and I tend to name my AG listener after the AG itself:

New-SqlAvailabilityGroupListener -Name 'ENTERPRISE' -staticIP 192.168.10.101/255.255.255.0 -Port 1433 -Path 'SQLSERVER:\Sql\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE'

If you look at the help file for this cmdlet, you’ll notice the -StaticIP argument will take an array. This is becausewe could have multiple IP addresses for the listener if the AG spans multiple subnets. Note, you can still only have one IP address per subnet, so keep this in mind as you provision your listener.

After the listener is created, we can view the listener in a few places. First is in SSMS under Availability Groups. The second is within the Failover Cluster Manager, as the cmdlet creates cluster resources. Finally, we could look at it using the provider:

SQLPS-19-2

Set-SqlAvailabilityGroupListener will only allow you to do one thing: Change the port the listener uses. You can not change addresses, only add and remove them. You also can not change the listener name, only remove it and create a completely new listener. To use this cmdlet, you’ll need the provider path or Smo.AvailabilityGroup object for the AG, specifically on the primary node.

Set-SqlAvailabilityGroupListener -Path SQLSERVER:\SQL\KIRK\DEFAULT\AvailabilityGroups\ENTERPRISE -Port 1433

Add-SqlAvailabilityGroupListenerStaticIp allows us to add additional static IP addresses to the listener. This cmdlet will also create cluster resources for these addresses, just like New-SqlAvailabilityGroupListener, and must be unique per subnet for the listener.

If you need to remove any of these objects, you’ll probably want to do it through the Failover Cluster Manager or the Failover Cluster cmdlets. You can use Remove-Item if you reference the provider path of the listener and this will drop the entire object. However, managing the IP addresses of the listener itself can only be done by managing the individual cluster resources.

Using these cmdlets will give us a functioning Availability Group and allow us to connect to it using the AG name. With this complete, there is one more step to make an AG 100% functional: install a database into the AG and enable it for synchronization. In the last post for this week, we will cover the cmdlets for enabling and managing databases within an AG.

Leave a Reply

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