2

I have two instances of SQL Server 2005 STANDARD edition, Server A in New York and Server B in California.

Server A has a large database; for DR/BCP purposes, I would like to use Log Shipping to keep a warm backup on Server B.

Unfortunately, the transaction logs are too large (over 10GB across a WAN =( ). Before I invest in a third party product that deals with this issue, could I do something like this:

1) Disable the copy job on Server B. Backups are still created on Server A, but are not automatically moved to Server B.

2) Create a scheduled task to compress all backed up logs on Server A (using a tool like 7zip or WinZip), and dump them on Server B where the restore job expects them (and unzipping, of course).

3) Allow the restore job on Server B to work as normal.

Is this a naive or workable solution?

If we had enterprise edition, I would use asynchronous database mirroring; or if we had 2008 Enterprise edition I would look into the built in compression.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
Hythloth
  • 235
  • 2
  • 7

2 Answers2

1

Don't create another scheduled task as the timing can get messed up. Modify the existing sql server generated log shipping jobs for backing up the log & copying the log.

There's a blog post here with a solution that you may want to borrow some or all parts from.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
0

I believe this would work fine. Keep in mind that unless you are using 2008 you'd have to look into some kind of 3rd party tool for the compression anyways.

The only real downside I see is it's a bit more work for you to maintain as you'll be relying on more than just SQL server to do the work.

Sean Howat
  • 1,849
  • 4
  • 20
  • 33