March 2, 2020 at 7:57 pm
Hi,
I'm looking after a cluster that has 2 instances; one for 2016 and one for 2017. The AG listeners are both configured to use port 1433 and everything is working fine?
Customer wants a separate instance of 2017 - how do listener ports relate to SQL ports? In the SQL port world named instances default to dynamic ports, which this new instance has done as well.
Also, do I need to create a dummy database to create the listener - it looks like the previous setups have done this and matched the database name to the listener name. Is that standard?
Thanks
March 3, 2020 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 5, 2020 at 12:18 pm
I found this thread: https://www.sqlservercentral.com/forums/topic/alwayson-listener-port-numbers-and-multiple-listeners
The existing setup for me is:
SQL 2017, Default instance: Instance port 1433, AG Listener port 1433
SQL 2016, Named instance: Instance port dynamic, AG Listener port 1433
SQL 2017, Named instance; Instance port dynamic, AG Listener port with 1433 errors out ("already in use")
Post #2 from the linked thread suggests the 2016 AG Listener should not work on 1433 because the 2017 default instance has the instance and listener already on 1433.
??
March 5, 2020 at 12:42 pm
well, many instances can share one listener port, however no instances should share the same port as is configured on the listener.
You said DB names have been matched to listener names, I'd check the SPN for the listener on #1 and #2 and they will most likely be different thus using the same port is not an issue anymore.
straight from the link you're referencing:
An availability group listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener. The DNS name of the listener must also be unique in the domain and in NetBIOS.
DNS Listener name && Port && 1 to n IP addresses and the combination of these three parts gets you an unique listener, this is similar to aliasing SQL Instances, they might be running all on the same IP but due to the DNS name you will see exactly one.
March 5, 2020 at 1:57 pm
well, many instances can share one listener port, however no instances should share the same port as is configured on the listener.
That is what I would expect, however the original config. (not done by me) was as above:
SQL 2017, Default instance: Instance port 1433, AG Listener port 1433
SQL 2016, Named instance: Instance port dynamic, AG Listener port 1433
Having discussed it with the customer I have just learned that the 2016 setup doesn't failover so I'm assuming because of the port conflict. As it turns out, although you get an "already in use" error when trying to use a conflicting port, it still actually applies it - you need to change it back to a non-conflicting one. It's not a hard stop error.
So they need to change the default instance port for 2017 to something other than 1433 (can you use dynamic for default instances - I hardly ever use default instances so cannot remember!!!), then all the listeners can use 1433. As applications will be connecting to the listener, those applications won't need to manually specify a non-standard SQL port.
As for DB names matching the listener names; the original setup has 1 listener for the entire instance, rather than a listener<>db relationship. So it looks like they created a dummy database which matches the name of the listener, for example:
Dummy db: DummyDB1
Listener: DummyDB1
And when creating the first AG they've used this database and then created the listener to match the DB name. No other availability groups have a listener set.
March 9, 2020 at 12:41 pm
PS: I assume it's not possible to have groups of databases directly share listeners?
The GUI doesn't allow you to select an existing listener for an AG, either an existing one of when creating one.
March 9, 2020 at 12:58 pm
That is what I would expect, however the original config. (not done by me) was as above:
SQL 2017, Default instance: Instance port 1433, AG Listener port 1433
SQL 2016, Named instance: Instance port dynamic, AG Listener port 1433
So they need to change the default instance port for 2017 to something other than 1433 (can you use dynamic for default instances - I hardly ever use default instances so cannot remember!!!), then all the listeners can use 1433. As applications will be connecting to the listener, those applications won't need to manually specify a non-standard SQL port.
You shouldn't need to do this. I have many servers hosting default instances on port 1433, each of which have multiple AG listeners, also on port 1433.
I don't like using dynamic ports for named instances though; why not switch to a fixed port?
Re the dummy databases, sometimes people create those to prevent alerts being generated (because if you create an empty AG, its health state is unhealthy/not synchronising).
March 9, 2020 at 3:12 pm
But do you have multiple SQL instances running on the same servers? This is the key thing...
March 9, 2020 at 3:44 pm
On a couple, but in most cases they are all named instances (so no default instance).
March 9, 2020 at 3:51 pm
Yeah that's the thing, normally all those named instances would be dynamic leaving 1433 for the listeners, but by the sounds of it your preference is fixed ports and I take it none of them (instances that is, not listeners) are using 1433 (unless you chose AG listeners to not also use 1433).
?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply