Hi All,

I have below questions on database mirroring.
I am using sql server 2008 sp3 Developer Edition.
I am using High Availability mode (Syncronous, witness & Automatic failover).

Here is what I observed and wanted to know reason for the below behavior.

A - Principal Server
B - Mirror Server
C - Witness Server

Scenario-1

The mirroring state is SYNCRONIZED. Meaning no more log records to be sent to Mirror db.
Now, say, Witness is down
After 3 minutes later, Principal is down.

At this point, there is only my Mirror instance is running with role as Mirror.
My question here is, how can I make this Mirror as Principal.
Since my Principal and Witness is down, I cannot run below command

use master
go
ALTER DATABASE <database_name> SET PARTNER FAILOVER
go

Scenario-2
Its a continuation question on Scenario-1.

Mirroring state is SYNCRONIZED
witness is down
Principal is down
only mirror is up and running but couldnt come online.
I tried starting Witness , hoping that , mirror and witness are now connected and can form Quorum and make Mirror as Principal, but that didn't happen.
I checked below command
select name,state_Desc from sys.databases

and I can see it is in recovering state and couldn't come online.
I Even tried below so that startup is initiated and recovery can run on that database.

use <dbname>
go

IT throws me an error saying, quorum cannot be forum. Error is
Database %.*ls is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened.  Check the partner and witness connections if configured.

Question here is, even after starting my witness server , why it couldnt form a Quorum and why didnt Mirror wasnt able to become new Principal.
From the Database monitor tool, it is very evident that, Mirror and Witness are connected but only Pricipal is disconnected as it is down.

Eventually, when I started the Principal server (i.e A), it became as Principal and Mirror was Mirror (there's no change in role)

One more thing, I want to mention here is, I am not using FQDN's. Does it make any difference. I have installed 3 sql instances on my Desktop and worked on it.
Again, I have tested by bring the Principal down and it automatically failover to the Mirror server (i.e. B).

Principal  --> TCP://TESTING-PC:5022
Mirror     --> TCP://TESTING-PC:5023
Witness    --> TCP://TESTING-PC:5024

Appreciate if someone can provide comments/suggestions on Scenario-1 and Scenario-2.

Thank you in advance.

Hello,

Seems you did not reffered to document i provided in question you asked about mirroring.Anyways I will try to answer your scenario

1.>>The mirroring state is SYNCRONIZED. Meaning no more log records to be sent to Mirror db.

Wrong assumption it means your mirroring is working and Principal is sending log records and mirror is trying to make changes and commit it.Its not like nno log records would be sent.

If mirror is down no effect now if principal is dowm your mirroring is gone and since Mirror database cannot for Quorum with witness no failover will happen and you need to manually break mirroring and bring Mirror DB online.

2.In second scenario you need first break the mirroring and then recovery on mirror database.Even if you start witness i Doubt it will form quorum

--run this on mirror database
use master
go
alter database db_name set partner off
go
restore database db_name with recovery

Please read below article for understandin how Quorum works

http://msdn.microsoft.com/en-us/library/ms189902.aspx

Hello,

Seems you did not reffered to document i provided in question you asked about mirroring.Anyways I will try to answer your scenario

1.>>The mirroring state is SYNCRONIZED. Meaning no more log records to be sent to Mirror db.

Wrong assumption it means your mirroring is working fine and Principal is sending log records to mirror  and mirror is trying to make changes and commit it.Its not like, no log records would be sent.

If Witness is down no effect on mirroring  now if principal is also down your mirroring is gone and since Mirror database cannot for Quorum with witness no failover will happen and you need to manually break mirroring and bring Mirror DB online.

2.In second scenario you need first break the mirroring and then recovery on mirror database.Even if you start witness i Doubt it will form quorum

--run this on mirror database
use master
go
alter database db_name set partner off
go
restore database db_name with recovery

Please read below article for understandin how Quorum works

http://msdn.microsoft.com/en-us/library/ms189902.aspx