Availability group listener is not listening

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    I've successfully created an availability group with 2 nodes that one node is physical and another is VM. Also running SQL Server Agent and engine on both machines with domain administrator account SQL Server on both server is 2017 and windows is 2019 datacenter edition. I've tried to create a listener, separately trying port 7028 and 7029, but the listener is not even trying to listen - there's no entry in sys.dm_tcp_listener_states, and nothing in netstat -abn.
    also at sys.availability_group_listeners field ip_configuration_string_from cluster is null.
    but clustering is configured successfully and both server is up. 
    firewall on both server is off and alwayson configured correctly and connect with endpoint to each other. also I create a table on database that table create on server 2.
    but when create listener I get successfully message but I cannot connect to listener and also cannot ping that
    I don't know how to begin debugging this.

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18985

    Hamid-Sadeghian - Monday, January 7, 2019 12:37 AM

    I've successfully created an availability group with 2 nodes that one node is physical and another is VM. Also running SQL Server Agent and engine on both machines with domain administrator account SQL Server on both server is 2017 and windows is 2019 datacenter edition. I've tried to create a listener, separately trying port 7028 and 7029, but the listener is not even trying to listen - there's no entry in sys.dm_tcp_listener_states, and nothing in netstat -abn.
    also at sys.availability_group_listeners field ip_configuration_string_from cluster is null.
    but clustering is configured successfully and both server is up. 
    firewall on both server is off and alwayson configured correctly and connect with endpoint to each other. also I create a table on database that table create on server 2.
    but when create listener I get successfully message but I cannot connect to listener and also cannot ping that
    I don't know how to begin debugging this.

    Can you post the error or image of error.
    As long as you create a listener with cno permission, you could connect.
    Try connect using IP and port as well - edited

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    Thank you, but I don't get any error when creating listener and listener is created.

    ،This is query from tables that listener must be exists .

  • Johan Bijnens

    SSC Guru

    Points: 134265

    How did you configure the listener ?  ( TSQL / Posh script please )
    Check your cluster logs !

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    ALZDBA - Monday, January 7, 2019 2:44 AM

    How did you configure the listener ?  ( TSQL / Posh script please )
    Check your cluster logs !

    I Create listener with Availability group wizard and generate script.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32390

    When you try to connect to the listener, what happens (is there an error message, etc.)?

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Hamid-Sadeghian - Monday, January 7, 2019 3:36 AM

    ALZDBA - Monday, January 7, 2019 2:44 AM

    How did you configure the listener ?  ( TSQL / Posh script please )
    Check your cluster logs !

    I Create listener with Availability group wizard and generate script.

    Did you create it providing a fixed IP address ( your screenshot shows NULL ) ?
    Maybe altering  it ( to the address you provided ) can solve the issue:
    ALTER AVAILABILITY GROUP MyAg2   ADD LISTENER 'ListernerName' ( WITH IP ( *** ) , PORT = *** );
    GO

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    ALZDBA - Monday, January 7, 2019 5:09 AM

    Hamid-Sadeghian - Monday, January 7, 2019 3:36 AM

    ALZDBA - Monday, January 7, 2019 2:44 AM

    How did you configure the listener ?  ( TSQL / Posh script please )
    Check your cluster logs !

    I Create listener with Availability group wizard and generate script.

    Did you create it providing a fixed IP address ( your screenshot shows NULL ) ?
    Maybe altering  it ( to the address you provided ) can solve the issue:
    ALTER AVAILABILITY GROUP MyAg2   ADD LISTENER 'ListernerName' ( WITH IP ( *** ) , PORT = *** );
    GO

    yes this is my code to create listener with static IP.
    Exactly my question is why my ip address is not in sys.availability_listeners !!

  • Johan Bijnens

    SSC Guru

    Points: 134265

    can you find any information in the SQLServer Errorlog file or in the cluster logfile ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Mike Good

    SSCertifiable

    Points: 7367

    In failover cluster mgr right click on the AG resource, properties, dependencies, and ensure AG resource is dependent on your listener.  If not you can add that dependency here.  If dependency is not set, you will sort of have listener, but only partially with symptoms like described here.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    I always configure it using Powershell.
    As marked: did you restart the cluster resource to enable the listener ?


    # create listener with default DHCP
    $NewAGListener = New-SqlAvailabilityGroupListener -InputObject $NewAG -Name $AGListenerName -Port $ListenerPortNumber ;

    if ( $NewAGListener ) {
            Write-Host $('{0} Listener [{1}] create for AG [{2}]' -f (get-date -Format 'yyyy-MM-dd HH:mm:ss'), $AGListenerName, $AGName ) -BackgroundColor Yellow -ForegroundColor Black ;
            if ( ! ( get-module FailoverClusters ) ) {
              Import-Module FailoverClusters
              }

            # Default naming convention for Cluster Resource = "AGName_AGListenerName"
            $ClusterResourceName = $('{0}_{1}' -f $AGName, $AGListenerName ) ;
            $Res = Get-ClusterResource -Name $ClusterResourceName | Where ResourceType -eq 'Network Name' ;
            if ( $Res ) {
              # ref https://msdn.microsoft.com/en-us/library/ff878487.aspx#SystemReqsForAOAG
              $Res | Set-ClusterParameter -Name HostRecordTTL -Value 300 ;
              # WARNING: The properties were stored, but not all changes will take effect until ClusterResourceName is taken offline and then online again.
              $Res | Stop-ClusterResource ;
              $Res | Start-ClusterResource ;
        
              # When AGListener goes offline, the availability group is being taken offline ! you need to restart that too !
              #Start dependant resource AG
              Start-ClusterResource -Name $res.OwnerGroup.Name ;
              }
            else {
              Write-Warning $('Cluster Recource NOT found [{0}]' -f $ClusterResourceName ) ;
              }
            }

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    ALZDBA - Tuesday, January 8, 2019 11:57 PM

    I always configure it using Powershell.
    As marked: did you restart the cluster resource to enable the listener ?


    # create listener with default DHCP
    $NewAGListener = New-SqlAvailabilityGroupListener -InputObject $NewAG -Name $AGListenerName -Port $ListenerPortNumber ;

    if ( $NewAGListener ) {
            Write-Host $('{0} Listener [{1}] create for AG [{2}]' -f (get-date -Format 'yyyy-MM-dd HH:mm:ss'), $AGListenerName, $AGName ) -BackgroundColor Yellow -ForegroundColor Black ;
            if ( ! ( get-module FailoverClusters ) ) {
              Import-Module FailoverClusters
              }

            # Default naming convention for Cluster Resource = "AGName_AGListenerName"
            $ClusterResourceName = $('{0}_{1}' -f $AGName, $AGListenerName ) ;
            $Res = Get-ClusterResource -Name $ClusterResourceName | Where ResourceType -eq 'Network Name' ;
            if ( $Res ) {
              # ref https://msdn.microsoft.com/en-us/library/ff878487.aspx#SystemReqsForAOAG
              $Res | Set-ClusterParameter -Name HostRecordTTL -Value 300 ;
              # WARNING: The properties were stored, but not all changes will take effect until ClusterResourceName is taken offline and then online again.
              $Res | Stop-ClusterResource ;
              $Res | Start-ClusterResource ;
        
              # When AGListener goes offline, the availability group is being taken offline ! you need to restart that too !
              #Start dependant resource AG
              Start-ClusterResource -Name $res.OwnerGroup.Name ;
              }
            else {
              Write-Warning $('Cluster Recource NOT found [{0}]' -f $ClusterResourceName ) ;
              }
            }

    Thank you .I'll check it.
    But in cluster log I get this error 

    mscs_security::SchannelSecurityContext::AuthenticateAndAuthorize: HrError(0x80090327)' because of '[Schannel] Server: could not match certificates with other side, number of certs: 1'
    00002120.00002cbc::2019/01/07-10:50:35.491 WARN mscs::ListenerWorker::operator (): HrError(0x80090327)' because of '[SV] Schannel Authentication or Authorization Failed'

    and at AD in computer object , Listener does not added and in cluster resource also does not exist.
    But account that I login to windows is domain admin and also is local admin and also sql server services in both servers run under domain admin account.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    You have to register the Listener computer object in AD yourself and grant the cluster computer object full authority to manage that listener object !

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Hamid-Sadeghian

    SSC Eights!

    Points: 879

    ALZDBA - Wednesday, January 9, 2019 2:22 AM

    You have to register the Listener computer object in AD yourself and grant the cluster computer object full authority to manage that listener object !

    ummmm!!! 
    Thanks , but in another systems that I configured alwaysON, never register listener in AD or grant cluster computer object to that.
    Only steps that I do is:
    1- enable failover cluster on both systems
    2- install sql server on both systems and change SQL Server account to domain admin.
    3- enable alwaysON feature on both SQL Server.
    4- create clustering and added two computers to that
    5- Configure Availability Group and listener and endpoint with Availability group wizard 
    6- configure firewall for open ports for listener and endpoints.
    that's it.
    and everything is ok and listener work fine.
    (

  • Johan Bijnens

    SSC Guru

    Points: 134265

    What's the cluster service account ? Does it have AD auth to register computer objects ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

Viewing 15 posts - 1 through 15 (of 18 total)

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