Availability group listener is not listening

• Hamid-Sadeghian

  SSC Eights!

  Points: 890

  More actions

  January 7, 2019 at 12:37 am

  #373458

  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: 19026

  More actions

  January 7, 2019 at 12:47 am

  #2018115

  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: 890

  More actions

  January 7, 2019 at 1:00 am

  #2018117

  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: 135258

  More actions

  January 7, 2019 at 2:44 am

  #2018121

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

  Johan
  
  Learn to play, play to learn !

  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 but most of the time this is me

• Hamid-Sadeghian

  SSC Eights!

  Points: 890

  More actions

  January 7, 2019 at 3:36 am

  #2018129

  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: 32407

  More actions

  January 7, 2019 at 4:57 am

  #2018146

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

• Johan Bijnens

  SSC Guru

  Points: 135258

  More actions

  January 7, 2019 at 5:09 am

  #2018148

  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
  
  Learn to play, play to learn !

  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 but most of the time this is me

• Hamid-Sadeghian

  SSC Eights!

  Points: 890

  More actions

  January 7, 2019 at 5:16 am

  #2018149

  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: 135258

  More actions

  January 8, 2019 at 12:24 am

  #2018255

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

  Johan
  
  Learn to play, play to learn !

  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 but most of the time this is me

• Mike Good

  SSCertifiable

  Points: 7415

  More actions

  January 8, 2019 at 7:55 am

  #2018290

  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: 135258

  More actions

  January 8, 2019 at 11:57 pm

  #2018376

  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
  
  Learn to play, play to learn !

  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 but most of the time this is me

• Hamid-Sadeghian

  SSC Eights!

  Points: 890

  More actions

  January 9, 2019 at 1:40 am

  #2018380

  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: 135258

  More actions

  January 9, 2019 at 2:22 am

  #2018384

  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
  
  Learn to play, play to learn !

  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 but most of the time this is me

• Hamid-Sadeghian

  SSC Eights!

  Points: 890

  More actions

  January 9, 2019 at 2:32 am

  #2018388

  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: 135258

  More actions

  January 9, 2019 at 2:47 am

  #2018389

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

  Johan
  
  Learn to play, play to learn !

  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 but most of the time this is me