How Does Preferred Owner Effect Availability Groups

Question

How Does Preferred Owner Effect Availability Groups

alex.sqldba

SSChampion

Points: 10254
More actions
June 4, 2017 at 10:35 am

#402132

Evening All,
I know the rule of thumb is don't touch the Preferred Owner settings (apart from the Priority Order) but I am wondering how it effects an Availability Group
I've 4 node Availability Group Cluster. 3 Automatic Failovers with Synchronous Commit and a Asynchronous with Manual failover.
When the Group is owned by Replica A and I look at Cluster Managerl. There is only one preferred Server checked. However, If I fail over to Replica B then refresh Cluster Maanger I see that the 3 synchronous replicas are checked as preferred owners and the Asynchronous is not.
So I am confused!? Why is it like that? Have I configured something wrong?
Cheers
Alex

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Are you referring to the Preferred owner setting on the cluster role\group or the possible owner settings on each of the cluster resources?

Your question seems to imply possible owners but you use the term preferred owners

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

Well, I was referring to this screen:

But looking at the properties again they have changed to all 3 being ticked as I expected. So now I wonder if I when I looked yesterday it was too soon after a failover (within a second or two) and it hadn't finished updating it own settings.

So when I looked at this yesterday when on this replica it had only 1 server ticked. Where as historically I've seen every replica with auto failover has a tick.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

alex.sqldba - Monday, June 5, 2017 5:01 AM
Well, I was referring to this screen:
But looking at the properties again they have changed to all 3 being ticked as I expected. So now I wonder if I when I looked yesterday it was too soon after a failover (within a second or two) and it hadn't finished updating it own settings.
So when I looked at this yesterday when on this replica it had only 1 server ticked. Where as historically I've seen every replica with auto failover has a tick.

Ok, that's the clustered group\role Preferred owners list. This is updated dynamically by the Availability Group configuration. It is set for nodes where the SQL Replicas are configured for synchronous commit. Since only 2 replicas can be configured for synchronous commit only 2 nodes should ever be selected for one particular clustered role\group.
Do not modify this setting, as I pointed out above, it is managed automatically

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

alex.sqldba SSChampion Points: 10254 More actions · Answer 4

Cheers for that.

Just to confirm, I can change the order they are in the list -- to alter the order in which they fail over?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

alex.sqldba - Monday, June 5, 2017 6:49 AM
Cheers for that.
Just to confirm, I can change the order they are in the list -- to alter the order in which they fail over?

As I pointed out, do not touch any of these settings they are dynamically modified.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

alex.sqldba SSChampion Points: 10254 More actions · Answer 6

Okay.

How does one change the order in which fail over occurs.

If I have 4 SQL Server 2016 instances: A, B, C and D

And I want to failover to B as a last resort so this is set to manual failover

The others are set to automatic failover (A, C and D)

How would I ensure when the Principle server is A that the next server is fails over to is D, rather than C?

Cheers
Alex

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

alex.sqldba - Monday, June 5, 2017 7:54 AM
Okay.
How does one change the order in which fail over occurs.
If I have 4 SQL Server 2016 instances: A, B, C and D
And I want to failover to B as a last resort so this is set to manual failover
The others are set to automatic failover (A, C and D)
How would I ensure when the Principle server is A that the next server is fails over to is D, rather than C?
Cheers
Alex

I'm fairly certain that even in sql 2016 you can only have 2 replicas set for automatic failover not 3, you can have 3 replicas set for synchronous commit

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

alex.sqldba SSChampion Points: 10254 More actions · Answer 8

Here's the screen from my AG and the source of my confusion on failover order:

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

ok, it's an enhancement in 2016.
You may want to read this

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉