2

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.

2
  • Just wondering... Have you considered mirroring as a HADR solution? I've found log shipping to be a bit, well, meh.
    – gbn
    Commented May 25, 2012 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
    Commented May 25, 2012 at 11:43

2 Answers 2

0

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

1
  • Thanks Christian, this is great to know even though I wont be using log shipping this time round.
    – duyker
    Commented May 31, 2012 at 3:28
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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