Creating AlwaysOn Availiability Group & Listener not showing on second node with T-SQL?

All we need is an easy explanation of the problem, so here it is.

I have followed this guide and stole some of T-SQL code to edit into my own and this is what I have got:

CREATE AVAILABILITY GROUP jammytest   
   WITH (  
      AUTOMATED_BACKUP_PREFERENCE = SECONDARY,  
      FAILURE_CONDITION_LEVEL  =  3,   
      HEALTH_CHECK_TIMEOUT = 600000  
       )  
  
   FOR   
      DATABASE  jammytest 
   REPLICA ON   
      'SQLCLUSTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://SQLCLUSTER02:5022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC,  
         BACKUP_PRIORITY = 50,  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = NO),  
         PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL ),  
         SEEDING_MODE = AUTOMATIC,
         SESSION_TIMEOUT = 10  
         ),

      'SQLCLUSTER01' WITH   
         (  
         ENDPOINT_URL = 'TCP://SQLCLUSTER01:5022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC,  
         BACKUP_PRIORITY = 50,  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = NO),  
         PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL),  
         SEEDING_MODE = AUTOMATIC,
         SESSION_TIMEOUT = 10  
         )   

GO  
ALTER AVAILABILITY GROUP [jammytest]
  ADD LISTENER 'jammytest' ( WITH IP (  ('172.26.240.186','255.255.255.192'), ('172.26.241.186','255.255.255.192')  ) , PORT = 1433 );   
GO 

Now the problem is, it’s not pushing this to the secondary node and I can’t figure out why. It creates the AlwaysOn AG and listener on SQLCLUSTER01 perfectly fine, but not on SQLCLUSTER02.

I have gone through the following logs:

  • SQL Server error logs
  • SQL Agent error logs
  • Event viewer
  • Windows failover cluster

And there is no errors at all, I have tried the ENDPOINT_URL with the hostname and FQDN, still nothing, I have tried swapping them around, nothing. It creates the SQL listener DNS entries in AD as well as the Computer Object.

I have been using this document as a reference.

I am pretty much baffled as to why it doesn’t show it on SQLCLUSTER02, and was hoping someone may have an answer here.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Now the problem is, it’s not pushing this to the secondary node and I can’t figure out why. It creates the AlwaysOn AG and listener on SQLCLUSTER01 perfectly fine, but not on SQLCLUSTER02.

You’re creating the AG on the primary but I don’t see anywhere that you’ve joined the secondary replica. If the secondary replica isn’t joined then it doesn’t know it’s part of the AG.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply