1

We have been doing rigorous testing of the Always On environment by doing manual failovers multiple times at the same time doing transaction in the midst of failover and we observed the following and would like to know your opinion on this.

The application that uses the AG databases does inserts/updates frequently (like in seconds interval). During failover… the application is connected to the failed over AG Group but throwing an error saying the database is in read only state and not able to write to the database and it couldn’t do any inserts and updates until the failover completes. The Nodes are set up for synchronous commit mode (automatic failover).

When we are doing manual failover it takes about 10 seconds to fully failover. The issue we have is on this 10 second interval.

I know we can add error handling codes from the application side to handle the error or make the application wait until the failover fully takes place. The problem is to change the application code is not going to be an easy process for us since it is a third party application.

Is there any way we can set/tell SQL server to not allow connections to the server until the failover has completed; hence we do not see the “database is in read only state” error. In other words, can SQL server deny connections until the failover has completed?

  • Even if you could somehow deny connections, wouldn't you still get "failed to connect to SQL server" errors on the application side? – BradC Apr 26 '17 at 18:36
  • It gets better if you configure a connection validation in your database pool. Not sure if it needs to do more than SELECT 1 – eckes Apr 26 '17 at 18:42
  • We would prefer the failed connection since the app is designed to handle that issue. The failure occurs when it calls an SP that will write to the database when the database is still in read only mode. We will look at the database pool option to see if that might help. Thanks to both for your response! – M. Livingston Apr 27 '17 at 15:22
  • If you're using an FCI, SELECT 1 will work since it works at the instance level. If you're using an AG, then specify the name of the DB as it works on the DB level per failover. – Ali Razeghi Apr 27 '17 at 18:02
1

There is not a built in option to do this directly from AlwaysOn. You would have to go with something a bit custom. Depending on your setup, you could do something as simple as a job or a trigger scanning for changes in alwayson state. Which then disables/enables the login for the application during a failover. But then you may see other errors in your log for the failed login attempt.

|improve this answer|||||
  • Thanks for the note. We are looking at doing something that monitors the status before allowing the connection. It would be great if the connection pool idea will allow this feature. That's what we are looking into now. Thanks for the input. – M. Livingston Apr 27 '17 at 15:25
  • Good luck!, Also you could try requesting this as a feature via microsoft connect or something like that. Perhaps they will agree and add this as to AO in a future release. – mokszs Apr 27 '17 at 19:20
0

One thing you can look into is ScaleArc

http://www.scalearc.com/

it will queue the connections until the replica has failed over from SideA to SideB. the application will not know that a fail over has occurred. The connections and transactions will be released to the new primary replica once its available... Of course the solution costs $$

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.