Couple questions about protecting SQL server

[james411]

Hi, I'm trying to see if my current Veeam 9 deployment can be leveraged to better protect our SQL Server. We have 2 standalone ESXi 5.5 hosts each with local disks (so no shared storage). I currently have Veeam replicating our SQL 2014 VM from host 1 to host 2 every night and once during the day at lunch time. I tried increasing replication to every 5 minutes during business hours, but this proved to be counterproductive as users would get runtime errors in the application that uses the SQL server. So, I was looking at another option to decrease RPO should host 1 fail during the day.

I was thinking about using Veeam to backup the transaction logs frequently as I understand transaction log backups don't stress the production database. So a couple of questions:

1) How often can I backup the logs with Veeam? Can I go as low as 1 minute?

2) Let's say host 1 fails during the day. Since I already have the SQL VM replicated to host 2, can I restore the log files to the replicated VM on host 2 to get up and running to the point of my last log backup before the crash?

Thanks for your input.

[Mike Resseler]

Hi James,

1) 15 minutes is the default, 5 minutes is the lowest you can go

2) Yes! What you are going to do at that moment in time is start the Veeam Explorer for SQL from your backup. Choose your point in time (which probably will be the last one ) and restore the DB as such to that started replica.

Actually, having a backup now (with transaction logs getting backed up) and a replica of that machine gives you a nice additional layer of security. But be aware that when you boot the replica (or use instant recovery for the backup depending on the situation) you will still need to recover the DB itself so it will take some time!. RPO will be 5 minutes, but RTO can take a bit longer and you might want to test this before promising your peers something

Hope it makes sense

Mike

[skrause]

What edition of SQL Server are you using?

[james411]

Currently using 2014 Standard. We may upgrade to 2016 at some point, but no final decision on that yet.

Thanks for the answers Mike. I'm going to have to think about this a little more. I was hoping to get down to 1 minute RPO if at all possible.

[skrause]

If you want a ~1minute RPO, using SQL Server Enterprise and setting up an Always On cluster with an asynchronous replica in your secondary location would work. It wouldn't require you to do any restore operations to fail over either. You could still use Veeam to backup the primary server (I believe they have better support for Always On now as well).

But, of course, you would have to upgrade your license to Enterprise which might be prohibitively expensive. If you use an asynchronous replica in Always On the replica's licensing is covered by the licensing for the main instance so it would just be the edition upgrade price.

[james411]

1) 15 minutes is the default, 5 minutes is the lowest you can go

Mike, does going as low as 5 minutes render any performance problems with the production database? My understanding of the way Veeam performs the log backups is that every time the log job runs, Veeam installs the backup components on the target VM and then removes them once finished. Just wondering if very frequent installing/uninstalling becomes resource intensive.

[foggy]

My understanding of the way Veeam performs the log backups is that every time the log job runs, Veeam installs the backup components on the target VM and then removes them once finished.

Components are deployed once, upon start of the logs collection job, collect logs with the specified interval and are removed once the next parent backup job run is performed.

[james411]

Is there any technical limitation as to why you can't go below 5 minutes for log backups?

[Mike Resseler]

Hi James,

I am not sure if there is a technical limitation (I would need to ask that) but even if you would not be using Veeam there are a few things you need to keep in mind:

1) We are talking about closed logs. Only databases that continuously receive thousands and thousands of changes per minute have closed logs faster then 5 minutes. (You can verify that and see for yourself the timestamps per closed log for your databases.)
2) We are and remain backup. Many of our competitors (not that I want to say we are better or so, I prefer not to talk about the competition but talk from our own strength) have the same "limitation" or in most cases it is even 15 minutes. If you want an RPO of less than 5 minutes, you need to consider other defense mechanisms such as @skrause has proposed (there are others also). Don't forget protection of data is a multi-layered approach
3) Even with SQL log shipping, the advised method is 15 to 30 minutes. They do have a theoretical 1 minute possibility but most will tell you that it is not practical and steal to much resources.

Personally, if you are that concerned about the RPO (and I assume that is a business requirement) I would consider investing into 2 things: Have a decent solution with Veeam and choose a good timing (it does not have to be 5 minutes) in combination with SQL always ON.

But that are my 2 cents

Cheers
Mike

[james411]

Thanks Mike for the further input!

1) Interesting thought on "closed" logs. I had not even considered that. I'm not sure I fully understand though. When a log backup kicks off, does the current log not get closed and a new one started so you would get whatever changes happened between the last log backups regardless if the log was not yet closed?

2) Yes, I agree I am definitely looking at mult-layer protection. I think Veeam is great, I just thought that maybe there was a way to use it to get a super low RPO for SQL server that would save me from having to invest in other licensing (not to mention setup, administration, etc, etc). That would be really super!

3) I'm not sure about the resource overhead of log shipping so often. I've known several others who have their mission critical DBs log shipping that frequently and I've seen it recommended before: https://www.brentozar.com/archive/2014/ ... es-really/

Thanks for your input again!

[Mike Resseler]

So first, I am not disagreeing with Brent at all, in fact, we have worked with him already a few times at Veeam. He is a great resource for SQL.

Still, while I understand what he says that the "cost" of doing a backup of every minute versus 1 hour can be "cheaper" it does not take away the fact that we are talking about a backup solution. I still believe that it would be another solution that needs to give you that very low RPO (remember, imagine that you take 5-minute transaction log backups, but only 1 day image backups... Doing a restore of that image + 23 hours+ of transaction log backups is not going to be fast) but not a great RTO. So having something like always-on will cover that RTO for you

Makes sense?

[james411]

Yup, thanks again.

Edit: I still think it would be a nice feature to be able to do log backups more frequently than 5 minutes