Sunday, 13 August 2023

Always On Secondary Replica in a disconnected state where we have Multiple Availability Groups on the same SQLServer Instance

Always On Secondary Replica is showing as disconnected state where we have multiple Availability group  on the same SQL Server instance .

Scenario:-

we have 2 Availability Group running on  2 servers. First AG is working fine as expected . 

But secondary replica on the 2nd AG is showing as disconnected state  and databases are not in sync .

Resolution:-

when we checked  and understand  that the endpoint URL of the secondary AG replica  is configured with wrong TCP port , 

TCP://DBSERVER1.manufacturing.Adventure-Works.com:5022

TCP://DBSERVER2.manufacturing.Adventure-Works.com:5023

After updating the correct port on the endpoint URL , secondary replica came online and starts synchronizing 

It should be as below ,

TCP://DBSERVER1.manufacturing.Adventure-Works.com:5022

TCP://DBSERVER2.manufacturing.Adventure-Works.com:5022

Cause:-

1) We can not have multiple endpoint on the SQL Server Instance 

2) Each port number must be associated with only one endpoint, and each endpoint is associated with a single server instance; thus, different server instances on the same server listen on different endpoints with different ports. Therefore, the port you specify in the endpoint URL when you specify an availability replica will always direct incoming messages to the server instance whose endpoint is associated with that port.

3)We can use the same port on multiple AG's  on the same server since each listener is associated with an IP address and it will use the same TCP socket 

4) If you are using multiple instance on the same server then we need to have different endpoint created with different port  .Hence the endpoint URL also will change for the named instance .









No comments:

Post a Comment