How to Change the Port Number of an Availability Group Listener

Changing the port number of an existing AG listener is very simple. As long as you don’t have any blocking on the firewall side of things there should be no issues with existing connections.

Remember listeners don’t use the SQL browser service so if you set up a listener with a non standard port you will need to specify the port in the connection string.

Example of changing a listener port

The syntax for changing an AG listener to port 1433 is:

USE [master] GO ALTER AVAILABILITY GROUP [** AG name here **] 
MODIFY LISTENER N'** Listener name here **' (PORT=1433); 
-- or change to whatever port you are wanting to change to. 

I have tested this with live connections and if all goes well there should be no problems with currently connected users/applications. Make sure your firewall rules are set to allow traffic on whatever ports you are going to be using.

There are no issues with using port 1433 for your listeners if you are running on the default instance and no other services (apart from SQL) are using that port.

Query to show existing Listener configuration:

SELECT AS [Availability Group], agl.dns_name AS [Listener DNS Name], 
agl.port AS [Port] --select * 
FROM sys.availability_group_listeners agl 
INNER JOIN sys.availability_groups ag ON agl.group_id = ag.group_id 
ORDER BY, agl.dns_name 

Query to show information about current connections

You can run this to see what ports connections are using on the SQL Server.

SELECT local_tcp_port, c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, 
s.host_name, s.program_name, s.client_interface_name, s.login_name, s.nt_domain, 
s.nt_user_name, s.original_login_name, c.connect_time, s.login_time ,client_tcp_port 
FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s 
ON c.session_id = s.session_id 

I hope this helps save you time on research.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.