Issues with increasing the frequency of MS SQL log backups on a data source

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Hi

Has anyone had any issues with increasing the frequency of log backups on a data source?  We are looking at changing our log backups on our live DB from 15 mins to 1 min.

I was tasked to investigate how this would affect delphix and tested the following:
15 min log backups
10 min log backups
5 min log backups
2 min log backups
1 min log backups

I found that when I got down to 1 min log backups, the delphix staging dSource really struggled to keep up and ended up about 20 mins behind.



As you can see here from the image which shows difference in seconds between the snapshot and the log backup...normally the lag was around 60-120 seconds which is expected as Delphix only polls for the log backup every min.  Once I went down to 1 min log backups, delphix started falling behind.

If anyone has discovered the same, or not had the same issue, or has any thoughts, please let me know
Photo of Mark Hayter

Mark Hayter

  • 394 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Venkat Navaneeth

Venkat Navaneeth, Employee

  • 88 Points 75 badge 2x thumb
Hi Mark,

I’m curious why you want to implement one minute log backup/restore process. Is it for DR?

Your log backup frequency shouldn’t impact Delphix performance. Log restore from production to staging was handled entirely at SQL Server level, except for reading backup metadata from MSDB. How fast staging can keep up with production log backups depends on multiple factors...

1. What's the read throughput you are getting from your backup share?

            (If you are writing your log backups directly to backup appliances then you are bound to see issues. Backup appliances are tuned for write performance. Read throughput suffers especially when reading multiple files.)

2. Log backups frequently run for more than a minute. In that case, Delphix won’t find the backup entry in MSDB immediately and has to process multiple backups on next iteration. This can add up to restore lags pretty fast.

3. Do you use dedicated staging server? If not, how's the resource utilization on the server looks like?

4. What's the size of your log backup files at different frequency intervals?

Try to quantify the backup share throughput you are getting at different frequencies. Let me know what you find.

Regards,

Venkat

Photo of Mark Hayter

Mark Hayter

  • 394 Points 250 badge 2x thumb
Thanks for you quick response Venkat.

Yes one min log backups are for disaster recovery purposes.

I will check your 4 questions and get back to you...we are currently testing with our pre production server so I know the following:

1. Need to check this...
2. The backups mostly take less than a few seconds.
3. Our staging server is a VM and resource utilization looks fine from what I've seen.
4. Log backups are small..normally around 10-100kb and occasionally (every 20 mins) around 2.5mb