Will a long running open transaction block you from changing to synchronous mode and failing over to your SQL Server database mirror? Join Kendra to test performing a planned failover from an asynchronous database mirror.
Brent Says: Often, when I’m working with developers, they’re totally surprised that SQL Server’s failover methods all break in-flight transactions. No matter whether you use mirroring, AlwaysOn Availability Groups, or failover clustering, you’ve gotta build in retry logic in your applications if you want seamless failovers.
5 Comments. Leave new
Brent, not only developers but managers and company presidents. I worked for a very small company several months ago. The president ask me to create a 2-node failover cluster that had almost 300 database on it. One day there was a problem and it failed over just as it was supposed to do. Next thing I know, the president was in my office upset that the failover was not “instantaneous” and a couple of long running application transactions were lost.
Calvin – Yep, absolutely!
Does AlwaysOn Async replicas too uses same rollback mechanism for open or uncommitted transactions as database mirroring ?
Ishtiaq – that’s an interesting question. Can you elaborate a little on that to explain what you’re trying to do?
Great video as always, Kendra! One question: I have a principal database in high performance mode (asynchronous – safety off) and suppose the server goes down – no time to do a manual failover in the way you did – ie set safety on.
What are my options for doing a fail back once my principal server is back up?