Log Shipping : Multiple Databases, Same Schedule

csm

Hello!

As I mentioned in another post, we're rolling out Log Shipping between two servers; one at each of our two locations - connected via a 1.5mb private line.

My question regards scheduling simultaneous jobs for the 3 databases.

We've scheduled the log shipping to occur every 15 minutes (restore on a 5 minute delay)... this results int he SQL Backup 5 timeline to show each job as colliding.

Currently we have 3 jobs on the source server, and 3 jobs on the destination server. Each set have the same schedule - causing them to appear as conflicts in the timeline.

What is the recommended way to handle this?

Some thoughts included consolidating the 3 jobs into a single job, with 3 steps, or offsetting each of the 3 jobs by a minute or two.

Curious what others recommend.

Thank you!
Colby

Jason Cook

There are pros and cons to each approach, so it depends what you want to get out of it. I'll post a few thoughts to get the ball rolling, and hopefully others will add their own opinions.

If you keep them as three seperate backups and restores and offset by a few minutes:
+ Will be supported by tools such as SQL Backup, because you are using the standard log shipping configuration.
+ Very easy to follow if the delays are much more than the expected duration - will work well unless the expected duration is much higher than anticipated (where collisions will appear again)
- Requires more attention if backups and restores vary wildly in expected duration, since ultimately will result in the same collision problem.

If you combine them all into one job with 3 steps:
+ All the steps will work one after the other, so the only collision that can occur is if the whole combined job exceeds 15 minutes.
- Any changes to the jobs will need to be done via Management Studio, as it is unlikely to be supported in the SQL Backup user interface.
- Care needs to be taken when setting up the job - if the first step fails, you probably want the second step to continue - which is usually not the default.

Hope those thoughts help,
Jason

petey

A 'conflict' does not mean that one or more backup/restore tasks will fail.

If your server can spare the CPU cycles and support the additional disk throughput that the 3 simulataneous processes will require, while maintaining an acceptable level of service for other 'normal' database activities, then you shouldn't need to reschedule your tasks.