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?
Best Answer
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.