locked
Always on listener on named instance. Do I have to specify the port while connecting a listener created on a named instance? RRS feed

  • Question

  • Hi all. Here again.
    Scenario: I have to consolidate several physical servers with one SQL server instance install on each one. I decided to consolidate all in one server installing different named instances. In order to avoid application connection string change I had the idea of configuring different listener pointing to different alwayson group.

    To meet this requests, I configured the cluster, I installed a principal SQLServer Instance and configured the alwayson groups with the required listeners.

    All ok so far, alwayson is great.

    So, I installed a named instance, I configured the necessary alwayson groups and the related listener but...

    NOTE: The db in the principal and in the named instance have the same name.

    If I connect the listener pointing the ag belonging the named instance I connect to the principal instance!

    the only way I have to connect to the named instance is to specify the port that must be different from the 1433 as usual.

    Anyway, why I cannot use the 1433 port if dealing with a different ip?

    Listener are the following:

    Named instance
    10.100.21.100 : 1455

    Principal instance
    10.100.21.50  : 1433

    Thankyou

    Wednesday, May 7, 2014 2:37 PM

Answers

  • You can have two different AG Listeners on port 1433, with each belonging to a different instance. 

    The thing you're missing is that one of your instances is a standalone instance listening on port 1433 on ALL IP addresses.  Look in the log and you'll see see something like

    2009-07-15 17:40:06.39 Server      Server is listening on [ 'any' <ipv4> 1433].

    This means that if Windows adds a new IP address, that instance will be listening on port 1433.

    In your configuration, when the cluster brings the AGL online, port 1433 on the new IP is used by the default instance, even though the AGL belongs to another instance. 

    This doesn't affect FCIs, as they always listen on only IP addresses that are dependencies of their cluster resource group's client access point.

    To remedy this, just configure the instances to listen on specific IP,Port pairs, and turn off "Listen All":

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, May 14, 2014 6:20 PM
  • This is the solution, use the SQL Server Alias :

    Good article : http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:44 AM
    Friday, May 9, 2014 11:58 PM
  • In order to connect to a network resource, you need to provide an IP address and a port number. In a standalone SQL Server instance, there can only be one combination of an IP address-port address. However, since the Availability Group listener name is using a different IP address, it has its own range of port numbers in of itself, 1433 included. This means that now your physical server has two port 1433 - one for the physical IP address and the other for the Availability Group listener name IP address. This is similar to having multiple websites in a single machine - each website has its own IP address and port number which then gets assigned to the physical network adapter.

    If port 1433 is already being used when you create the Availability Group Listener Name you can try flushing the DNS cache and/or failing over the Availability Group to the other replica.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:44 AM
    Wednesday, May 14, 2014 4:54 PM

All replies

  • Hello,

    I'm somewhat confused by your setup. When the listener is defined, it follows the availability group that it was created for and the listener "listens" on a specific port specified during creation (or if modified later).  The Listener points to the primary replica, whatever instance that may be and those instances are defined as replicas in the AOAG DDL which has the name and port number (if needed) of every instance that AG could belong to.

    You'll need to give us a better picture of how this is setup, as currently it doesn't make to me exactly how you're connecting and to which nodes own what, etc, etc.


    Sean Gallardy | Blog | Twitter

    Thursday, May 8, 2014 11:56 PM
    Answerer
  • 1433 port is using only for default instance, If you are dealing with named instance port should be some thing else.

    You can add multiple listener using different port. If you want to make SQL listen to multiple ports go through below link

    http://blogs.msdn.com/b/sqlalwayson/archive/2012/02/03/how-to-create-multiple-listeners-for-same-availability-group-goden-yao.aspx

    If still facing error post the error detail ...

    --Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Friday, May 9, 2014 2:52 AM
  • Hi all.

    I reviewed the architecture installing only named instances and I solved.

    Anyway, i'm going to explain better my configuration in order to understand where I went wrong.

    I have two servers, SQLCLU01 and SQLCLU02 partecipating to AVSQLCLU cluster

    Each server has two SQL instances, the principal SQLPROD and the named instance SQLTEST

    Each instance contain the OneToOne database.

    I created an AG named AgOneToOneProd laying on the SQLPROD instance with its OneToOne listener described here down (sys.dm_tcp_listener_states;)

    7 10.100.21.50 1 1433 0 TSQL 0 ONLINE 2014-05-07 10:23:04.113


    DnsName:OneToOne
    Port:1433
    IpconfigurationFromCluster:('IP Address: 10.100.21.50')

    So I tried to create an AG named AgOneToOneTest laying on the SQLTEST named instance with its OneToOneTest listener but the port 1433 is unavailable even if the listener IP is different. I managed in creating the listener changing the port like this


    7 10.100.21.100 1 1455 0 TSQL 0 ONLINE 2014-05-07 10:36:05.347

    DnsName:OneToOneTest
    Port:1455
    IpconfigurationFromCluster:('IP Address: 10.100.21.100')

    So, the issue is:

    Connecting to OneToOne.mydomain.local i can see the OneToOne database and crud operation are performed against the SQLPROD instance.

    But connecting to OneToOneTest.mydomain.local the OneToOne database I'm connected to is still the one belonging to the SQLPROD instance.

    The only way to let my application connected to the SQLTEST instance is to specify the port in the DSN.

    Anyway, summary here, issues are bolded

    OneToOne.mydomain.local -------> SQLPROD

    OneToOneTest.mydomain.local -------> SQLPROD

    OneToOneTest.mydomain.local,1455 -------> SQLTEST

    The same by IP

    10.100.21.100,1455 --------> SQLTEST

    10.100.21.100 --------> SQLPROD


    and.....

    10.100.21.100,1433 --------> SQLPROD

    May be... Do I missed some best practices in creating listeners against a principal + named instances?


    Thankyou

    Stefano


    Ste'

    Friday, May 9, 2014 10:35 AM
  • 1433 port is using only for default instance, If you are dealing with named instance port should be some thing else.

    Listeners can listen on whatever port you want them do, regardless of if it's a default instance or not. There could be 5 named instances and 5 listeners and all of the listeners could be using port 1433. Would I do it this way? Probably not, the point is you could if you really wanted to.


    Sean Gallardy | Blog | Twitter

    Friday, May 9, 2014 11:08 PM
    Answerer
  • This is the solution, use the SQL Server Alias :

    Good article : http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:44 AM
    Friday, May 9, 2014 11:58 PM
  • Yep, I thought about using alias but I decide to follow the idea of having all named instances.

    Anyway, I'll resume the first two vm and I'll try the aliases.... I'll add an alias to the named instance and... bind to the listener.... uhmm, I'm going to try next week

    Thankyou


    Ste'

    Saturday, May 10, 2014 12:35 PM
  • Yep, the only way to let the same port listening on two different ag listener, in a primary + named instance is to chang the port. I was not able to assign the same port. I got an error saying that on this ip the 1433 port is already in use.

    Anyway, having only named instances seems to me the cleanest way.



    Ste'

    Saturday, May 10, 2014 12:40 PM
  • add an alias to the named instance and... bind to the listener

    I must not be understanding because an alias isn't even needed. The instances and ports are defined per replica and the listener is for an entire group. You can give the listener any port you want and can use 1433 on every one if you would like. From what I can tell (again I might be misunderstanding) there is no need for aliases anywhere.


    Sean Gallardy | Blog | Twitter

    Saturday, May 10, 2014 4:58 PM
    Answerer
  • Hi

    Did anyone tested a similar configuration?

    Bye.


    Ste'

    Wednesday, May 14, 2014 11:58 AM
  • In order to connect to a network resource, you need to provide an IP address and a port number. In a standalone SQL Server instance, there can only be one combination of an IP address-port address. However, since the Availability Group listener name is using a different IP address, it has its own range of port numbers in of itself, 1433 included. This means that now your physical server has two port 1433 - one for the physical IP address and the other for the Availability Group listener name IP address. This is similar to having multiple websites in a single machine - each website has its own IP address and port number which then gets assigned to the physical network adapter.

    If port 1433 is already being used when you create the Availability Group Listener Name you can try flushing the DNS cache and/or failing over the Availability Group to the other replica.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    • Marked as answer by Sofiya Li Tuesday, May 20, 2014 1:44 AM
    Wednesday, May 14, 2014 4:54 PM
  • You can have two different AG Listeners on port 1433, with each belonging to a different instance. 

    The thing you're missing is that one of your instances is a standalone instance listening on port 1433 on ALL IP addresses.  Look in the log and you'll see see something like

    2009-07-15 17:40:06.39 Server      Server is listening on [ 'any' <ipv4> 1433].

    This means that if Windows adds a new IP address, that instance will be listening on port 1433.

    In your configuration, when the cluster brings the AGL online, port 1433 on the new IP is used by the default instance, even though the AGL belongs to another instance. 

    This doesn't affect FCIs, as they always listen on only IP addresses that are dependencies of their cluster resource group's client access point.

    To remedy this, just configure the instances to listen on specific IP,Port pairs, and turn off "Listen All":

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, May 14, 2014 6:20 PM