Deploy SQL Server Log Shipping as a Disaster Recovery Solution

By: Steven Wort, Ross LoForte, Brian Knight

Even if an organization already has a local high-availability solution, regardless if it is based around Windows failover clustering or log shipping, an alternative, site-to-site solution is a vital tool to employ. If you deploy log shipping to a secondary server at a remote location, you can protect your organization from a power grid failure or local disaster.

If the transaction log files in the backup folder or the active transaction log are not accessible, such as in a disaster where the primary server cannot be restarted because of a power grid failure, you may stitch the primary server’s active transaction log and transaction log files together by using a third-party transaction log analyzer to identify transactions that did not make it across and manually apply them. However, your availability or recovery plan should not depend on these log analyzers. The transaction log files backed up by the backup job should be archived to provide point-in-time recovery of the primary server if, for example, a user error modifies some data that needs to be recovered.

Moreover, archiving the transaction logs along with a full database backup offers another disaster recovery option when needed. To control when the transaction log files are deleted so that the OS backup program can back up these files on its own schedule, set the Delete Files Older Than option to a time period great than that of the OS backup program schedule. You can find this option in the Transaction Log Backup Settings. For example, if the OS backup is scheduled to run every night, set the Delete Files Older Than option to at least keep the files there until the OS backup completes.

The challenges with this scenario are that the network bandwidth must have the capacity to support log shipping large log files to the remote location. Moreover, in the event of a disaster, there is a potential that some of the files may be in transit and may not make it to the secondary server. Even if the bandwidth supports log shipping comfortably, during a disaster the bandwidth may be constrained by other activity that can slow down the file transfers. That means the possibility

of data loss. The databases are kept in sync using the transaction logs. The amount of data that might be at risk during a disaster is the data included in the transaction log. For mission-critical applications for which you want to minimize any data loss, you may need to choose another solution. For instance, database mirroring can send transactions to be synchronized as soon as the transaction commits, without waiting for a transaction log backup. Using log shipping, you may need to accept a greater amount of data loss because of a missing log file, or an inaccessible backup log folder.

Leave a Reply