February 14, 2014 at 3:21 pm
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.
February 18, 2014 at 5:42 am
Are the servers on separate sites?
In this scenario you should work to get the principal online as soion as possible, i've never tested but with both witness and principal down you would to force service on the mirror. Work to get one of the partners back online would be the first operation to perform rather than just blindly forcing the mirror online.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2014 at 6:09 am
Oracle_91 (2/14/2014)
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.
In scenario 1, the only way you can make Mirror server the "principal" server would be to disable the mirroring altogether. The reason for this is that without a second server to communicate with (either the witness or principal), the mirror server can't tell if the other servers are down or if it's isolated and it just can't communicate with them.
Think about this problem. If you lose communication between your main site and your secondary site, your mirror won't be able to communicate with the witness or principal servers (both servers are up, communication is just down). If you bring up the mirror server as the principal database, it has no way of communicating that with the other two servers. Now your users could potentially make (different) changes in both your principal and mirror servers. When communication is restored, SQL server won't know how to merge these changes together.
In scenario 2, the error message seems to imply that the mirror still can't communicate with the witness and principal servers. I haven't run into that scenario in production yet, and I'm not sure how it's supposed to work. The testing I did was long enough ago that I can't remember if I even tested this scenario, let alone the results.
February 19, 2014 at 10:34 pm
Hi Perry,
I have tested the scenarios on my personal laptop. I have created 3 sql instances and setup mirroring. All are sql 2008 developer edition , service pack 3.
February 19, 2014 at 10:38 pm
Hi LightVader,
Thanks for the answer for scenario-1. I got to know the same thing from my peers. We need to break the mirroring and bring it online.
use master
go
alter database <dbname> set partner off;
go
restore database <dbname> with recovery;
go
February 20, 2014 at 5:42 am
Oracle_91 (2/19/2014)
Hi LightVader,Thanks for the answer for scenario-1. I got to know the same thing from my peers. We need to break the mirroring and bring it online.
use master
go
alter database <dbname> set partner off;
go
restore database <dbname> with recovery;
go
As long as you keep in mind that if you do it that way, when the principal and witness come back online, you will have to go through the whole mirroring setup process again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply