SQL Server Availability Groups – AlwaysOn Availability Group Listener Port Selection

availability-groupslistenersql server

Simple question (I hope!)

Why would you use a non-default port for an Always On AG listener?

I haven't found a good post (yet) outlining advantages vs disadvantages.

Currently, I can think of three reasons to use something other then 1433:

Are there any others people can think of?

Best Answer

Following Sql Server security hardening guidelines.

Because sometimes the Windows Firewall, if enabled, and add the exception for this IP/Port.

Avoiding port conflicts (as per https://msdn.microsoft.com/en-us/library/hh213417.aspx#SelectListenerPort).

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string.

Being able to identify db calls to specific AG's quicker in network traces and such.

Since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

Why would you use a non-default port for an Always On AG listener?

In the “Port” field, choose an adequate port, something that is not being used. By Default sometime port sql server port 1433.

suppose that you are configuring the port ( let say 5122) After configuring the 5122 port in AlwaysOn availability group in listener. you must test through telnet like

“telnet <your listener name> <listener port>”

After checking the test connection of listener port number . if you shall get the error like

connecting to SQLServerName ... could not open connection to the host ,on port 5122 : connect failed

Note: Here SQLServerName is the Name of the Primary Replica Server Name.

In that case, check if you didn’t misspell something (the hostname or the port), if all looks good, you might check the Windows Firewall, if enabled, and add the exception for this IP/Port. Another try is use the listener IP, instead f the computer name. If the connection using the IP works, you need to check why the name resolution is not working. Talk with the Domain/DNS/System Administrators in order to troubleshoot that.

If all looks good on Windows point of view, and if the instance/listener are really only and should be replying, you need to check with the Networking team of your company, on order to have this unlocked. Always pass to them the following information:

Source IP and hostname (from where you are connecting).
Destination IP and hostname.
Destination port.

For your further ref Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) and Here

Related Question