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