asked
Mirroring + log shipping together: log shipping breaks after fail over
We have several large (approaching 1 TB) databases that we are mirroring between two servers (high safety, no witness) and transaction log shipping to a third. The primary server went down unexpectedly so we failed over using SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS. Once the primary came back online we successfully reestablished mirroring. However, when attempting to apply the first transaction log backup created from when the mirror became primary to the third server we got the following error: "This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online." Running a full backup/restore to the third database to reestablish the transaction log shipping is very difficult due to the size of the databases. 1) What went wrong, why are we getting that error? 2) Is there any way to get the third database in a state where it will accept new transaction logs without transferring over a copy of a full backup? After failing over with data loss the old primary database had also forked from the mirror, but it is still able to resume mirroring (I assume it first rolls back transactions from when it was exposed to bring it to the point when it sent the last transaction to the mirror server). Why can't the third database do the same thing? 3) If this is an expected issue that can't be recovered from, what can we do to avoid this situation? So that even if the primary database goes down unexpectedly and we have to fail over, we do not have to rebuild the third, TLOG database. Thanks, Nathan
After a failover there's a procedure you have to do to make the new server a primary. [This is from the Books Online][1]: > To allow log shipping to continue > after database mirroring fails over, > you must also configure the mirror > server as a primary server, using the > same configuration you use for the > primary on the principal database. The > mirror database is in the restoring > state, which prevents the backup jobs > from backing up the log on the mirror > database. This ensures that the > mirror/primary database does not > interfere with the principal/primary > database whose log backups are > currently being copied by secondary > servers. To prevent spurious alerts, > after the backup job executes on the > mirror/primary database, the backup > job logs a message to the > log_shipping_monitor_history_detail > table, and the agent job returns a > status of success. [1]:
http://msdn.microsoft.com/en-us/library/ms187016.aspx
Failing over the database automatically sets the mirror database as primary. I'm not sure what "also configure the mirror server as primary" means to do other than the fail over. The following paragraph in that article describes exactly what happened after fail over: "...if mirroring fails over, the former mirror database comes online as the principal database. At that point, that database also becomes active as the log shipping primary database. The log shipping backup jobs that were previously unable to ship log on that database, begin shipping log." The log shipping continued from the mirror after the failover, the problem is that we get errors when applying the previous-mirror's log to the TLOG database. I'm pretty sure on a regular failover (without FORCE_SERVICE_ALLOW_DATA_LOSS) that the logs from the previous-mirror continue to apply fine and that the issue is only when failing over from an unexpected failure using FORCE_SERVICE_ALLOW_DATA_LOSS.
If the logs won't apply it means that they have a different set of LSNs showing them as being out of sequence. This means that the process broke. As far as i can tell, making this continue requires a manual intervention like was outlined above. Otherwise, the logs are from a different sequence chain, which means you can't restore them or use them within log shipping.
That may be, but why did it happen? After failing over with data loss the original primary database also forks from the mirror yet it can recover and resume the mirror, why can't the TLOG database? Is this the expected behavior when TLOG shipping from a database and doing a force service failover? If so, why and what can we do to avoid this situation? We want to have a primary, mirror and TLOG database but to having to rebuild the TLOG database in the event of an unexpected failure and force service. Thanks.
It *might* be possible to recover the log shipping if you have a Differential backup that was taken at the right time - see for some info
http://blog.perrywhittle.co.uk/page/Reinitialise-Log-Shipping-From-a-Primary-Differential-Backup.aspx
Why? Hard to say, but it looks like, based on the documentation, that you have to do a manual step to ensure that log shipping continues after a failover. This is one of the reasons why Microsoft introduced a whole different set of functionality in 2012 with the Availability Groups.
10 People are following this question.
