If I have an AG with 4 different nodes as below:
- Primary Node
- Secondary Node 1 - Asynchronous Commit - Read Only Yes
- Secondary Node 2 - Synchronous Commit - Read Only No
- Secondary Node 3 - Synchronous Commit - Read Only Yes
In this case, if a connection to secondary node is required from an application, will the SQL Server route it to Node 1 or Node 3 ? Node 2 is out of question because it's Read Only is set to NO, isn't it ?
Ideally it should be routed to Node 3 because it has synchronous commit and data will be more consistent with the primary. Is that's what will happen here?