Temporarily disable SQL Server Mirroring

Question

We've got a migration going on where a SQL Server 2012 mirroring configuration running in high safety with auto failover (primary/mirror/witness) is being completely shut down and restarted to move hardware.

Shutdown is being done by automation so the order of shutdown isn't going to be tightly controlled. We would like to prevent role switching during the shutdown/restart, so we need to "remove" the witness. Seems like this could be done either via explicitly removing the witness via TSQL on each mirrored database (there are about 5), then re-adding after everything restarts/resyncs or by just disabling the SQL Server service on the witness. The second option seems like less fiddling around, but most of the references I see say to remove the mirror from each session, then go back and re-add after.

Is there a reason not to just stop and disable the witness SQL server service until the maintenance is complete? Without the witness there will be no failover and all it takes is restarting the service to recover.

We are also planning on disabling tlog backups on the primary just before shutdown to make sure the mirror can resync automatically after restart.

Answer 1

From the documentation, you could just shutdown the witness instead of removing it from all the servers.

It says that the mirroring will then be a "partner-to-partner" quorum, and that no automatic failover will occur.

Of course, I would test it on a dev setup before trying it in production.

A partner-to-partner quorum that consists of the two partners.

As long as the partners retain quorum, the database continues in a SYNCHRONIZED state, and manual failover remains possible. Without the witness, automatic failover is not possible; but when the witness regains quorum, the session resumes regular operation, and automatic failover is supported again.