Sql-server – SQL Server backups strategy with limited storage available

sql server

I've a SQL 2012 Server setup on a VM, with 3 disks:

  1. Disk 1: 150GB –> System
  2. Disk 2: 750GB –> SQL mdf
  3. Disk 3: 750GB –> SQL ldf

DBs are in full recovery mode.
Let's say we'll have 350GB of data in the DBs.
This is a SharePoint 2013 databases server, but I don't think this impacts the backup strategy to adopt here.
The VM itself (and all its disks) is replicated via Hyper-V replicas.

Is the following backup strategy acceptable?

  1. I plan full data backups every week to Disk 3. As far as I understand, this flushes out all transaction logs. I then remove backups from previous week.
  2. I plan logs backups every 1 hour to disk 2. I remove log backups older than 1 week.

With this strategy, I should optimize my disk space, and at worse, I lose 1 hour data? Am I correct?
Will the logs backups (every 1 hour) have a noticeable impact on my server performances when they occur?

Thanks,

Best Answer

Ideally you'd add a 4th volume for backups, but I understand that's likely not an option. (FWIW, Sql Server does support making backups to network shares).

With that out of the picture, it sounds to me like you're trying to keep it so that losing volume 2 on any given day means you can recover the full backup on volume 3 and still have the ldf file valid for that full day to get you back to current, or that losing volume 3 would mean you can still replay the transaction log backups on volume 2 from that day to get back up to the last hour. However, I don't think this will work how you want. I think you'll end up with much larger ldf and transaction log files than you need, and Sql Server will want to keep a lot more of that ldf information in memory, hurting performance.

I think you will be better served by taking nightly full backups, and doing transaction log backups as often as performance and disk space allow (which could be fairly often... but you'll need to test to know). This should result in better overall performance and reliability for your system. I do think it is okay to separate which volumes you're using for each role, as you suggested.

The one other thing I would add is to look at where you're keeping your tempdb. Moving that around can have a big impact on things as well. You might find (just as one example) that keeping your database's mdf and ldf together on one volume with tempdb and backups on the other gives better results.

Related Question