Rolled back transactions during SQL Server Mirroring Failover

Question

I have two SQL Server 2014 instances setup with High Safety Mirroring for a production environment. I need to apply security patches to these servers on a regular basis which requires a reboot.

My typical process is applying patches to mirror and rebooting. Then triggering a failover to mirror and applying patches to the other server. Rebooting, then failing back.

My issue is, every failover has some uncommitted transactions rolled back and this is really not good for a production environment. I have no idea which transactions were rolled back and what kind of data loss I'm having. But I need to reboot these servers and apply patches. If I have zero data loss tolerance, what are my options?

Answer 1

In FULL SAFETY mode you should be good to do this without data loss if there are no transactions in flight. Are you waiting for the state to change from Synchronizing to Synchronized after that restart? My guess is application users are in there when you are doing the failover, though.

If the applications are still connecting and data is still being modified then there are transactions in flight and that is the price you have to pay. Best to get a window and stop access. Shut down the site or apps accessing SQL, etc.

Answer 2

You'll need to disable the login(s) on the server, wait for all ongoing transactions to complete, then perform the failover.

Either that, or you need to rewrite the client code to understand when rollbacks occur, and wait/retry until they can complete the transaction successfully.

Although mirroring failover is extremely fast, in-flight transactions cannot be moved between BEGIN TRANSACTION and COMMIT TRANSACTION from one server to another server.