How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

Question

The following question pertains to Microsoft SQL Server transactional log shipping (TLS).

We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.

Background

I have a primary data center (A) and a secondary, disaster recovery data center (B). Let's say I have 64 databases that need to be log shipped of varying sizes, but no one database exceeds 50GB.

I'm currently using the default SQL Agent Jobs that SQL Server automatically creates, so I have 64 SQL Agent Jobs.

I want to log ship every database every 15 minutes. Assume that's possible to do if I just transferred all the files back-to-back.

Issue

Let's also say that the path from A to B travels over the public internet, and so I am paying for the bandwidth to ship these logs. I'm metered at the 95th percentile. I want to smooth out or optimize bandwidth so as to minimize the chance of paying overages. I'm using backup compression.

Possible Resolution

My current idea is to write a script that will customize the start time of each backup job automatically. This script could be run as frequently as desired to maintain optimal transfers. Having it run regularly will enable new log shipping backup jobs for newly added databases to be optimized without human intervention.

The current SQL Agent Jobs all start with the string "LSBackup" such that I can list them all using:

SELECT * FROM msdb..sysschedules WHERE name LIKE 'LSBackup%'

I can fetch the list of all the SQL Agent Jobs and store them in a table variable, then iterate in a WHILE loop calling EXEC msdb.dbo.sp_update_schedule to update the @active_start_time to space the jobs appropriately.

What value should I use for the start time for each job?

In order to answer this I need to know how big the log backup file might be in order to optimally space the jobs. Can you predict how large a transaction log backup will be before you run the backup? Alternatively, I could look at the past days' log backups on the local file system to determine the relative weights for each database. However, that's not really going to work for a brand new database that has little to no Transaction Log backup history.

Assuming it is possible to determine how relatively large each backup will be, what's an optimal algorithm to space them out to achieve minimal bandwidth impact when being metered at the 95th percentile?

Answer 1

DBCC SQLPERF (LOGSPACE) will return the size of the log file in MB & the current percentage used. From that you can calculate the current utilised portion of the log file in MB.

That by itself will not be sufficient to calculate log backup size, not least because you are using backup compression. However collecting this data over time & correlating it with the size of the backups might reveal a linear relationship.

You could insert a step into the Log Shipping backup job to record the size of the utilized portion of the log file and a another step after the backup to record the new size of the log file and the size of the backup.

Running this for a period and analyzing the results could allow you to estimate the size of the backup file.

Not the most straight forward method and to be honest it might not even work. If you are interested in giving it a go I would be interested in helping.

Answer 2

I ultimately wrote a stored procedure which I run nightly via SQL Agent.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'dba_StaggerLogShippingJobs' AND type = 'P') DROP PROCEDURE dba_StaggerLogShippingJobs
GO

CREATE PROCEDURE dbo.dba_StaggerLogShippingJobs
AS
-- This job is intended to be run nightly.
-- It queries the msdb..sysschedules table for jobs that start with 'LSBackupSchedule%'
-- It determines how to space the jobs evenly within a 15 minute window, then calls msdb..sp_update_schedule to set the new @active_start_time. 

SET NOCOUNT ON

declare @logShipEvery int = 900 -- Log ship every 900 seconds or 15 minutes
declare @staggerSeconds int -- number of seconds between jobs
declare @new_active_start_time int -- calculated new start time for a given job
declare @current_active_start_time int -- existing start time for a given job.

-- Some simple variables for use in the loop.
declare @i int = 0
declare @maxId int
declare @schedule_id int

-- table variable in which we store all the current log shipping jobs and their current active_start_time
declare @sqlAgentSchedules table (id int identity (0,1) primary key, schedule_id int not null, current_active_start_time int, new_active_start_time int)

-- Fetch all the LSBackupSchedule jobs into a table variable.
-- Order the query by schedule_id, which monotomically increases as new jobs are added, so that we 
-- can do some tricks to make sure we're not unnecessarily updating schedules nightly
-- when the new value would be equal to the old value.
insert into @sqlAgentSchedules (schedule_id, current_active_start_time)
select schedule_id, active_start_time from msdb..sysschedules 
where name like 'LSBackupSchedule%'
order by schedule_id

select @maxId = (select MAX(id) from @sqlAgentSchedules)

select @staggerSeconds = @logShipEvery / @maxId

-- Calculate a new staggered active_start_time for each job
-- and store it back in our table variable.
-- Formatting the active_start_time is a little tricky because MS is using an integer in a weird
-- way to represent a time value.  For example, the integer value 235959 is actually 23:59:59, or a second before midnight.
-- Took some shortcuts here with the assumption that our time values will always lie between 0 and 15 minutes.
-- In other words, I only bothered to zero-pad the seconds value, and not the minutes value, and I completely
-- ignored the possibility of needing to ever set the hours value.
update @sqlAgentSchedules set
 new_active_start_time = convert(int,convert(varchar(2),@staggerSeconds * id / 60) + right('0'+convert(varchar(2),@staggerSeconds * id % 60),2))

-- Loop over each row of our table variable and update the active_start_time 
-- for any log shipping job that needs to be updated.
while @i <= @maxId
begin
    -- Get the values from this row.
    select 
        @schedule_id = schedule_id, 
        @current_active_start_time = current_active_start_time,
        @new_active_start_time = new_active_start_time
    from @sqlAgentSchedules
    where id = @i

    -- Only update the job schedule if we'd be making a change to its existing active_start_time.
    if @new_active_start_time <> @current_active_start_time
    begin
        exec msdb..sp_update_schedule @schedule_id = @schedule_id, @active_start_time = @new_active_start_time
        print 'Updating schedule for job ' + convert(varchar(8),@schedule_id) + '. Old active_start_time: ' + convert(varchar(8),@current_active_start_time) + '. New active_start_time: ' + convert(varchar(8),@new_active_start_time)
    end

    select @i = @i + 1
end

SET NOCOUNT OFF