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

by

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment