September 19, 2011 at 2:04 pm
Hi,
We are running database mirroring synchronous mode (with witness) over three sites.
The principal and Mirror databases are running on Windows Server 2008 R2 64bit, and SQL Server 2008 R2 Enterprise 64bit.
The Witness is running on Windows server 2003, SQL Server Express edition.
I needed to perform some maintenance on the network, so I paused mirroring on the principal before working on the firewall.
I then took down the firewall at the principal site, expecting the principal database to carry on as normal (as the mirror was paused), however the principal database became unavailable!
I understand that the database would go offline if I didn't pause mirroring (as it would lose connection to the mirror and witness), however I didn't expect it to go offline when it was paused!
Am I doing something wrong?
Thanks for your help
September 19, 2011 at 2:11 pm
in synchronous mode with a witness the loss of the mirror partner and the witness will take the principal database offline.
Why did you pause the mirror if you were taking the witness down?
If you want to pause the mirror and take the witness down you would be better to remove the witness and add it back later. The principal would run exposed and the log would grow continuously until mirroring is resumed.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 2:30 pm
Thanks for the fast reponse - much appreciated!
I paused the mirror as I was taking the firewall at the principal site down for maintenance.
With the firewall down, the witness and mirror wouldn't be able to connect to the principal. As I didn't want the principal database to go offline, I paused the mirror.
Was this wrong?
Thanks again
September 19, 2011 at 2:41 pm
If you know you're taking both down, remove the witness first then pause the mirror.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 2:47 pm
Perry Whittle (9/19/2011)
If you know you're taking both down, remove the witness first then pause the mirror.
Does the order that I take things down matter then?
Surely if I pause the mirroring on the principal, then the principal shouldn't go offline?
Thanks again
September 19, 2011 at 2:55 pm
Books Online
The session loses quorum.If all the server instances become disconnected from each other, the session is said to have lost quorum. As server instances reconnect to each other, they regain quorum with each other.
If the principal server reconnects with either of the other server instances, the database becomes available.
If the principal server remains disconnected, but the mirror and witness reconnect to each other, automatic failover cannot occur because data loss might occur. Therefore, the database remains unavailable, until the principal server rejoins the session.
When all three server instances have reconnected, full quorum is regained, and the session resumes its regular operation.
Important:
When a session has a partner-to-partner quorum, if either partner loses quorum, the session loses quorum. Therefore, if you expect the witness to remain disconnected for lots of time, we recommend that you temporarily remove the witness from the session. Removing the witness removes the requirement for quorum. Then, if the mirror server becomes disconnected, the principal server can continue to serve the database.
Witness first then mirror, remove the need for quorum 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 3:07 pm
I understand what you are saying, I'm just confused as I thought pausing the mirroring would have basically disabled eveything, leaving the principal database unaffected and accessible.
What does the "pause" option do then?
Thanks again.
September 19, 2011 at 3:14 pm
yes, if you want to take the mirror down for a maintenance op or some other business reason then this is what the pause is for. If you plan to take witness and mirror then you are removing quorum which in High Safety with Automatic Failover WILL take the principal offline.
If both witness and mirror are planned to go offline you must remove the need for quorum first, i.e. remove the witness.
Adding or removing the witness is a standard operation and will not affect the mirroring it will merely enable\disable the automatic failover
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 3:21 pm
Perry Whittle (9/19/2011)
yes, if you want to take the mirror down for a maintenance op or some other business reason then this is what the pause is for. If you plan to take witness and mirror then you are removing quorum which in High Safety with Automatic Failover WILL take the principal offline.If both witness and mirror are planned to go offline you must remove the need for quorum first, i.e. remove the witness.
Adding or removing the witness is a standard operation and will not affect the mirroring it will merely enable\disable the automatic failover
Thanks
So just to clarify, in my case where I "paused" the mirror, the principal database shouldn't have gone offline when I broke the comms link (by shutting down the Firewall)?
September 19, 2011 at 3:32 pm
rdavies 35008 (9/19/2011)
the principal database shouldn't have gone offline when I broke the comms link (by shutting down the Firewall)?
Yes it should, because you wiped out the witness too which provided quorum.
Reconnect your mirror session with witness then do the following in order, the principal should stay online
Remove the witness from the session
Pause the mirror or just shutdown the firewall
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2011 at 3:40 pm
Perry Whittle (9/19/2011)
rdavies 35008 (9/19/2011)
the principal database shouldn't have gone offline when I broke the comms link (by shutting down the Firewall)?Yes it should, because you wiped out the witness too which provided quorum.
Reconnect your mirror session with witness then do the following in order, the principal should stay online
Remove the witness from the session
Pause the mirror or just shutdown the firewall
I've clearly missunderstood the "Pause" function......
So even if the mirroring is paused, you still need to maintain quorum?
September 19, 2011 at 4:40 pm
rdavies 35008 (9/19/2011)
So even if the mirroring is paused, you still need to maintain quorum?
if you're running with a witness, yes
rdavies 35008 (9/19/2011)
I've clearly missunderstood the "Pause" function......
Its all detailed here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2011 at 4:41 am
Thanks
I have another question if I may?
If we have the system running as normal (not paused), and the principal and Witness lose connection with the mirror server (Firewall fails at the site with the mirror server), how does the principal database handle the disconnection? Does the principal database freeze at all (even for 1 sec) or does it simply carry on as normal.
The reason I ask is we have applications configured to use the principal database, and we are experiencing brief disconnects when we lose the mirror site (witness can see principal, however witness and principal cannot see mirror).
As a test, if we pause mirroring and then stop the firewall at the mirror site (witness can see principal, however witness and principal cannot see mirror) then we experience no disconnect in our applications to the principal database (which is good), however when we "resume", it appears that we lose connection (although brief) to the principal database. Is this expected? Is the principal waiting for the mirror database to "catch up/resync" before the principal allows connections?
If you can answer this, it will help us a great deal with understanding what on earth is going on lol.
Thanks again
September 21, 2011 at 12:34 am
Did you have any thoughts on this last question Perry?
Your help is really appreciated.
Thanks again
September 21, 2011 at 3:24 am
Its all detailed in the link i sent you.
There are 3 possible quorum modes
Full Quorum.
This is where both partners and witness are connected
Witness to partner.
This is where the witness is connected to a partner (principal or mirror)
Partner to partner.
This is where principal and mirror are connected but witness is lost
rdavies 35008 (9/20/2011)
If we have the system running as normal (not paused), and the principal and Witness lose connection with the mirror server (Firewall fails at the site with the mirror server), how does the principal database handle the disconnection? Does the principal database freeze at all (even for 1 sec) or does it simply carry on as normal.
If the principal loses connection with the mirror and the witness the database will be unavailable.
rdavies 35008 (9/20/2011)
The reason I ask is we have applications configured to use the principal database, and we are experiencing brief disconnects when we lose the mirror site (witness can see principal, however witness and principal cannot see mirror).As a test, if we pause mirroring and then stop the firewall at the mirror site (witness can see principal, however witness and principal cannot see mirror) then we experience no disconnect in our applications to the principal database (which is good), however when we "resume", it appears that we lose connection (although brief) to the principal database. Is this expected? Is the principal waiting for the mirror database to "catch up/resync" before the principal allows connections?
If you can answer this, it will help us a great deal with understanding what on earth is going on lol.
Thanks again
I believe what you could be seeing here is a performance hit when they try to catch up which may affect connections. What distance do you have between the sites? Have you thought of adjusting the failover timeout from the default 10 secs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply