I have an Alwayson test environment with the following settings. Always on settings

My test goals are as follows:

  1. When SqlNod1 is primary instance, I want SqlNod2 to answer read-only queries.
  2. When SqlNod2 is primary instance, I do not want SqlNod1 to answer any queries.
  3. 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.

Dashboard shows SqlNod1 is primary instance WSFC Manager shows cluster resources hosted by SqlNod2

  • What error(s) do you get when you attempt to failover via T-SQL? – Thomas Stringer Apr 8 '15 at 19:44
  • No errors. Alwayson fails over successfuly but WSFC not. – SelmanAY Apr 8 '15 at 19:47
  • I think you are looking at this the wrong way. You shouldn't care which node owns the core cluster resource group (and resources). You care which node owns the SQL Server AG cluster resource group, which will most definitely be the same server/node that hosts the primary replica of the availability group. – Thomas Stringer Apr 8 '15 at 19:52
  • Tom already pointed out that it doesn't matter. You actually have two questions - the first being about the core cluster resource group which tom already answered in the comment. The second is about read only routing. You'll need to setup your read only routing list properly for what you'd like and setting the readable secondary property correctly. – Sean Gallardy Apr 9 '15 at 18:28
  • Many thanks to both of you. After Tom's comment. I found out about read only routing list settings. I was missing that since it does not have GUI. I was planning to find a good tutorial and answer the question for future reference but i did not have time for it. If one can answer the question i can mark as accepted. Thanks again. – SelmanAY Apr 9 '15 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.