Log shipping - file transfer

Question

Log shipping - file transfer

Krissy

Ten Centuries

Points: 1185
More actions
February 8, 2006 at 4:02 pm

#112702

Hi

I am a bit clueless when it comes to things like this, so bear with me

I want to implement logshipping (which I can do, so don't need help with that!) where the secondary server will be at a DR site.

The connection is a 4MB Frame Relay between the two sites.

If I were to logship every 15 minutes, and had approx 1.5GB of logs to restore on the other server, any idea if this would be possible in the 15 minute timeframe?

Thanks for your help!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 1

Hi Krissy,

Here's my wild stab at it. I left in my notes as I walked through it. Please let me know if I'm glaringly off: CONSTRUCTIVE criticism is always best 🙂

Network bandwidth is usually measured in bits (e.g., Mb or Gb) so, let's convert our Gigabyte (GB) data units accordingly (all the way to Mega so we can compare against our available bandwidth, also in Mega-units)...

1.5GB * 8 = 12Gb = 12000Mb (amount of data we need to transfer in 15 min.)

12000Mb / (15 * 60) = 13.3 Mb/S (divide our file size by (15 minutes * 60 to get seconds) to get our bandwidth requirement for a 15 minute transfer)

13.3 * 1.2 = 15.96 (13.3Mb/S is our raw bandwidth requirement. Let's assume 80% bandwidth utilization since you rarely get 100% [other traffic, packet collisions, etc])

you would need 16Mb/S bandwidth to transfer 1.5GB within a 15 minute window.

If your connection is really 4MB/S (32Mb) then you should have no problem.

However, if it's 4Mb/S (I only throw this out as a possibility because MB is used so often in place of Mb) then you won't make it without compressing your backup files (I typically get about 75% compression of sql backup files using gzip. This could get you very close to a 4Mb/S bandwidth requirement for just the data transfer. But, you'd still miss your window due to the amount of time it takes to compress the file. If you use a backup compression tool (RedGate Backup comes right to mind; there are others), you may gain enough time in compression to still have a chance of making your transfer window.

Krissy Ten Centuries Points: 1185 More actions · Answer 2

Thanks for your reply Ken.

Yes I think I did mean 4Mb... oops

However, your reply does explain to me how to work it all out, so I will have a look at your calculations and apply it to that.

Thanks so much for your help!

I can do DBA stuff, but when it comes to network and hardware, I am clueless

Krissy Ten Centuries Points: 1185 More actions · Answer 3

OK I think I meant to have 4Mb and 1.5Gb..

So would these calculations be more accurate

1.56Gb = 1536Mb

1536/(15*60) = 1.7Mb/S

1.7 * 1.2 = 2.04

So theoretically I would need at least a 2Mb link to handle transfering this amount of data in 15 minutes?

I assume that the /S part means "x Megabytes per second"?

Ken Johnson-162971 Ten Centuries Points: 1296 More actions · Answer 4

Yes, the /S is per second.

Your calculations look good but... I'd guess that your file is probably sized using GB. Just to confuse people, storage is typically measured in Byte units while networking it meaured in Bit units. You can right click and view properties on your file to make sure. If you're off, it's going to make a big difference!

Just so you don't have to worry about mistakes determining what would work "theoretically," you could always copy your tran log from production to DR and time it (assuming you have your file and link available for testing)

Krissy Ten Centuries Points: 1185 More actions · Answer 5

Ahh ok... Geez all these confusing bits and bytes

Well I will double check it all but at least now I have the formula to work it out.

I can test a few databases; but some of them are too big to put onto a test machine (70GB - or Gb ...

) but I can get sort of an idea.

Thanks again!

Jen-169507 Right there with Babe Points: 776 More actions · Answer 6

Hi Ken,

I'm interested on the formula you used (wanted to learn how)

why did you use the factor 8 to get total GB and 1.2 to get raw bandwidth rate?

thanks in advance

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 7

Hi Krissy,

depending on the way you implement the logshipping you might want to time the restore into the db too. If copying and restoring is done from server1 you have to add the transfer time and the restore time...

karl

Best regards
karl

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 8

Are you saying that your transaction log is currently 1.5GB, or that 1.5GB of log is produced every 15 minutes? If it's the former, once the initial transfer has been done you shouldn't have a problem as the incremental files wil be a lot smaller.

--
Scott

Krissy Ten Centuries Points: 1185 More actions · Answer 9

I know I need to take this into account, but if it was going to take longer than 15 minutes just to copy the logs over then I obviously won't bother with that frequency. Just wasn't sure how to work it out!

Krissy Ten Centuries Points: 1185 More actions · Answer 10

Well it is several databases, I currently backup the logs which is about 1.5Gb in total - but that's every hour.

You could be correct though, the size of logs will decrease if I am backing up more frequently.

Something else for me to consider and test out