Here’s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum that Kimberly and I talked about this morning (here in Barcelona).
Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session is running synchronously with the witness to allow automatic failover. A disaster happens to the site where the mirror and witness are, so the principal database is unavailable. I can’t seem to access the principal at all to bring it back online by removing the witness and the mirror and witness won’t be available for hours. What can I do?
A) The behavior you’re seeing (the principal database becoming unavailable) is expected. In a mirroring configuration with a witness, the principal needs to have quorum with (i.e. be able to see) at least one of the other partners, either the mirror, the witness, or both. If it can’t see either, it doesn’t know whether the witness and mirror can still see each other and the mirror may have brought itself online as the new principal. (Kimberly likes to say that the principal thinks the witness and mirror are conspiring against it :-)) In this case though, the customer knows that the mirror and witness are actually down and so he wants to bring the principal database back online.
I reproduced this situation in a VPC with three SQL Server 2008 instances running mirroring between them (the behavior is exactly the same in 2008 and 2005). I did a net stop on the mirror and witness servers and the principal database went offline. Trying to get into the principal database results in the following error:
USE [TicketSalesDB]; GO
Msg 955, Level 14, State 1, Line 1 Database TicketSalesDB is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened. Check the partner and witness connections if configured.
This is what I’d expect. The customer tried to remove the witness so let’s try that:
ALTER DATABASE [TicketSalesDB] SET WITNESS OFF; GO
Msg 1431, Level 16, State 4, Line 1 Neither the partner nor the witness server instance for database "TicketSalesDB" is available. Reissue the command when at least one of the instances becomes available.
That doesn’t work either because removing the witness needs to happen on one of the partners as well as the principal. The only way to get out of this situation is to break the mirroring partnership completely.
ALTER DATABASE [TicketSalesDB] SET PARTNER OFF; GO USE [TicketSalesDB]; GO
Command(s) completed successfully.