April 22, 2016 at 6:49 am
But failing over won't suddenly make those secondary connections appear, which is what his question implied. The only way that the secondary will receive connections is if the primary routes them there as a read only round robin; the IP never will point directly to the manual failover location unless a manual failover to that location is taken. And in fact if both R1 and R2 are down, there won't be a listener active to direct traffic that way so actually there won't be any traffic hitting the R3 via the listener name.
April 22, 2016 at 7:12 am
I am still not following that counting.
If the R1 is the primary replica, and fails, R2 picks it up. That's 1 Move, right?
Now R2 is the primary; and fails; why wont R3 pick that up as the second automatic failover.
Or is the interface saying that R1 to R2 is 1 automatic failover and R2 to R1 is a second automatic failover?
April 22, 2016 at 7:16 am
You could look at it that way if it helps. To say you have 2 automatic failovers does not mean failover paths. It means you can define two nodes that can be automatic failover partners. R1 is 1, R2 is 2. If it helps for now to think of R1-R2 and R2-R1 as two, that is fine -- but SQL 2016 is going to increase the number of failover partners, which won't necessarily increase the number of paths the same way. What you want is behavior not supported until SQL 2016.
April 22, 2016 at 7:42 am
jeff.mason (4/22/2016)The only way that the secondary will receive connections is if the primary routes them there as a read only round robin
The listener (rather than the primary) directs read-intent queries to a secondary.
And in fact if both R1 and R2 are down, there won't be a listener active to direct traffic that way so actually there won't be any traffic hitting the R3 via the listener name.
No, but read-only connections can still be directed straight to a specific secondary, so R3 would still be readable.
April 22, 2016 at 7:46 am
Beatrix Kiddo (4/22/2016)
jeff.mason (4/22/2016)The only way that the secondary will receive connections is if the primary routes them there as a read only round robin
The listener (rather than the primary) directs read-intent queries to a secondary.
And in fact if both R1 and R2 are down, there won't be a listener active to direct traffic that way so actually there won't be any traffic hitting the R3 via the listener name.
No, but read-only connections can still be directed straight to a specific secondary, so R3 would still be readable.
Can you tell me a time when the listener is not owned by the primary? And what's going to direct traffic if there is no listener?
April 22, 2016 at 7:47 am
? You don't have to have a listener to connect to either a primary OR a secondary.
April 22, 2016 at 7:55 am
You do if you use the listener name in a connection string, though, and again, if he is wanting to see his connections move as things fail over, he is clearly using the listener in his connection string.
April 22, 2016 at 9:11 am
jeff.mason (4/22/2016)
To say you have 2 automatic failovers does not mean failover paths. It means you can define two nodes that can be automatic failover partners. R1 is 1, R2 is 2.
Hmm, Okay, another question from this then if you don't mind.
As I cant test this right now (otherwise I would) because someone else is now using this test cluster to get their Read-Only portion of their application to work; so I am leaving the boxes alone for a bit:
What would happen if I defined the following:
R1: Synch. Auto-Failover.
R2: Synch. Manual Failover
R3: Synch. Auto-Failover
And then I powered off R1.
Would it simply failover to R3 Automatically?
April 22, 2016 at 9:16 am
Exactly.
April 22, 2016 at 9:25 am
And then if I brought back R1, waited for them to Synch and powered off R3 it would go straight to R1?
April 22, 2016 at 9:30 am
Yes again. The only gotcha for those coming from failover clusters is that there is no concept of "failback". In FCIs you can set up so that if a preferred node goes down and failover happens, then when the node comes back up, it fails back to the original location. This is for instance balancing during Windows patching, for instance. But in AGs, if you turn off R1 and fail to R3 and then turn R1 back on, it stays on R3 until either you manually move it or as you ask you turn off R3.
April 22, 2016 at 12:24 pm
Thanks Jeff..
That certainly clarifies the Automatic Failover options now. I clearly had it wrong!
Cheers again
Alex
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply