AG Failover Order

Question

AG Failover Order

PHXHoward

SSCrazy Eights

Points: 8389
More actions
March 10, 2014 at 11:50 am

#396395

Hi everyone. Hoping to get some clarification on a question that has been on my mind for quite a while. Really surprised that after countless Google searches, I can't find the answer (or even the question) published anywhere.
If I have multiple servers in my AG group and set two of the secondaries to synchronous with auto failover, how do I control which one one of the two secondaries will become primary if the original primary goes down?
We need to maintain HA even during patching so need a first choice auto failover and a second choice auto failover.
Thanks for reading.
Howard

Viewing 15 posts - 1 through 15 (of 15 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

I think you can do that by changing the Replica Mode for the Availability Replicas and by the preferred owners properties.

http://blogs.msdn.com/b/alwaysonpro/archive/2014/02/28/sql-availability-group-ag-resource-and-possible-owners-and-preferred-owners.aspx

--

SQLBuddy

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Thanks for the article on how it works SQLBuddy. It looks like it just isn't possible to have more than 2 replicas set in auto failover mode. I was hoping for a primary/secondary auto failover option but upon testing, it doesn't seem to work that way.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Yep thats correct

You are only able to have 2 synchronous replicas and auto failover is only available on synchronous replicas

Its by design or you could auto failover with data loss (potentially)

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

PHXHoward (3/10/2014)
Thanks for the article on how it works SQLBuddy. It looks like it just isn't possible to have more than 2 replicas set in auto failover mode. I was hoping for a primary/secondary auto failover option but upon testing, it doesn't seem to work that way.

You have only 2 synchronous replicas in auto failover mode. That's the limitation by architecture.

I was hoping for a primary/secondary auto failover option but upon testing, it doesn't seem to work that way.

Can you be bit clear on this ? I am not sure what you meant by that.

--

SQLBuddy

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 5

We hoped to have a failover priority order. Three synchronous replicas. One secondary locally and one in the other datacenter so that if the primary replica server fails, it would failover locally but if we ever lost the primary data center, it would automatically come up in the other data center. Our network can support synchronous replication across data centers but it seems that the technology of AlwaysOn will not support an automatic failover order. I'd be satisfied with FCIs in each data center and an AG between locations but that is more of a DR situation rather than cross data center HA.

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

PHXHoward (3/11/2014)
We hoped to have a failover priority order. Three synchronous replicas. One secondary locally and one in the other datacenter so that if the primary replica server fails, it would failover locally but if we ever lost the primary data center, it would automatically come up in the other data center. Our network can support synchronous replication across data centers but it seems that the technology of AlwaysOn will not support an automatic failover order. I'd be satisfied with FCIs in each data center and an AG between locations but that is more of a DR situation rather than cross data center HA.

FCIs when introduced to an Availability group do not support automatic failover

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

You can control this by ordering the replicas in the Availability Replicas section. You needn't change it in the Failover Cluster Manager. For ordering you have to change the mode from Automatic to Manual and Vice Versa. The Same order in the Availability Replicas section will be the failover order.

Suppose you have Primary P, Synchronous S1, Synchronous S2.

P is down --> S1 is Primary .. P1 & S1 down --> S2 is Primary.

Is it not working this way ?

--

SQLBuddy

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

PHXHoward (3/11/2014)
We hoped to have a failover priority order. Three synchronous replicas. One secondary locally and one in the other datacenter so that if the primary replica server fails, it would failover locally but if we ever lost the primary data center, it would automatically come up in the other data center. Our network can support synchronous replication across data centers but it seems that the technology of AlwaysOn will not support an automatic failover order. I'd be satisfied with FCIs in each data center and an AG between locations but that is more of a DR situation rather than cross data center HA.

The AlwaysOn group is using the cluster engine to handle the failover, it looks at the role preferred owners and the AG resource possible owners and actually modifies them dynamically when required.

So, as detailed in the link posted above, any replica that is an automatic failover partner will be able to host the AlwaysOn group as a Primary. Failing over to a manual synch partner strips all owners apart from the manual instance.

The listener resource as a member of the AG cluster role will also track the replica that becomes Primary.

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

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

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 9

I had hoped to have a failover order for AGs using preferred nodes similar to how FCIs work.

S1 is primary. If S1 becomes unavailable, automatically failover to S2 which is in the same data center. If the whole data center becomes unavailable, automatically failover from S1 to S3 which is in the second data center. This would be handled by having a server in a third location for quorum decisions.

I can set all three replicas to synchronous commit but I can only pick two replicas for automatic failover. Was hoping that the functionality existed but it looks like it doesn't yet.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

PHXHoward (3/12/2014)
I had hoped to have a failover order for AGs using preferred nodes similar to how FCIs work.

We can't change that directly on FCI. But the same can be achieved by specifying the order in the Availability Replicas Section. It gets propagated to the Preferred Owners.

S1 is primary. If S1 becomes unavailable, automatically failover to S2 which is in the same data center. If the whole data center becomes unavailable, automatically failover from S1 to S3 which is in the second data center. This would be handled by having a server in a third location for quorum decisions.

The above proposed solution does this.

I can set all three replicas to synchronous commit but I can only pick two replicas for automatic failover. Was hoping that the functionality existed but it looks like it doesn't yet.

Yeah, Unfortuantely we can have only 2 synchronous replicas. That's a limitation.

--

SQLBuddy

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

PHXHoward (3/12/2014)
I had hoped to have a failover order for AGs using preferred nodes similar to how FCIs work.

That is not how FCIs work, they cycle through the possible owners list with preferred nodes first but it doesn't parse the list from the start every time.

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

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

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

You can have more than 2 synch replicas but only 2 for auto failover

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 13

PHXHoward (3/12/2014)
I can set all three replicas to synchronous commit but I can only pick two replicas for automatic failover. Was hoping that the functionality existed but it looks like it doesn't yet.

This is correct. Even though 2 synchronous, only one pair of automatic failover. P -->S1 and S1 --> P

--

SQLBuddy

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

Just to clear any confusion, you may have more than 2 synch replicas. However, only 2 synch replicas may be configured for auto failover at any one time, any attempt to increase the number produces an error dialog. See attached image

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

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