Log Shipping : Multiple Databases, Same Schedule
csm
Posts: 17
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
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
Comments
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8