0

We use an SQL Server 2019 Always On setup.

2 sql server 2019 nodes in same subnet : server01, server02 1 sql server 2019 node in other subnet : server03

We decided to create only 1 availability group per instance. We created instances on all nodes : instance01 - port 66661 -> 1 availability group ag01 with listener listener01 - port 55551 instance02 - port 66662 -> 1 availability group ag02 with listener listener02 - port 55552

Our applications connect via the listener : listener01\instance01 listener02\instance02

This only works by allowing the following ports on firewall :

  • For the sql server node ips (server01/02/03):

    • TCP 66661 an 66662 and UDP 1434 for both sql server engine and sql server browser
  • For the listener ips :

    • listener01 - TCP 55551 and UDP 1434 for both listener and sql server browser
    • listener02 - TCP 55552 and UDP 1434 for both listener and sql server browser
  • But also and here comes my question :

    • listener01 - TCP 66661
    • listener02 - TCP 66662

So for the listeners, you also have to "open" the ports of the sql server engine, while listeners are only listening on the listener port and "redirect" your call to the primary.

So everything runs fine but I don't have a good explanation why I have to open the ports of the sql server engine 66661/66662 for the corresponding listener ips ?

3
  • 1
    None of those ports should work since they aren't even valid port numbers. Nov 9, 2023 at 13:19
  • Of course we used another range but this was just for the example. I don't like to put internal info on the internet...
    – PekeDBA
    Jan 3 at 8:55
  • Port really isn't internal or sensitive information, no matter how much someone in infosec tells you it is. In this case, it caused you waiting months for someone to give a good answer. If you wanted to change the numbers, that's fine, choose ones that are real, otherwise people will tell you it's wrong. Jan 3 at 11:32

1 Answer 1

1

listener01\instance01

You should never use instance names with a listener. Always use ports.

You can also designate a non-standard availability group listener port. However, you also need to explicitly use the target port in your application connection string when connecting to a listener. You also need to open permission on the firewall for this port.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overview?view=sql-server-ver16

The reason this is happening is that you're asking the SQL Browser for the port, and it always gives you the instance port not the listener port.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.