Persisting connections over AG failover

availability-groups

I see that: ‘When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database.’ – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b8857583-0ad7-419c-904b-e9e2a13511c0/sql-connections-to-the-sql-2012-always-on-availability-group?forum=sqldisasterrecovery
And the fix – setting multisubnetfailover = true
https://technet.microsoft.com/en-us/library/hh213417.aspx#CCBehaviorOnFailover
Is there a way to get this to work using a single subnet. Is there a way to detect such failures by the application and have it retry.

Best Answer

I want to point out something. When a failover occurs, any client using the listener to connect will have its' connection closed. This doesn't matter if MultiSubnetFailover is set or not. What MultiSubnetFailover helps with is the client driver to spin up multiple connection threads (1 for each IP) without the application knowing and connecting to the currently active IP transparently to the app.

Having said that, since on a single subnet this setting isn't required or needed (but best practice is to still put it in the connection string as you don't know if it may be needed later or not) there isn't anything needing to be done.

Is there a way to detect such failures by the application and have it retry.

Yes! It's the same error as if you were disconnected for any other reason. Depending on the driver used, the error checking may be different. For example if you're using .Net and the ADO.Net sqldata client you can use the StateChanged event to know when you connect or become disconnected. If your delegate is fired, check the connection. If you are disconnected, attempt to connect again, etc.

Related Question