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?
Best Answer
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.