Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Just wondering... Have you considered mirroring as a HADR solution? I've found log shipping to be a bit, well, meh. –  gbn May 25 '12 at 6:51
    
Thanks gbn, I am actually investigating it at the moment because of the issues with the above and also some difficulties around the disconnecting of connections using the secondary server (When I dont disconnect them, it keeps failing the job ). –  duyker May 25 '12 at 11:43

2 Answers 2

up vote 0 down vote accepted

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...

share|improve this answer
    
Thanks Christian, this is great to know even though I wont be using log shipping this time round. –  duyker May 31 '12 at 3:28

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.