LogShipping frequency for backup/copy/restore

Question

I wish to create a LogShipping job that runs every 5 minutes. What is the best way to do this so that the backup/copy/restore does not collide?

Maybe Sql server knows how to handle this, but I was concerned if I put 5 mins into all of the scheduled times, that I would get some sort of conflict.

i.e. would this create a problem?

• Primary server: starts backup at 9am
• Secondary server: starts copy at 9am
• Secondary server: starts restore at 9am

Would staggering them be a better idea?

• Primary server: starts backup at 9:00am
• Secondary server: starts copy at 9:01am
• Secondary server: starts restore at 9:02am

The database is only approx 200mb in backup size and the two sql servers are running on the same local network.

Answer 1

At work, we do log shipping with a 100 GB database.
We do everything (backup, ship, restore) only every 15 minutes (not 5), and we do it like this:

• Primary server: starts backup at 9am
• Secondary server: starts copy at 9am
• Secondary server: starts restore at 9am

We never experienced any concurrency problems doing this, and we're doing this for about 5 years now. SQL Server is able to handle this alone, it just avoids collision by "delaying" until the next schedule.

For example, if you just leave everything scheduled at 9 am, SQL Server will do this:

• 9 am:
  • primary server starts backup A
• 9.05 am:
  • primary server starts backup B
  • secondary server copies backup A
• 9.10 am:
  • primary server starts backup C
  • secondary server copies backup B
  • secondary server restores backup A

And so on...

Answer 2

This doesn't really solve the above problem, but I ended up using Transaction Replication and I didn't need to worry about the above complicated timing issues.