AlwaysON Listener: Verify performace connnect though a listener to access AG DBS

Ashwan 531 Reputation points
2020-09-02T18:03:19.797+00:00

My environment is 2016 SP2 CU14 EE edition. Application team has few concerns when connect to AG databases using group listener slower than just connect using host name

I need to verify /prove how connection though group listener performance over connect using host name . Could you please advice any method of doing it. If how to check ,validate our group listener performance is up to the standard performance.

Currently able to connect using listener as no issue as seen /experience

just try "ping -t <listenername> didnt see any packet drops

Any one can help would be highly appreciated
thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,428 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Dan Guzman 9,261 Reputation points
    2022-07-04T14:51:55.75+00:00

    You can capture the connection times using the listener and host name with a PowerShell script like this example:

    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourListenerName;Integrated Security=SSPI;Database=YourDatabase")  
    $timer = [System.Diagnostics.Stopwatch]::StartNew()  
    $connection.Open()  
    Write-Host "Connection with listener name duration is $($timer.Elapsed)"  
    $connection.Close()  
      
    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourHostName;Integrated Security=SSPI;Database=YourDatabase")  
    $timer = [System.Diagnostics.Stopwatch]::StartNew()  
    $connection.Open()  
    Write-Host "Connection with host name duration is $($timer.Elapsed)"  
    

    $connection.Close()

    1 person found this answer helpful.
    0 comments No comments

  2. Junior Galvão MVP 76 Reputation points
    2020-09-02T18:20:35.983+00:00

    Ashwan1234,

    You are highlighting that the connection through the Listener is slower than the connection directly through the Host.

    But are you using the Listener's name or IP address?


  3. Edwin M Sarmiento 261 Reputation points
    2020-09-02T21:46:28.59+00:00

    What concerns do your application team have about using the AG listener name? Plus, are they aware of the real reason behind using an AG listener name? Maybe they are not aware of why an AG listener is used.

    The primary reason to put databases in an AG is to provide high availability. The AG listener name allows for transparent redirection of client applications if and when a failover happens. If they use a hostname (or the SQL Server instance name) instead of an AG listener name, you run the risk of longer downtime because you have to manually redirect the client applications to the SQL Server instance running the primary databases.


  4. m 4,271 Reputation points
    2020-09-03T07:24:40.763+00:00

    Hi @Ashwan

    I need to verify /prove how connection though group listener performance over connect using host name . Could you please advice any method of doing it. If how to check ,validate our group listener performance is up to the standard performance.

    You want to prove using group listener performance over using host name? If more convenient and response faster are factors of performance. I think you just need to delete your current listener and then monitoring one AlwaysOn Failover ...

    Suppose we have established the AlwaysOn scheme as follows. The Availability Group is called testAG, and it has three availability replicas, Denali1, Denali2, and Denali3, as shown in Figure 1.
    22416-20200903nolistener.jpg

    Denali1 is now the primary replica, and the availability group contains two databases: testdb1 and testdb2. There is also a database nonAGdb on Denali1 that does not belong to any availability group. On the two secondary replicas Denali2 and Denali3, there are only two availability databases, testdb1 and testdb2, and no other databases.If you only set it in this way(not using one listener), you can only see one resource in the Windows Failover Cluster Manager, the availability group resource. But the SQL client cannot use the name of this resource to log in to SQL Server. They must know the instance name of the current master copy and use this name to connect to SQL Server. Once an AlwaysOn failover occurs, you need to redirect the application to the new primary copy by modifying the application's connection string, adding aliases, etc. In the above example, the user needs to use the name Denali1 to connect at the beginning. If AlwaysOn fails over and switches to Denali2, then all users must use the name Denali2 to connect. This is very inconvenient. In order to allow the application to transparently connect to the primary replica without being affected by failover, you need to create a Listener. A Listener contains three elements: virtual IP address, virtual network name (DNS name), and port number. After the Listener is created, virtual IP address and virtual network name resources are added to the availability group resources. The application connects to the virtual network name instead of the instance name of the primary replica to access the primary replica instance and the database running on it, which is very similar to a failover cluster. Unlike a failover cluster, in addition to the virtual network name, the real instance name of the master copy itself can still be used to connect to this instance.

    Still the above example. We can create a Listener whose name (ie virtual network name) is testAGvname. As can be seen from Figure 2, SQL Server Management Studio can either use the name Denali1 or testAGvname to connect to SQL Server. After connecting to SQL Server with testAGvname, in addition to the database in the availability group, you can also see nonAGdb, which proves that you have connected to the copy of Denali1 through testAGvname.
    22405-20200903usinglistener.jpg

    More information: sql-server-always-on-listeners

    If the reply is helpful, please do “Accept Answer”.
    BR,
    Mia

    0 comments No comments

  5. m 4,271 Reputation points
    2020-09-04T01:31:39.367+00:00

    Hi @Ashwan ,

    Is the reply helpful?

    If the reply is helped,please click "Accept Answer".
    BR,
    Mia


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.