Nasty split brain situation | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Nasty split brain situationYesterday I had a nasty mirroring problem. The principal and the mirror server are both running SQL Server 2005 64-bit Enterprise Edition. Witness is running Workgroup Edition. We are running in high availability mode (SAFETY ON). 3 nights before something strange happened and 1 database failed over (out of the six being mirrored) for some unknown reason. The other 5 didn’t. We failed that one back and all seemed ok over the weekend.
We came in Monday and found that the main live OLTP database was showing as the Principal on BOTH servers. this is the "split brain" scenario that the presence of the witness is supposed to prevent. Both databases were accessible with a USE statement – clearly not right. I pondered what to do, eventually I decided to remove mirroring from this database. That was ok until suddenly a few minutes later we realised the (original) Principal was in recovery! uisers of course were kicked out/unable to connect. I tried to force recovery with RESTORE DATABASE dbnmae WITH RECOVERY but it complained users were connected! I had to KILL the users then recovery proceeded and the database became available again. I forced the mirror offline to prevent accidental usage. This is obviously a nasty situation where mirroriing – which is supposed to prevent downtime – actually caused it instead. I intend to log a call with CSS but I wanted to warn other users if they encounter something similar – it has shaken my confidence in mirroring quite severely.
P.S. Both servers are SP1. I don’t see anything in the fix list for SP2 for something like this though.