SQL 2017 Listener with Read-Scale (Clusterless environment) AG not working

  • JP10

    SSCarpal Tunnel

    Points: 4372

    Hello,

    I have a Read-Scale AG set up.  I have it failing over back and forth just fine.  I am trying to now set up a Listener, but when I set it up I use the primary replica's IP address and use port 1433, but I get the below error when trying to connect to the SQL instance through Management Studio with the new Listener name.  The article I read said to use the Primary replica server IP for the Listener.  I even tried using an new static IP for the Listener and used a different port and added it to the firewall, but same error.

    Can you have a Listener when using a Read-Scale AG set up?  Or do we need a WSFC to have a Listener work?

     

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to tstsqldb.

    ------------------------------

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    Thank you.

     

  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • JP10

    SSCarpal Tunnel

    Points: 4372

    Where did everybody go?  This used to an active forum with lots of rich information and help.  =)

  • Sreekanth B

    SSCertifiable

    Points: 6144

    We haven't setup a clusterless Availability groups in our environment, but I don't see how we can have a listener when there is no underlying Failover Cluster involved. As far as I know, you can't have a listener with cluster-less AOAG setup.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32396

    You can have a listener, but there's kind of no point because you'd have to recreate it in the event of a (forced) failover anyway.

  • JP10

    SSCarpal Tunnel

    Points: 4372

    @sreekanth,

    both the below links show how to set up a listener in a Read-Scale AG set up (clusterless AG).

    https://kohera.be/blog/sql-server/clusterless-availability-groups/

    https://www.sqlservercentral.com/blogs/configure-always-on-ag-with-sql-2017

    @Beatrix,

    You are correct, but at least if you have a listener that works all your applications can point to one connection point and you don't have to go and rename all your connections strings to the new Primary Replica.

    I just want to know if the listener for a Read-Scale AG setup (Clusterless AG) works or not?  Cause I can't seem to get it to work.

    Appreciate the feedback.

  • Sreekanth B

    SSCertifiable

    Points: 6144

    Not sure if those links has complete details you need to achieve this.

    Again...I don't see how SQL Server can create underlying DNS records for your listener without WSFC involved. In normal AG scenarios, when we create a listener from SSMS by providing IP address(es) etc etc,  WSFC cluster objects/Network resource will take care of creating underlying records in DNS(assuming CNO has enough permissions). Unless you create that manually by working with your domain admins, I don't see how listener is going to work in your scenario, unless if I am missing something here.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32396

    Why not just use a DNS alias instead? As already stated, a listener is kind of pointless in this scenario.

  • JP10

    SSCarpal Tunnel

    Points: 4372

    Can you give me details on how to create a DNS alias?  Appreciate the information everybody!

  • JP10

    SSCarpal Tunnel

    Points: 4372

    Ok I worked with our SysAdmin we put the Listener name in DNS and used the primary replica IP for the Listener.  After DNS propagated I was finally able to connect in Mangement studio with the Listener name.  Just fyi I also put the Listener name in AD.

    I understand it is a manual failover process if we need to failover.

    Thank you all for your help much appreciated!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply