June 5, 2018 at 1:26 pm
Hi,
I'm in the process of setting up an AOAG cluster. This is my first time doing this, so I'm definitely working through some bumps in the road, but I've been able to get it set up successfully. It's the simplest setup--one primary and one secondary. I've tested failing over by using:
ALTER AVAILABILITY GROUP [AG] FAILOVER
on the secondary, which worked perfectly. After that, I configured my application to point to the AG listener, started a process within the application, and did another failover in SSMS. Unfortunately, the application recognized immediately there was a break in connection and error-ed out. I was under the assumption (perhaps naively) that the failover in an AOAG would be seamless. Is there something I'm missing? Is it well known that there will be a few moments of down time before primary fails over to the secondary--enough so that an app would recognize that?
Again, it's my first time working with AGs so it's possible I'm missing something big here...
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
June 6, 2018 at 1:53 pm
It sounds like you will have to add MultiSubnetFailover to the application's connection string or properties https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery. The Availability Group has this enabled by default so you shouldn't need to change anything with the AG.
Note: if this is a 3rd party application, you will want to check with them to see if their product supports Multi-Subnet Failover.
June 7, 2018 at 6:27 am
Got it. Thank you, Blake!
Mike Scalise, PMP
https://www.michaelscalise.com
June 7, 2018 at 9:18 am
Also do bear in mind that failovers are never completely seamless. In-flight transactions do not fail over, and your connections will drop for a short while. Your application will have to handle that (no idea how).
June 7, 2018 at 9:20 am
Thanks, Beatrix. Good to know.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply