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
Best Answer
Unfortunately there is not, most of the client side connection string settings are not persisted or sent across the wire as they are specific to the connection library - one such is the
MultiSubnetFailoversetting.I'll point you to a post I made a while ago for someone that had a very similar issue and they wanted a way to track it down. It was very helpful for them, it may or may not be helpful for you but it's the closest you're going to get without asking to see the connection string of the application. The very minimum it'll show you if they are using the listener and which one. It gives some extra information so you can narrow down which client on the remote (note that this data can be spoofed, as I show).