April 30, 2012 at 9:22 am
Greetings all,
Mirroring on my SQL 2005 instance is set up with a Witness (on a 3rd server). Last week, there was a reported problem with the Primary databases being "Principal, Disconnected". I put them back in order and ran a test last week-end by stopping all SQL services on the Mirror server. This should have put the primary databases into "Principal running exposed" state, but instead they all showed "Principal, Disconnected". Everything righted itself when I restarted SQL services on the Mirror server, but why Disconnected?
When I query sys.database_mirroring, it shows the Mirroring_Witness_Name, and that the witness state is connected. All as expected. So what is going on?
Thanks,
Elliott
May 1, 2012 at 11:01 pm
There is no such state as "Principal running exposed". The proper state for the scenario you outlined is "Disconnected".
The possible states for a mirroring session are:
DISCONNECTED -- partner disconnected from other partner
SYNCHRONIZED -- partners connected and session synchronized
SYNCHRONIZING -- partners connected and session in process of getting synchronized
PENDING_FAILOVER -- Failiver initiated and in process
SUSPENDED -- Mirroring session stopped
UNSYNCHRONIZED -- partners connected and session not synchronized
NULL -- database is not mirrored
May 2, 2012 at 5:39 am
Hi Robert,
For SQL 2005, State 12 is defined as "Principal Running Exposed"; the definition is: "This state occurs on the principal server instance when the operating mode is high protection (synchronous) and the principal cannot connect to the mirror server instance." (Phillip Garding, Microsoft Corporation, MSDN article, April 7, 2006)
The practical problem is that the prrincipal database in the mirroring session, is not "seeing" the witness at the same time that communication is broken with the mirror database. So instead of being available for our production needs, the database(s) reports "Disconnected" and we have no access.
And yet there apparently is communication with the witness, or at least we find no evidence that communication is broken. I'm looking for a way to diagnose and troubleshoot the problem.
Thanks for your time,
Elliott
May 2, 2012 at 8:20 am
Okay, so you're not talking about the state in sys.database_mirroring. You're talking about the state in the DATABASE_MIRRORING_STATE_CHANGE event (for WMI, event notifications, etc). It sounds like from your description that you are viewing the state in Object Explorer in SSMS. The display of "Principal, Disconnected" does not mean that the principal is disconnected from the mirror and the witness. It means that the principal is disconnected from the mirror. If it was not connected to the witness, the state would be "Suspended" and the principal would be offline.
You can still connect to a principal that is disconnected. So something else is going on if you could not connect.
What is your SQL version? Which SP level, etc?
May 2, 2012 at 8:53 am
Sorry for the delay in responding to your questions; I did not get a notification.
Anway, the Principal Server is SQL Server 2005, Enterprise Edition 64 bit, SP3.
The Mirror Server is SQL Server 2005, Enterprise Edition 64 bit, SP2.
The Witness is SQL Server 2005, Express Edition 32 bit, SP1.
Thanks for any suggestions on understanding the problem.
Elliott
May 3, 2012 at 11:59 am
You have an unusual set up. Is there are reason why your mirror is still on SP2? Your mirror and principal should be on the same build level. You shouldn't be able to mirror down-level that far, but it would appear that there are no breaking changes from SP2 to SP3. It is possible that when you fail over to the mirror, the database upgrade process will fail and the mirror won't come online.
I would upgrade the mirror to the same level and see if this issue continues. I'd also consider upgrading the witness to SP3 as well.
May 3, 2012 at 2:04 pm
Thanks Robert. I'll see about bringing up the service pack levels. Also, this week-end I may be able to experiment. If I see anything interesting, I'll post it here.
Elliott
May 9, 2012 at 4:01 pm
Nothing too exciting to report.
On the week-end, I brought the Mirror server up to SP3 to be in parallel with the Primary server. Then I experimented with various "faults" to see how the mirrors would react.
First, I shut down the SQL Services on the Witness instance. That did not produce any observable effects on the Primary server. Other than the loss of the ability to have an automatic fail-over, the databases stayed in their synchronized mode.
I re-started the witness, and shut down SQL Services on the Mirror server. That produced the display of "Principal, Disconnected" on the description of all the mirrored databases on the Primary server (via Management Studio). However, each of the Principal databases was still accessible, so this is the desired behavior.
thanks for the suggestion on the upgrade. I'll look into upgrading the Witness (SQLExpress) when I get a chance.
Elliott
May 9, 2012 at 8:33 pm
Thanks for the update! This is a very interesting case.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply