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

Question

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.

Answer 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.