3

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.

5

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.

  • In my case, the mirror didn't go down. At the time, I was running with a witness, and both the witness and mirror had the message "mirroring connection to "TCP://blah" has timed out for database "blah" after 20 seconds without a response" in their logs. I want to know what if the same happens but without a witness. – SandraV Mar 2 '16 at 23:52
  • As Kendra hinted at, setup a virtual machine environment to test this; it's very simple to understand when you try it and see what happens. Also, HI KENDRA LITTLE! – Max Vernon Mar 3 '16 at 2:02
  • To answer your last question, if the mirror comes back online, the mirroring session will resume automatically without intervention. However, if you force service on the mirror because the primary is down, the session won't automatically resume upon reconnect because there may have been data lost that you may want to extract from the principal. You can resume the session by issuing the ALTER DATABASE ... SET PARTNER RESUME command. When you resume, any data on the principal that was not on the mirror will be lost. – Robert L Davis Mar 3 '16 at 11:25
  • So, am I hung up on semantics? The responses keep talking about the mirror coming back but my mirror never went anywhere. With a witness, the DB fails over to the mirror. What I want to know is what state I'm in without a witness, stressing that this is a false-failure. I appreciate the recommendation for a test environment but I'm challenged to understand how I'd simulate a timeout being exceeded that affects one but not all of my DBs. I suppose it's not necessary. I can make them all timout... – SandraV Mar 3 '16 at 18:13
  • If the mirror database doesn't respond to the principal, it is a real failure (whether or not the mirror instance is online). Does that help? – kendra Mar 4 '16 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.