Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB mirroring questions .. Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 3:21 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 11:36 AM
Points: 774, Visits: 1,742
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.

Post #1541793
Posted Tuesday, February 18, 2014 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:36 PM
Points: 6,759, Visits: 14,408
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"
Post #1542504
Posted Wednesday, February 19, 2014 6:09 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:06 AM
Points: 763, Visits: 2,287
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.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1542952
Posted Wednesday, February 19, 2014 10:34 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 11:36 AM
Points: 774, Visits: 1,742
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.


Post #1543327
Posted Wednesday, February 19, 2014 10:38 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 11:36 AM
Points: 774, Visits: 1,742
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
Post #1543330
Posted Thursday, February 20, 2014 5:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:06 AM
Points: 763, Visits: 2,287
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.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1543433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse