Architecting Backup

We've learned a lot about some concepts when it comes to backing up, but how does this actually apply to creating a schedule? Which ones do we choose? How do we architect an actual backup?

The scenario

Let's create a little scenario here. This is actually pulled from my experience in the past when working with a SaaS company. We have a web application that has millions of users that are logging into this web application, and on the back end we have some database servers. What we need to do is come up with a backup solution for these database servers back here, to make sure we're backing it up consistently.

The problem is that we have a lot of data that needs to be backed up, so backup can take a really long time, and we have this recovery point objective of 15 minutes. So we cannot lose any more than 15 minutes, which means that we need to back up every 15 minutes. But I can tell you right now, taking a full backup of this every 15 minutes is just not going to be reasonable.

The considerations

So let's take a look at what some of the considerations are.

Is this data recreatable? No, it's not. People are logging into our systems and we're recording the information, we're recording their progress. So it is very critical for what we have, and we don't care too much about the cost. I mean, we always care about the cost, but we want to make sure we get backup correctly, because it's so critical to what we're doing.

Our recovery point objective is 15 minutes. For this right here we're not going to address the recovery time objective, because we're not dealing with disaster recovery right now — but if we're creating a disaster recovery plan, we would want to be concerned with that.

It would cause major, major disruption if it's down, and if we are taxing it by doing a backup during working hours, that can be problematic. So we can only back up things in the evenings and weekends, at least to any significant point, and we do have some backup windows in the evenings and weekends.

We do want to encrypt this, because we're concerned about security, and the information in this database is pretty dynamic. We've got people that are logging in at all hours of the day, and that information, what they're doing, needs to be recorded.

So what do we need to back up? There are probably a lot of systems and a lot of information that we need to back up with this, but for this exercise we're really focusing on the database and making sure we have a great plan to back up this database.

The frequency: as I mentioned, we need at least every 15 minutes. So this is the frequency at least that we need to have a backup copy of it, because we can't lose any more than 15 minutes worth of information on it.

Building the plan

We're going to need to create a plan for backing up this database server. It's very active and it's going to take a while to do, so we need to figure out what is a good solution for this.

The first thing we should address is we need a full backup. What we're going to do is grab a full backup during the weekend — there's really no other time. So we're going to start maybe around 6:00 p.m. on Friday night, and we already know from experience it's going to take about 48 hours, so it's going to work into Sunday. So now the weekend is spoken for. We need to do this initially.

So what else do we need to do? Next up, we want to consider how we can get differentials in here. We can't really do it during the daytime. If we were to do every 15 minutes, which is what our eventual goal is going to be, those differentials take too long — it's going to take hours to do those differentials. So that's not going to be doable, and it's going to be disruptive to our systems when we do it midday.

Instead, what we're going to have to do is put our differentials at night. So every night we'll do a differential. That would be Monday, Tuesday, Wednesday, and Thursday, and then when it comes to Friday, that's when we start our full.

So now we've got some differentials in there, but we need a target every 15 minutes. In order to do that, we can get some incrementals in there. The incrementals are going to back up these little tiny spaces that are in here. So we'll do incrementals throughout the day every single day, except for the weekend, to make sure that we have a copy of it every 15 minutes, a backup copy of the full database essentially.

What a restore looks like

Hopefully we never have to do a restore of this database, but if we do have to do a restore of the database, what does that look like?

Let's say things go terribly awry on Wednesday evening. Maybe we did a patch and it just was terrible and we need to fail back, and now we need to fail back to about 3:00 p.m. on Wednesday. What does that look like?

First of all, we're going to do a restore of this full database, so we've got the full. Next, we need a differential. Remember, a differential is going to be all the changes since the last full. So a differential is going to be there for Monday, there for Tuesday, there for Wednesday. Really we can't do Wednesday night, because it's past this point in time which we said is what we need, so what we're going to have to do is the differential from Tuesday.

So we do the differential on Tuesday, and then we need to go all of these incrementals that are throughout the day all the way up to 3:00 p.m. So all of those incrementals all the way up to 3:00 p.m., until we get fully restored back to that 3:00 p.m. on a Wednesday. So that's how a restore process is going to look.

How common this plan is, and one caveat

I would say that this is a pretty common backup plan for a lot of the places that I work. They vary a little bit, but most of the time during the day I'm doing some sort of incremental, and then in the evenings I'm doing a differential, or if I can I'll do a full in the evening and try to get a full every single evening. It depends on how much data you're backing up. There are different scenarios here, but essentially what you need to do is architect this according to the scenario.

I will also point out that we can't really use the archive attribute for rolling out this plan either, because if we are taking the incrementals during the day, it's resetting that archive bit and can be problematic with the differential. So we're going to need software that's more complex than working off of just straight the archive attribute that's being set on there.