0

I'm trying understand database failover (mainly in SQL Server) and what handling is required from application side to have zero downtime (no failures) to handle DB failover?

Assuming that DBAs have done the best set-up for SQL servers (primary and some secondary nodes with replication). Now, if one of the nodes fail (or any node is brought down for scheduled maintenance),

  1. What happens to the ongoing queries in SQL server? Do they fail or are they transferred to another node?
  2. Does the SQL Server jdbc driver connecting to the database get any failures? Will it retry automatically in such cases?

Any insights on these basic questions?

1
  • Independent from the SQL Server, which I do not have much knowledge for: the best thing is always to just test the behavior. Even with good specifications/documentation you could gain important insights regarding your specific setup.
    – Ctx
    Feb 19 at 11:15

1 Answer 1

1

What happens to the ongoing queries in SQL server? Do they fail or are they transferred to another node?

If the connection was using the listener address, the connection will be disconnected. This will generate an event and the driver will be notified. In-progress queries via listener connections will be terminated/rolled back and not transferred to another node.

If the connection wasn't using the listener address, the connection will stay alive but any databases that were participating in the availability group that failed over will no longer be accessible. This will kill any currently running queries in those databases or part of those databases and also generate an event back to the driver (depends on which situation hits first, there are various errors that may be returned) depending on the current status of the connection.

If the connection is idle and connected to the listener, depending on the driver, it may reconnect on its own. There are many variables here such as if a database was specified, read only routing was being used, etc.

Does the SQL Server jdbc driver connecting to the database get any failures?

Yes, which ones depend on the timing, but in the general sense you'll either get a connection was disconnected or a database is unavailable, among others, depending on the status of the connection.

Will it retry automatically in such cases?

It depends on if the connection was in a state that allows it to be and the version of the client drivers attempts to do so.

5
  • You might want to call out that ongoing queries via listener connections will be terminated and not transferred to another node.
    – Dan Guzman
    Feb 19 at 13:21
  • @DanGuzman I believe I did in the first sentence, if it's not clear, feel free to propose an edit :) Feb 19 at 13:30
  • 1
    I don't know how to propose an edit outside comments with my privileges so I went ahead and made the change. Feel free to improve as you see fit.
    – Dan Guzman
    Feb 19 at 15:41
  • Perfect @DanGuzman :) Feb 19 at 17:45
  • Thanks @DanGuzman for adding clarification.
    – Hitesh
    Feb 22 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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