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



  • 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 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-availability-group-transact-sql?view=sql-server-ver15 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.



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2