Failing over an Asynchronous Mirror in SQL Server (video)

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.

Previous Post
Announcing Row-Level Security in Azure SQL Database
Next Post
The Hard Truth About Patching SQL Server Availability Groups (Hotfixes, Cumulative Updates, and Service Packs)

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.

  • Does AlwaysOn Async replicas too uses same rollback mechanism for open or uncommitted transactions as database mirroring ?

  • Daniel Souchon
    October 22, 2015 8:08 am

    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?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.