SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB mirroring questions ..


DB mirroring questions ..

Author
Message
Oracle_91
Oracle_91
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 1742
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.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26641 Visits: 17336
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" ;-)
LightVader
LightVader
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 3002
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.
Oracle_91
Oracle_91
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 1742
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.
Oracle_91
Oracle_91
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 1742
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
LightVader
LightVader
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 3002
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search