Author: Sanjay Mishra

Contributors: Mark Scurrell, Richard Waymire

Technical Reviewers: Denny Lee, Prem Mehra, Richard Tkachuk, Burzin Patel, Kun Cheng, Peter Carlin, Juergen Thomas, Michael Thomassy, Thomas Kejser, Lubor Kollar, Alexei Khalyako, Peter Scharlock, Nicholas Dritsas, Tom Michaels, Mike Ruthruff, Mike Weiner

Overview

Log shipping allows you to automatically take transaction log backups on a primary server, send them to one or more secondary servers, and then restore the transaction log backups on each secondary server. Many Microsoft SQL Server customers have asked for the ability to schedule the log shipping jobs with less than 1 minute frequency. In SQL Server 2005, SQL Server Management Studio user interface allowed the frequency of the scheduled jobs to be 1 minute or more, which meant that the minimum latency of log shipping was as long as 3 minutes (1 minute each for the backup, copy, and restore jobs). Many customers have asked for this latency to be less than 1 minute.

In this paper we introduce the new sub-minute log shipping capability in SQL Server 2008, and we discuss some considerations you need to be aware of in scheduling frequent log shipping jobs.

Introducing Sub-Minute Log Shipping in SQL Server 2008

SQL Server 2008 enables log shipping jobs to be scheduled with frequency in seconds. In SQL Server 2008, SQL Server Management Studio and the stored procedures sp_add_jobschedule and sp_add_schedule allow frequency settings in seconds, minutes, and hours. The minimum frequency is 10 seconds.

Figure 1: SQL Server 2008 Log Shipping user interface enables scheduling the jobs in hour, minute, or second frequency

Considerations

There are some considerations you should be aware of when you set up too frequent log shipping jobs:

• The next execution of the job will not start until the previous execution has completed. Let’s assume you have set the frequency interval of the log backup job to 10 seconds, but one execution of the log backup takes 12 seconds to complete. The next backup job will start at the next scheduled time, which is 20 seconds after the start of the previous backup job. One execution of the job is skipped in this case.
• Every time a log backup is completed, a message similar to the following is shown in the SQL Server ERRORLOG:

2009-02-09 15:25:56.94 Backup Log was backed up. Database: Test_LS, creation date(time): 2009/02/09(14:27:24), first LSN: 19:145:1, last LSN: 19:145:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\PRIMARY_DL380\LSBackup\Test_LS_20090209232551.trn'}). This is an informational message only. No user action is required.

If you take a log backup every 10 or 15 seconds, the SQL Server ERRORLOG flooded with such messages.

If you don’t want these messages flooding the SQL Server ERRORLOG, you can enable trace flag 3226. This trace flag doesn’t alter the behavior of backup jobs; it just suppresses the backup completion messages, preventing them from getting into the SQL Server ERRORLOG. Note that this trace flag suppresses all backup messages – database backup as well as transaction log backup.

• Information about each backup is also recorded in the msdb database (the msdb.dbo.backupset, msdb.dbo.backupmediaset, msdb.dbo.backupmediafamily, msdb.dbo.backupfile, and msdb.dbo.backupfilegroup system tables). If you back up too frequently, you can expect these tables to grow faster than usual. You should periodically check the size of these tables and delete or archive the old information as necessary. To delete the old backup history, use the stored procedure sp_delete_backuphistory.
• The backup compression feature in SQL Server 2008 provides significant space and time savings. Backup compression results in smaller backups, and it helps improve the performance of all the operations performed by log shipping by providing the following:
  • Faster backup of the transaction log on the primary server.
  • Faster copying of the transaction log backup file to the secondary over network.
  • Faster restore of the log backup on the secondary.

However, the benefits of backup compression come with the cost of higher CPU utilization. If your log backup jobs use compression and are scheduled too frequently, you may notice frequent spikes in CPU utilization on the primary. Restoring from a compressed backup uses more CPU, and you could see frequent spikes in CPU utilization on the secondary as well. For more information about backup compression, see Tuning the Performance of Backup Compression in SQL Server 2008 and Tuning Backup Compression Part 2.

Conclusion

SQL Server 2008 provides the ability to schedule log shipping jobs as frequently as 10 seconds, which results in reduced latency of log shipping. Reduced log shipping latency can result in reduced data loss in case of loss of primary.