Sql-server – False-failures when mirrored in high-safety mode without a witness

mirroringsql serversql-server-2008-r2

A number of DBs are mirrored in high-safety mode without a witness. I'm trying to understand what might happen in the case that a DB (or DBs) experiences a false-failure while the primary server remains up and functioning and all other mirrored DBs are in their normal SYNCHRONIZED state.

Would the mirroring state for the false-failure DB become DISCONNECTED but, because the server had not failed, the DB on the primary still be able to accept connections and do transactions, although it would be running unprotected? Or, would the DB on the primary become DISCONNECTED and unavailable? And, what would be the recovery for this? Is it most likely to be a simple ALTER DATABASE SET PARTNER RESUME?

Previously, we've had these DBs in high-safety mode with witness (auto failover) and increased the timeout to 20 seconds to eliminate false-failures, which worked well for a year and a half. But, this week, we had a single DB failover for unknown reasons. I've increased the timeout to 30 seconds but we're also considering removing the witness because the app needs manual intervention in the case of failover anyway so we might as well make the DB failover manual as well. I'm fine with dealing with a planned failover but I'm not so comfortable with the case I described.

Best Answer

OK, so this is high safety without automatic failover with no witness.

Here's the official books online answer:

If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data)." Source

I have seen that to be true in my experience. In other words, disconnected, but available for reads and writes. The transaction log will grow until the situation is fixed, because it needs to keep the info to send to the mirror when it comes back around.

Note: I absolutely recommend setting up a test environment for yourself before you make any changes. Don't just take my word for it. You want a place to be able to stage the changes and need to test what happens for yourself before going live.

Edit: As for what happens when the mirror comes back, typically it will automatically resume in my experience, but I know of cases where it hasn't and TSQL had to be run to make it resume. Very easy to simulate in a demo environment to get yourself comfortable with it.

Related Question