Recently a customer was concerned to implement a fail over strategy for the Dynamics AX database. SQL Server 2008 R2 Standard Edition was in place. The underlying hardware was not capable to handle database mirroring. Therefore, log shipping and manual failover was chosen. However, the job on the second server refused to restore the .trn files. We managed to reproduced the problem in our lab environment with two SQL Instances (SQL1, SQL2) and a Northwind Demo Database.
Log Shipping Configuration
There are 2 SQL Servers in place, called SQL1 (primary) hosting a Northwind Database and SQL2 (traget). There are two file shares on the secondary server. LSBackup is used to backup .trn files from the primary server. LSCopy is used by the secondary server to copy .trn files and restore these files to the replicated database.
The backup at the primary server SQL1 was scheduled to run every 5 minutes. Files older than 1 hour in the LSBackup folder will be deleted.
The target database at the secondary server SQL2 was initialized by the configuration wizard
The copy job at the secondary server SQL2 was scheduled to run every 5 minutes. This job copies .trn files from the LSBackup folder into the LSCopy folder. Files older than 1 hour in the LSCopy folder will be deleted.
The restore job at the secondary server SQL2 was scheduled to run every 5 minutes.
Log Shipping Behavior
The log shipping configuration seemed to be correct. The backup job on the primary server SQL1 placed the backup and .trn log backup files in the LSBackup folder. The copy job on the secondary server copied the .trn files to the LSCopy folder.
An initial check of the restore job history showed no errors.
The Problem
A detailed look at the job revealed that the .trn files were not restored. The backup job reported “Skipped log backup file” because it “Could not find a log backup file that could be applied to the secondary database”"
The Reason
A maintenance plan was in place on the primary server SQL1. This maintenance plan took a full backup at midnight and a transaction log backup every 15 minutes. The interleaving transaction log backups resulted in holes within the transaction log chain. Therefore the restore job on SQL2 was not able to apply the copied transaction log files.
The Solution
At the Northwind DB properties on primary server SQL1, the transaction log replication setting to SQL2 was removed. The wizard also removed the copy and restore jobs at SQL2. The replicated Northwind DB at SQL2 was removed manually. The transaction log shipping configuration was deactivated at the Northwind DB on SQL1. All files in the LSBackup and LSRestore folders were deleted. The maintenance plan was deactivated.
Next the transaction log shipping configuration was redone, as described above. Again the target database at SQL2 was initialized by a full backup of the Northwind DB from SQL1. Finally the secondary server SQL2 was able to restore the .trn files.
