August 16, 2011 at 10:04 am
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. Did this happen because there was a transaction that couldn't be committed on the mirror database? The documentation seems to indicate that the principal server should have been UNAFFECTED and just run exposed; it didn't seem to work that way.
There was network maintenance and SAN issues during the same time. Could the issue of been mirror related or just network/SAN related?
SQL Server 2008 SP2 Enterprise
Windows Server 2003 R2 Enterprise x64
Any help in understanding this would be greatly appreciated.
Also, there was no witness server.
August 16, 2011 at 10:40 am
DBAgal (8/16/2011)
since the documentation I've found is somewhat vague.
When running in High availability mode Books Online is quite clear and quotes the following
Books Online
In high-availability mode, the loss of a partner has the following effect:If the principal server becomes unavailable, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
If the mirror server becomes unavailable, the principal and witness continue.
If a high-availability mode session loses the connection to the witness, quorum requires both partners. If either partner loses quorum, both partners lose quorum, and the database becomes unavailable until quorum is re-established. This quorum requirement ensures that the database never runs exposed (that is without being mirrored).
If you expect the witness to remain disconnected for a significant amount of time, we recommend that you temporarily remove the witness from the session.
Basically, if you expect long outages remove the witness to ensure the primary is still available!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2011 at 10:43 am
Basically, if you expect long outages remove the witness to ensure the primary is still available!
There is no witness server.
August 16, 2011 at 10:46 am
so are you running in High-Performance(asynchronous) or High-Protection(synchronous) mode?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2011 at 11:08 am
High-Protection(synchronous)
August 16, 2011 at 11:34 am
please execute the following against the primary and post the results
select * from sys.database_mirroring
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2011 at 11:45 am
Since I wasn't sure of what was happening in High Safety (sync mode), I switched to High Performance (async mode) this morning.
Here are the results of the query:
365A10FF5D-4F04-4009-8A0D-6478CF04D1894SYNCHRONIZED1PRINCIPAL11OFF2TCP://server.domain.com:5022server\instance0UNKNOWN9765300000024670000110NULLUNLIMITED9765300000024670000197653000000246700001
August 16, 2011 at 12:07 pm
needed to see details of the configuration this morning not after you switched to asynch
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2011 at 12:22 pm
In High-Safety/Protection/Synchronous mode, you're in a two-phase commit situation, so having the mirror offline is not an option. If you don't have a monitor in place for this scenario, or you can expect either network latency or outages (both creating transaction commit latency of varying forms), you shouldn't be running in High-Safety/Protection/Synchronous mode. You need to be in High-Performance/Asynchronous mode to make it thru situations such as the one you encountered.
For example, at a former employer we tried High-Safety/Protection/Synchronous mode over a highly redundant WAN, but with the number of transactions/sec, the latency for mirroring caused the applications to perform horrendously due to the two-phase commit. And these were reasonably powerful individual SQL Server nodes in both locations. The switch to High-Performance/Asynchronous mode solved the problem. We were running with a witness btw (recommended by me at least).
HtH,
-Patrick
August 16, 2011 at 12:29 pm
Patrick Purviance (8/16/2011)
In High-Safety/Protection/Synchronous mode, you're in a two-phase commit situation, so having the mirror offline is not an option. If you don't have a monitor in place for this scenario, or you can expect either network latency or outages (both creating transaction commit latency of varying forms), you shouldn't be running in High-Safety/Protection/Synchronous mode. You need to be in High-Performance/Asynchronous mode to make it thru situations such as the one you encountered.For example, at a former employer we tried High-Safety/Protection/Synchronous mode over a highly redundant WAN, but with the number of transactions/sec, the latency for mirroring caused the applications to perform horrendously due to the two-phase commit. And these were reasonably powerful individual SQL Server nodes in both locations. The switch to High-Performance/Asynchronous mode solved the problem. We were running with a witness btw (recommended by me at least).
HtH,
-Patrick
Ah ok. So in High-Safety/Protection/Synchronous mode, both commits on the principal and mirror need to be totally committed, otherwise the mirror breaks in both places. Good to know. It was a mistake to have the db in High-Safety/Protection/Synchronous anyways.
August 16, 2011 at 2:53 pm
That's not entirely correct!
High Safety - High Availability and High Safety - High Protection modes will still allow connections to the database even when there is a mirror failure.
It's when you run in High Safety - High Availability mode, if you lose the witness and the mirror, the principal then becomes unavailable. Subsequent access attempts to the principal database produce the following message
Database blah is enabled for mirroring, but the database lacks quorum, the database cannot be opened. Check the witness and the partner
Also note, you may modify the default database mirroring session timeout of 10secs by using the following
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2011 at 7:19 am
Perry Whittle (8/16/2011)
That's not entirely correct!High Safety - High Availability and High Safety - High Protection modes will still allow connections to the database even when there is a mirror failure.
It's when you run in High Safety - High Availability mode, if you lose the witness and the mirror, the principal then becomes unavailable. Subsequent access attempts to the principal database produce the following message
Database blah is enabled for mirroring, but the database lacks quorum, the database cannot be opened. Check the witness and the partner
Also note, you may modify the default database mirroring session timeout of 10secs by using the following
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30
When you say, "still allow connections to the database even when there is a mirror failure," does that mean just connections to read the data or connections that can update/delete/insert data? The behavior I saw when the mirror was lost, no witness server set-up, was that the principal database had an uncommitted transaction on the mirror, therefore the principal database could not be updated until the transaction committed on the mirror, once the mirror was brought back up. Does that sound right?
Principal -> Transaction Committed
Mirror -> Transaction Not Committed, because mirror db offline
Principal -> Since in high safety mode, database cannot be updated, since transaction not committed on mirror
Mirror -> Bring server back online, open transaction committed
Principal -> Data can be updated again
August 17, 2011 at 7:57 am
Loss of the mirror
If the mirror fails, the principal continues functioning, but the mirroring state is
DISCONNECTED and the principal is running exposed. Once the mirror database
becomes operational, it automatically assumes the role of the mirror and starts
synchronizing with the principal. For as long as the mirroring state stays
DISCONNECTED, the transaction log space on the principal cannot be reused, even if
you back up the transaction log. If the log file grows and reaches its maximum size limit
or runs out of disk space, the complete database comes to a halt. To prevent this you
have two options—either plan for enough disk space for the transaction log to grow and
bring back the mirror database before the space fills up, or break the database
mirroring session.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2011 at 8:33 am
"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. "
So, if the mirror database is offline and can't acknowledge the transaction right away, the transactions just continue to queue up until the mirror database becomes available? The principal continues to be unaffected and continue processing transactions, even if the mirror database server is offline. Am I understanding this correctly?
That still doesn't explain my problem where the application couldn't connect to the principal database without the mirror database server being online; the application must have been experiencing a problem unrelated to the mirroring.
August 17, 2011 at 8:44 am
DBAgal (8/17/2011)
So, if the mirror database is offline and can't acknowledge the transaction right away, the transactions just continue to queue up until the mirror database becomes available? The principal continues to be unaffected and continue processing transactions, even if the mirror database server is offline. Am I understanding this correctly?
yes at this point the session is marked as disconnected so transactions will backup in the log and cannot be truncated until mirroring resumes and is synchronized. Suspending a mirroring session has the same effect, log entries backup until the session is resumed and has to play catch up.
DBAgal (8/17/2011)
That still doesn't explain my problem where the application couldn't connect to the principal database without the mirror database server being online; the application must have been experiencing a problem unrelated to the mirroring.
possibly, but what you may have seen is some latency in the session being marked as disconnected
Have a read of this, it may help. It quotes the following
Technet
Loss of the principalIf the principal fails, the failover scenario depends on the transaction safety level and whether you have a witness.
Scenario 1: Safety FULL with a witness
This scenario provides the high safety with automatic failover. In the event of the failure of the principal, the mirror forms a quorum with the witness. Automatic failover will take place, thereby minimizing the database downtime.
For example, prior to the failure, Server_A and Server_B acted as principal and mirror, respectively. Server_A fails. Following the automatic failover, Server_B takes on the role of the principal. However, since there is no mirror after the failover, the mirroring state is DISCONNECTED and the principal is exposed. Once the database on Server_A becomes operational, it automatically assumes the role of the mirror.
Scenario 2: Safety FULL without a witness
This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.
For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:
ALTER DATABASE <database name> SET PARTNER OFF
RESTORE DATABASE <database name> WITH RECOVERYOnce Server_A becomes available, you need to re-establish the mirroring session.
Scenario 3: Safety OFF
If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.
For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSSOnce the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.
Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Scenario 2: Safety FULL without a witness. Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.
Loss of the mirror
If the mirror fails, the principal continues functioning, but the mirroring state is DISCONNECTED and the principal is running exposed. Once the mirror database becomes operational, it automatically assumes the role of the mirror and starts synchronizing with the principal. For as long as the mirroring state stays DISCONNECTED, the transaction log space on the principal cannot be reused, even if you back up the transaction log. If the log file grows and reaches its maximum size limit or runs out of disk space, the complete database comes to a halt. To prevent this you have two options—either plan for enough disk space for the transaction log to grow and bring back the mirror database before the space fills up, or break the database mirroring session.
Loss of the witness
If the witness server fails, database mirroring continues functioning without interruption, except that automatic failover is not possible. Once the witness becomes operational, it automatically joins the database mirroring session.
Loss of the mirror and the witness
Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database <dbname> is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.”
If the mirror or the witness cannot be brought back online quickly, then the only way to resume database service is to terminate the database mirroring session. To do this, you execute the following command after connecting to the master database of the principal server:
ALTER DATABASE <db_name> SET PARTNER OFFNote that you need to execute a SET PARTNER OFF command, and not a SET WITNESS OFF. SET WITNESS OFF will not work in this situation.
Once the mirror becomes available, you can re-establish the database mirroring session. The principal will start sending the log information to the mirror, and the mirror will eventually catch up. If you backed up the transaction log after terminating the database mirroring session, you need to restore the transaction log backup on the mirror before you can re-establish the database mirroring session. There is no need to do a full database backup / restore. Once the witness becomes available, you can join in the witness as well, but you have to establish the mirroring session with the mirror before the witness can join in.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply