Recently I was working on a customer environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.

Right at the outset, I would like to point out that the restore of transaction logs are dependent on a number of parameters like disk subsystem performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring. Considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server. In this blog post, I shall outline how you can workaround the same in case you have to meet strict restore time SLAs.

I used a loop to update the rows in a table with a single integer column using a waitfor delay (as shown below) for approximately 20 minutes with log backups happening every 1 minute on a SQL Server 2012 Enterprise Edition instance. There were 100 rows in the table.

SET NOCOUNT ON
WHILE 1=1
BEGIN
UPDATE tbl SET A = A + 1
WAITFOR DELAY '00:00:05'
END

Below is the VLF activity from the loop that I was running against the database tables.

2012-09-11 12:46:01.630  first LSN: 53:198:1, last LSN: 53:201:1
…
…
2012-09-11 13:00:00.920  first LSN: 53:240:1, last LSN: 53:243:1

The above iteration was done with the the secondary database in standby mode. The time taken to restore 20 transaction log backups was an average 760 milliseconds with a maximum of 1193 milliseconds.

The same update activity was done again and the VLF activity for the database for the second iteration is shown below from the log backup messages:

2012-09-11 15:31:00.890 first LSN: 110:114:1, last LSN: 110:117:1
…
…
2012-09-11 15:46:00.720 first LSN: 110:159:1, last LSN: 110:162:1

Before the log backups were restored, the recovery mode was changed to no recovery for the secondary log shipped database.

DECLARE @LS_Add_RetCode2 AS int
EXEC @LS_Add_RetCode2 = master.dbo.sp_change_log_shipping_secondary_database
@secondary_database = N'<secondary database name>'
,@disconnect_users = 0
,@restore_mode = 0
SELECT @LS_Add_RetCode2

The above piece of T-SQL code causes the operating mode of the log shipping change. Post this change 20 log backups were restored. The time taken to restore 20 transaction log backups was an average 315 milliseconds with a maximum of 486 milliseconds. Note that changing of the operating mode takes effect only when the next log backup is restored. Once all the log backups are completed, you will need to change the operating mode back to standby. This can be done using the script below:

EXEC @LS_Add_RetCode2 = master.dbo.sp_change_log_shipping_secondary_database
@secondary_database = N'<secondary database name>'
,@disconnect_users = 1
,@restore_mode = 1

As you can see from a crude and simple test above, that there can be a significant amount of time saved when operating using norecovery as compared to standby mode for log shipping.

In case you have an agreed SLA of more than 2 hours of delay between your primary and secondary databases which contains more than 2 log backups in that time period, then above mentioned tweak will definitely help you. The larger the gap between two successive runs of the log shipping restore job, the greater will be the performance gain from the above trick!

Since it is not supported to directly modify the log shipping jobs, you can create your own job which executes the necessary T-SQL commands and invokes the necessary log shipping jobs in the correct order.

The correct flow of events would be:

1. Change log shipping restore mode to norecovery using the stored procedure sp_change_log_shipping_secondary_database
2. Start the log shipping restore job
3. Change the log shipping restore mode to standby using the stored procedure sp_change_log_shipping_secondary_database
4. Initiate the log backup job on the primary server
5. Initiate the log copy job
6. Initiate the restore job in the same order to ensure that the new restore mode is affected as an operating mode changes only after a new transaction log backup is restored.

As always, test the above to ensure that all your SLAs and objectives are met before implementing the same in a production environment.