August 26, 2015 at 8:07 am
I have a DB mirror with FULL safety and automatic failover.
The mirror server will be unavailable for 4 hours for an upcoming maintenance window.
How best to manage the maintenance window?
What is best to do?
1. PAUSE mirroring?
2. Change to High Safety and remove witness?
3. other options
Thanks
August 26, 2015 at 3:07 pm
Is it the Principal or secondary that is having downtime?
August 26, 2015 at 3:18 pm
I have read the following related posts:
http://www.sqlservercentral.com/Forums/Topic1160643-391-2.aspx
http://www.sqlservercentral.com/Forums/Topic1270176-391-1.aspx
From these I have been able to gather the following:
With a FULL safety mirror with a Witness server, if the mirror server is unavailable for a period of time, the session will go into a DISCONNECTED state, and it will still process transactions (both READ and WRITE). The only thing to worry about is if the WITNESS loses contact with the Principal, then the DB will be unavailable as a quorum cannot be formed.
The one issue I have with this is the documentation for FULL safety says the following:
... every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
Synchronous operation is maintained in the following manner:
1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.
So I am slightly worried that in this mode with the MIRROR unavailable no write transactions will commit? Is this worry unfounded?
In HIGH PERFORMANCE MODE (safety = OFF) you shouldn't use a witness server
... whenever the SAFETY property is set to OFF, we strongly recommend that the WITNESS property also be set to OFF. A witness can coexist with high-performance mode, but the witness provides no benefit and introduces risk.
If the witness is disconnected from the session when either partner goes down, the database becomes unavailable. This is because, even though high-performance mode does not require a witness, if one is set, the session requires a quorum consisting of two or more server instances. If the session losses quorum, it cannot serve the database.
When a witness is set in a high-performance mode session, the enforcement of quorum means that:
If the mirror server is lost, the principal server must be connected to the witness. Otherwise, the principal server takes its database offline until either the witness or mirror server rejoins the session.
If the principal server is lost, forcing service to the mirror server requires that the mirror server be connected to the witness.
====================================
Conclusion
I could keep SAFETY FULL and remove the WITNESS during the extended outage.
The mirror would go into DISCONNECTED. But i am unsure if transactions would COMMIT?
I could SUSPEND the mirror session, but this seems to be effectively the same as DISCONNECTED, just forced?
I should remove the WITNESS from all configurations to remove the possibility of the loss of a WITNESS causing an outage.
I could use HIGH-PERFORMANCE (safety off) without a MIRROR which seems to be effectively the same as SAFETY FULL without a witness in either a DISCONNECTED for SUSPENDED state?
===================================
Further Conclusion
The documentation is far too ambiguous!!!
August 26, 2015 at 3:21 pm
The secondary.
I have just posted a reply with my research so far.
August 26, 2015 at 3:41 pm
My post seems to have been lost
Essentially my research allowed me to conclude the following:
Leaving the WITNESS in place it a very bad idea when the secondary/mirror server is going to be unavailable for a long period. If the Principal/Primary loses connection to the witness whilst the mirror is unavailable the quorum is lost and the DB is unavailable until quorum re-established.
FULL SAFETY WITHOUT WITNESS
If the mirror becomes unavailable the mirroring session will go into a DISCONNECTED state.
This seems to be effectively the same as SUSPENDING the session as transactions queue up a the principal.
What is ambiguous is whether transactions commit in either of these states or would no write transactions actually complete causing problems?
To quote from the Technet Documentation:
... every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
Synchronous operation is maintained in the following manner:
1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
HIGH PERFORMANCE WITHOUT WITNESS
In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database might lag somewhat behind the principal database, though typically the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded.
This unambiguously allows me to be confident that transactions would commit. Should i still PAUSE? Would the DISCONNECTED state be fine ??
=======================
Conclusion
The documentation is far too ambiguous.
Is FULL SAFETY WITHOUT WITNESS in a DISCONNECTED or SUSPENDED state effectively the same? Can transactions commit in this mode?
Is high-performance recommended without a WITNESS when there is an extended outage for the secondary/mirror planned? Is DISCONNECTED fine? Should I SUSPEND?
Important Note
The SQLServerCentral forum post adds to my concern about FULL SAFETY, to quote:
I would like to understand this concept some more, since the documentation I've found is somewhat vague.
We had an interesting scenario happen over the weekend. The mirror database server went down. The principal server was still up and running, but nothing could be updated. The data could only be read. It wasn't until the mirror server was brought back online that the principal database could be updated.
Sources:
http://www.sqlservercentral.com/Forums/Topic1270176-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1160643-391-2.aspx
https://technet.microsoft.com/en-US/library/ms179344(v=SQL.105).aspx
https://technet.microsoft.com/en-us/library/Cc917681.aspx#EJAA
https://technet.microsoft.com/en-US/library/ms187110(v=sql.105).aspx
August 27, 2015 at 12:47 am
Is anyone able to clarify?
August 27, 2015 at 2:17 am
simeharr (8/26/2015)
Leaving the WITNESS in place it a very bad idea when the secondary/mirror server is going to be unavailable for a long period. If the Principal/Primary loses connection to the witness whilst the mirror is unavailable the quorum is lost and the DB is unavailable until quorum re-established.
Yes, quite right, if the Primary and witness lose connectivity then the Principal database will go offline. For this reason if you expect a period of extended outage then remove the witness temporarily from the session.
simeharr (8/26/2015)
FULL SAFETY WITHOUT WITNESSIf the mirror becomes unavailable the mirroring session will go into a DISCONNECTED state.
This seems to be effectively the same as SUSPENDING the session as transactions queue up a the principal.
No, the two are different, if you pause the session then the remaining partners may well still be available, a disconnect happens because either a partner is not available or some other major issue exists.
simeharr (8/26/2015)
Transactions commit on the Primary but excessive log growth may occur as the log cannot be truncated until all transactions have been passed to the mirror (which is now not available)
every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
Synchronous operation is maintained in the following manner:
1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
This describes a synchronous mirror session where both partners are synchronised, when the partners are not synchronised the unsent log will accumulate and in severe cases may become too much for the servers to handle
HIGH PERFORMANCE WITHOUT WITNESS
In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database might lag somewhat behind the principal database, though typically the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded.
Again, unsent log can accumulate here
simeharr (8/26/2015)
This unambiguously allows me to be confident that transactions would commit. Should i still PAUSE? Would the DISCONNECTED state be fine ??
Transactions always commit at the Primary, by using either synchronous or asynchronous sessions you're just controlling whether they are passed immediately to the mirror and hardened (synchronous) or left to their own devices (asynchronous)
The loss of the mirror partner in the synchronous session will still commit transactions on the Primary but since the session is not synchronised with the partner or it is paused they start to accumulate inside the t-log
simeharr (8/26/2015)
Is high-performance recommended without a WITNESS when there is an extended outage for the secondary/mirror planned? Is DISCONNECTED fine? Should I SUSPEND?
Do not use a witness with high performance mirror sessions, the asynchronous session mode has the potential to lose data.
Use a witness for high safety only.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 27, 2015 at 2:40 am
Thank you for your thorough reply.
Transactions always commit at the Primary, by using either synchronous or asynchronous sessions you're just controlling whether they are passed immediately to the mirror and hardened (synchronous) or left to their own devices (asynchronous)
This seems to contradict the TechNet documentation:
Synchronous operation is maintained in the following manner:
1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.
Another comment
No, the two are different, if you pause the session then the remaining partners may well still be available, a disconnect happens because either a partner is not available or some other major issue exists.
Good point, but I wanted to clarify whether their effect was the same? There is no effective difference between a SUSPEND and DISCONNECT during the period of DISCONNECTION. It only dictates how they respond with the mirror becomes available again?
August 27, 2015 at 2:37 pm
simeharr (8/27/2015)
Thank you for your thorough reply.
You're welcome
simeharr (8/27/2015)
This seems to contradict the TechNet documentationSynchronous operation is maintained in the following manner:
1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.
2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.
3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.
On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
High-safety mode protects your data by requiring the data to be synchronized between two places. All the committed transactions are guaranteed to be written to disk on the mirror server.
Erm, no it doesn't, you're completely missing the detail below which precedes the section you posted above 😉
MSDN - Database Mirroring Operating Modes
To achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. Note the wait for this message increases the latency of the transaction.
The time required for synchronization depends essentially on how far the mirror database was behind the principal database at the start of the session (measured by the number of log records initially received from the principal server), the work load on the principal database, and the speed of the mirror system. After a session is synchronized, the hardened log that has yet to be redone on the mirror database remains in the redo queue.
simeharr (8/27/2015)
Good point, but I wanted to clarify whether their effect was the same? There is no effective difference between a SUSPEND and DISCONNECT during the period of DISCONNECTION. It only dictates how they respond with the mirror becomes available again?
Yes, the end result is they both accumulate unsent log and the principal runs exposed
.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply