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?

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?

The following question pertains to Microsoft SQL Server transactional log shipping (TLS). We're on SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.

##Setup## I have a primary data center (A) and a secondary, disaster recovery data center (B). Let's say I have N=64 databases that need to be log shipped of varying (let's assume random) 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.

##Need to Optimize Bandwidth## 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.

##The Idea## 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.

The question, kind readers, is what value to use for the start time for each job?

I suppose I need to know how big the log backup file might be to optimally space the jobs. Can you predict how large a transaction log backup will be, before you've run the backup? Alternatively, there's an option of looking at the past days' log backups on the local file system to determine the relative weights for each database. But that's not really going to work for a brand new database that has little or no tlog 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?

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?