SQL Server myths 30th talk about the 1th day a transaction that is running continues after a server failover _mssql

Myth #1: After a server failover, a running transaction continues to execute

Of course it's wrong!

Each failover is accompanied by some form of recovery. However, if a connection is disconnected because of a server or instance crash that occurs when a transaction is not being executed, SQL Server has no way to re-establish the context of the transaction and continue to perform the transaction after the failover server-whether you are using a cluster, mirroring, or failover mode, Log shipping or San replication.

For a failover cluster, when a failover occurs, an instance of SQL Server is started on the node of another failover cluster. Databases on all instances experience the recovery phase-that is, all transactions that do not commit are rolled back.

For database mirroring, logs from the principal server are continuously routed to the mirror server for redo operations. When the mirror server is switched as the principal server, the transaction log of the original mirror server becomes recovery mode, which makes it seem as if the original mirror server has experienced a crash, after which all connections will be directed to the original mirror server.

For transaction log shipping, the transaction log is regularly backed up and delivered to the secondary server. When the primary server crashes, the DBA resumes the secondary server after resuming in the order of recovery. But the final step is to perform the recovery step, which is to rollback the uncommitted transaction.

For SAN replication, I/O to the local San is replicated to the remote San for replay, and when failover occurs, the system will connect to the remote San but the database still needs to perform recovery steps, which is very similar to the failover cluster.

The "unique" technology that allows an executing transaction to continue after a failover uses virtualization technology with real-time migration capabilities, because the connection itself does not know that the object to which it is connected has become another physical server.

But regardless of the technology, if "connection" fails, the transaction being executed will be lost, so this part of the work dealing with such a problem needs to implement some sort of "re-performing" function in the program with code.