I have an Alwayson test environment with the following settings.
My test goals are as follows:
- When SqlNod1 is primary instance, I want SqlNod2 to answer read-only queries.
- When SqlNod2 is primary instance, I do not want SqlNod1 to answer any queries.
- I want to use SqlNod3 to mimic offsite replica (with poor network, all my nodes are virtual machines i am planning to disconnect nic card and connect a slow one etc.)
My problem is, say my primary instance is SqlNod2 and core cluster resources are hosted on SqlNod2, when i fail over using SSMS Alwayson dashboard, Dashboard shows that SqlNod1 is primary instance, but windows failover cluster manager shows that cluster resources is hosted by SqlNod2. When that happens (or vice versa) i cannot make secondary instance to answer any queries.
In order to complete failover i have to move cluster resources to same node using WSFC Manager. Is this the default behaviour? I feel something is wrong, not being able to failover using only TSQL and needing WSFC Manager.