none
Does a legacy SQL client get redirected to the SQL instance behind an 2012 AG Listener?

    Question

  • I found the following video in another forum post where the video shows how enlightened (newer) SQL clients that support the redirect feature are redirected to the SQL instance where the AG listener is running because that is the primary replica in the AG at that time:

    http://www.youtube.com/watch?v=A8lSl8nO1r4

    That makes total sense.

    What I can't find confirmation is if in the case of a legacy SQL client if the SQL client keeps talking to the AG Listener the whole time (unlike the redirected clients), or if something else happens. I suspect it keeps talking to the AG Listener name and IP address which "proxies" the requests to the back end SQL instance.

    I apologize if I am not using the correct terminology, I am new to SQL 2012 AG and am trying to wrap my brain around a lot of concepts all at once. :)

    Friday, July 26, 2013 1:02 AM

Answers

All replies

  • The Listener basically is an IP Address for the Database, so even legacy Clients can handle a Failover easily. (Given that they do reconnect at all... After all, during the Failover you loose Connection for a few seconds.) The one Thing that will not work is a Multi-Subnet Cluster, because there you have different IPs for the nodes, and that Needs a Client Feature to work...
    Friday, July 26, 2013 3:07 PM
  • I apologize, I realize now I didn't phrase my question as a question but rather a statement of what I was looking for clarification on.

    To rephrase my question as an actual question:

    Does a legacy SQL client only talk to the AG Listener name and IP the whole time, or does it switch to another name and IP like the enlightened SQL clients do by switch to the SQL Instance name and IP of the primary replica?

    We are trying to anticipate any difference in behavior between older SQL client and the updated SQL clients that the video shows are aware of the SQL instances behind the AG Listener.


    Friday, July 26, 2013 3:37 PM
  • ALL Clients always connect to the AG listener, given that you specify the listener as the connecting Server Name in the first place. The only exception to that would be if you specify ApplicationIntent=ReadOnly, in which case the Client might Redirect. But other than that there is no reason whatsoever for a Client to reconnect anywhere other than the Server you have specified.

    Friday, July 26, 2013 5:32 PM
  • I understand all clients initially connect to the AG listener. However if you watch the video I linked at minute 36, you will see where the SQL client switches to using the back-end SQL instance behind the AG listener.

    If what I am saying doesn't make any sense, please watch the video and let me know how you would describe what is happening. I freely admit I may be using incorrect terminology here. :)

    Friday, July 26, 2013 6:36 PM
  • Think of an AG Listener as a DNS alias. In fact, it is a DNS alias. When you create an AG listener, it creates a virtual computer object in Active Directory with a corresponding DNS entry (this assumes that you have an Active Directory-integrated DNS zone). Your client applications check the network for the listener name and grabs the corresponding IP address assigned to it. In a multi-subnet configuration, it grabs all of the IP addresses assigned to it. Depending on which one responds, the client establishes a connection to the virtual IP address thru the Windows Server Failover Cluster. The cluster resource DLL for the Availability Group redirects the client request to the instance running as the primary replica and binds the listener IP and port number to that instance. While it may appear as if the client requests are "proxied" back to the SQL Server instance running as the primary replica, the listener name actually runs in the same process space as the database engine. You can check the SQL Server error logs to see this information, similar to the IP address information as being written in the SQL Server error log

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Friday, July 26, 2013 6:39 PM
    Moderator
  • Think of an AG Listener as a DNS alias. In fact, it is a DNS alias. When you create an AG listener, it creates a virtual computer object in Active Directory with a corresponding DNS entry (this assumes that you have an Active Directory-integrated DNS zone). Your client applications check the network for the listener name and grabs the corresponding IP address assigned to it. In a multi-subnet configuration, it grabs all of the IP addresses assigned to it. Depending on which one responds, the client establishes a connection to the virtual IP address thru the Windows Server Failover Cluster.

    The cluster resource DLL for the Availability Group redirects the client request to the instance running as the primary replica and binds the listener IP and port number to that instance. While it may appear as if the client requests are "proxied" back to the SQL Server instance running as the primary replica, the listener name actually runs in the same process space as the database engine. You can check the SQL Server error logs to see this information, similar to the IP address information as being written in the SQL Server error log


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    The first part of you response we fully understand as that is how it works with our existing SQL FCI and other Windows clusters.

    I understand the newer SQL clients are redirected from the AG listener to the SQL instance running as the primary replica.

    My question is regarding older SQL clients and whether they get redirected to the SQL instance or continue to talk directly to the AG Listener.

    Let me use Lync 2010 as an example. It doesn't support database mirroring, only failover clustering. If we move the Lync 2010 databases from our SQL 2008 R2 FCI to a SQL 2012 AG with a Listener, will the Lync 2010 servers (not being capable of fully leveraging a SQL 2012 AG because it is an older SQL client) be redirected from the AG listener to the SQL instance (as you described above) or will the Lync 2010 servers ignore the opportunity for redirection and continue to talk to the AG Listener as if it was the older 2008 R2 FCI Virual Network Name.


    Friday, July 26, 2013 6:58 PM
  • As I've mentioned, the AG listener runs in the same process space as the sqlservr.exe process. So, if clients connect to the AG listener, they are actually connected to the SQL Server instance, regardless of whether or not they use the newer SQL Server Native Client or not. 

    Case in point, however, is that Lync (even 2013) does not support AlwaysOn Availability Groups or even SQL Server 2012. Even if the configuration will work, Topology Builder wouldn't have support for it. I will not pursue this configuration even if I know that it will seamlessly work simply because of supportability. This is similar to how creating additional indexes in SharePoint database is not supported eventhough I know that it works perfectly fine. 


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Friday, July 26, 2013 7:38 PM
    Moderator
  • Ok, let me ask my question a little differnetly.

    For enlightened clients it appears as if they switch from the AG Listener VNN and IP to the back-end SQL insance name and IP. Do non-enlightened clients remain talking to the AG Listener VNN and IP, or do they also switch to the back-end SQL instance name and IP?

    Why am I asking this question even though on the back-end it really is the same to SQL since the AG listener runs in the same process space? Because we have firewalls and network tracing software, and I want to be able to explain to my security guys why one SQL connection behaves one way and why another behaves diffterently.

    My apologies for using Lync 2010 as an example and it not being supported with SQL 2012, I was just trying to grab an older SQL back-ended application. Lync 2013 is supported on SQL 2012 though, just not through an AG.


    Friday, July 26, 2013 8:22 PM
  • No worries. The default port number that you use with an Availability Group listener is 1433, the same port number that the SQL Server instance uses. So, technically, the connectivity behaves the same whether you are using the SQL Server instance name or the Availability Group listener name. It's as if your SQL Server instance now listens to more than one IP address and port number - the usual one and the Availability Group listener name

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Friday, July 26, 2013 8:36 PM
    Moderator
  • No worries. The default port number that you use with an Availability Group listener is 1433, the same port number that the SQL Server instance uses. So, technically, the connectivity behaves the same whether you are using the SQL Server instance name or the Availability Group listener name. It's as if your SQL Server instance now listens to more than one IP address and port number - the usual one and the Availability Group listener name

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    I fully understand that in the grand scheme of all things SQL that it is essentially the same. That's not my question.

    If the AG Listener is 10.10.1.5 and the Back-end SQL instance is 10.10.1.6, does the non-enlightened client only talk to 10.10.1.5, or does it switch to talking to 10.10.1.6 just like the enlightened clients in the video?

    Friday, July 26, 2013 8:44 PM
  • They will still be talking to 10.10.1.5 - the IP address of the AG listener. Only those applications who use the SQL Server instance name will be talking to 10.10.1.6

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Friday, July 26, 2013 9:04 PM
    Moderator
  • Thank you very much for that clarification!

    It seems like the non-enlightened clients will treat the AG Listener just like the FCI VNN & IP of our SQL 2008 R2 cluster today, and follow it around the AG as the primary server changes. Since the folks in our environment are already familiar with that model, this will be an easy transition for them and we can explain the enlightened client behavior to them as a new better way of doing things.

    Thanks again for humoring me and providing the definitive answer!

    Friday, July 26, 2013 11:41 PM
  • And, thanks for watching the video :-)

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Saturday, July 27, 2013 1:12 AM
    Moderator