In SQL Server Always oafter a failover, SQL Server keep the connections open without killing any session
Hi Msft Team!
I want to suggest one improvement to your sql server always on feature. Normally, once we perform failovers we need to ask app teams for maintenance windows... or in worst cases ,if this happens automatically, we need to discover the RCA of this because all the application or users connections had been terminated. Also many times that we had automatic failovers or perform failovers with long executions happening the secondary instance turned into recovery state because it was waiting for this session to finish to synchronize again. So, is it possible to add the capability for failovers to keep the session opened? let's say that you are pointing to the listener and independently where you are your connection will not finish after a failover or if you are executing a process on the primary and you have a failover, this process will continue on the other replica as well. I hope this helps! Regards!
John Q. Martin commented
The answer here is not to burden SQL Server with the 'functionality' but rather design applications in the correct way to handle transient connection failures. There is a wealth of material and .NET libraries that will help with this.
I think ORA RAC Real App Cluster was that approach. Wonder why they didn 't win the entire DB market with it ;).
A area to become a database god - if you succeed ...
Paul Lambert commented
This would be an awesome feature - perhaps impractical for an unplanned failover but maybe the alter availability group ... failover command could do something to push across session/transaction state. This would make maintenance operations an absolute breeze.
It would be difficult to do this for unexpected failovers. You'd have to have a copy of everything in tempdb and also everything in memory available on the secondary node at all times. But what about for a scheduled failover? Create a mechanism so that at some starting point in time everything is gracefully moved to the secondary server (tempdb and memory). New transactions can queue up during this period. Bonus points if you list open transactions left while the transition is occurring so the DBA can gauge progress. If an incredibly long transaction is occurring the DBA would need to know so they can either kill the transaction or reschedule the failover.
Josh Obudzinski commented
Maybe if we could incorporate something similar to SessionState into AlwaysON instead of having additional setup?
I also think that this idea of keeping a session open would be incredible, but I think we have to remember the challenges that they would have to overcome to do so: Any temporary table or table variables would have to be moved over from one tempdb to the other, and that can be quite large. Not only that, but if you are mid-insert into a tempdb table how are they supposed to transition you over; Are they supposed to wait for you to finish or do they just kill your session?
I think this is a great idea, but there will be a lot of hurdles that they will have to overcome.
My Mitel SX 2000 phone system from the 90's had builtin hardware redundancy and if it could keep a phone call live during a transition from one plane to another I would think that 20 years later "Always ON" could do the same thing.
Arthur M. Boopz commented
is this a joke?