SQL Server 2005: Unable to manually fail over at the mirror in High Availability operating mode

Question

According to the book I'm reading, one is supposed to still be able to manually fail over at the mirror in high availability operating mode when the principal fails while the witness is offline.  When I try to fail over at the mirror in this scenario, the following error message is displayed:

Msg 955, Level 14, State 1, Line 1

Database <database_name> is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.

This seems to say that it's impossible to manually fail over to a mirror in High Availability operating mode when the principle fails while the witness is offline. 

Could the book be wrong, or am just being silly?

Answer 1

Ledigimate_,

You are running this command at the Mirror, correct?

-Sean

Answer 2

Yes, like so:

USE <database_name>
ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

I guess the book hasn't said enough?  I've just come across this post:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/d4775805-74a0-4706-9be6-7c69d38eed5f

After reading the post at the above URL, I executed the following commands:

USE master
ALTER DATABASE <database_name> SET PARTNER OFF
RESTORE DATABASE <database_name> WITH RECOVERY
GO

and it worked.  I guess the book just forgot to tell me.

thanks to Jacob Sebastian for his answer in the above linked thread.

Answer 3

Although I wouldn't call the above method "failing over a database mirroring session at the mirror", it does recover the database in the event that both the principal and witness is down.  It seems like the only solution in such a case.  Only I don't know if a "split brain problem" can emerge if, after restoring the database in such manner, the principal miraculously comes back online.

Answer 4

You should run this from the mirror as Sean mentions above:

ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


It looks to me like you are running into a situation where the witness and the mirror are not in contact with the principle.  When that happens in High Availability mode the Principle will stop taking connections just in case we do have a "split brain" event.

Setting the partner OFF and restoring the database is just removing mirroring, it is not failing it over.

Answer 5

You should run this from the mirror as Sean mentions above:

ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

I did try that also prior to my starting this thread, and the error message displayed was

Msg 1455, Level 16, State 106, Line 2

The database mirroring service cannot be forced for database <database_name> because the database is not in the correct state to become the principal database.

I might have done something wrong somewhere because the book makes it seem like what you said should work.

To create this situation, I shut down both the witness instance and the instance hosting the principal, to simulate a disaster in which both the witness and the principal is lost, the witness being lost first and then the principal.

Answer 6

When you lose Quorum (ie: 2 of the 3 machines) the mirror will not accept connections.  The logic behind this is that you want to make sure changes are only happening in one place.  When this happens you recover by running the command you listed in your previous note.

Answer 7

Thank you, Michael_W and Sean :)  It all makes sense now.  I forgive the author of the book.  He's probably the top DBA in the world and a regular contributor to the SQL Server forums, and he's the one I've learned about SQL Server from the most.  Thanks to him too.

Both Michael_W and Sean's answers are correct, given the information you had from me at the time you answered.  There's no doubt Sean would've given an answer similar to that of Michael_W, had I posted my previous comment sooner, so thanks to you both.