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.
2 thoughts on “Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?”
Hi Paul,
Just wanted your thoughts on the below memory warning.
I’m having a server with 4.5gb of physical ram, AWE enabled along with /3GB and /PAE switches. SQL server is updated with SP4 and has a fixed mem of 3gb. The problem here is twice a day i receive a WARNING: "Failed to reserve contiguous memory of Size= 65536". We checked the network packet size and it is 4096 from both SQL as well as from the application so that’s not the case. We suspect that the mem-to-leave area could have fragmented but not sure if it is. Did a trace and see that one application is doing a select query and nothing else. Let me know if you have come across such.
Thanks for your time.
Oops – this fell through the cracks. Yes, sounds like virtual address space fragmentation. Unfortunately that’s not my speciality so I don’t know a quick fix for you. I suggest you search through the Knowledge Base articles or checkout Slava Oks’ blog – he wrote the memory manager for 2005.
Thanks