How to Align Your SQL Server to Your RPO and RTO Goals

Many businesses depend on data systems. Data systems help doctors schedule appointments with patients. They allow construction companies to manage projects and calculate costs. The list goes on and on. And if a key data system isn’t available, it hurts. It’s disruptive. And users, customers, patients, and leadership get unhappy. That’s why it’s important to align your SQL Servers with your RPO and RTO goals.

No one like being thrust into firefighting mode unprepared. So, let’s look at 4 steps to supporting your RPO and RTO goals with SQL Server.

1. What are your RPO and RTO goals?
2. How close are you to meeting them today?
3. What adjustments should you make now?
4. What are your longer term plans?

Step 1: What are your RPO and RTO goals?

What are your Recovery Point Objectives (RPOs) and Recovery Time Objectives (RTOs) for each key data application? Not sure? That’s ok. Ask key stakeholders these two questions.

1. If the database goes down unexpectedly, how long can you go without it before it impacts your job or the business too badly? An hour? A few hours? All day?
2. If the database goes down and we have to restore from a backup, how much data can we lose without a lot of consternation/heartburn/headache/problems? Can we lose an hour’s worth of data? May a few hours of data? A whole day?

You can dive deeper into these questions. What about the busiest day of the month? What if it takes all day and we lose 4 hours of data? How will that impact you?

How much will it cost the company if the system is down? In lost revenue. Lost productivity? Lost reputation?

Often IT knows which applications are most important and which can be offline for a while without too much discomfort. But that’s not enough information. We need to know numbers, not relative importance.

Knowing your RPOs and RTOs for each application is critical. Well defined RPO’s and RTO’s will influence your approach to backups, disaster recovery, and even high availability.

Step 2: How close are you to meeting them today?

Now that you know the RPOs and RTOs for each application, it’s time to see how close you are to meeting them. Look at each SQL Server and review the backup process for each database. Some questions to answer:

• What’s the database recovery model?
• How often are you taking full, differential, and log backups? Or are you relying on VM snapshots?
• Do you know how long it would take to restore each database?
• Have you scripted out the restore process already? Or will you have to do that when needed?
• When’s the last time you tested your a restore?
• What if the SQL Server instance goes down? Do you have another SQL Server ready? Or will you need to provision and build one?

Be honest. And don’t sugarcoat it. You need to know where you are so you can begin closing the gap.

Step 3: What adjustments should you make now?

If you’ve identified any gaps, the next step is to determine what you do right now to better align your SQL Servers with your RPOs and RTOs? You may not be able to initially meet them 100%, but you can take small, incremental steps toward the goal.

For example, let’s say your Enterprise Resource Planning (EPR) application is critical. You cannot lose more than 15 minutes worth of data and the system cannot be down more than 30 minutes.

But, in step 2 you learned that there’s a nightly VM snapshot that’s being taken and the database is using the Simple recovery model.

That’s not going to meet your RPO, and possibly not even your RTO. So what can you do?

Start where you can. Change the recovery model to Full. Schedule full and differential backups as appropriate. Then schedule log backups every 15 minutes. Those changes will help with your RPO.

Don’t stop there though. Perform some test restores. Document the procedure. Script the process ahead of time. Test again and refine.

Step 4: What are your longer term plans?

So, we’ve made things better. But we’re still not where we want to be, and where the business needs us to be. So, let’s plan our next steps.

Are there other application databases with similar uptime requirements?

If so, maybe you can move or consolidate those databases to a new SQL Server instance that’s configured as a Failover Cluster Instance (FCI)? That way if the primary node goes down, either for planned maintenance or due to an unplanned disruption, the workload will be automatically moved to the secondary node.

Or maybe you should consider an Availability Group spanning multiple subnets?

Or what about log shipping? It’s an oldie but goodie.

The point is, now that you’ve strengthened your initial position, look for ways to further improve it.

And communicate with your stakeholders. Keep them informed about your abilities to recover.

Want to know more?

If access to data is important to your organization, protect it. Aligning your SQL Server systems with your RPOs and RTOs is a good first step.

If you’d like to know more, here are some additional posts that may prove helpful.

• Where to Start with Disaster Recovery in SQL Server – The SERO Group
• Scary and Dangerous Things in SQL Server – The SERO Group
• High Availability and Disaster Recovery in SQL Server – The SERO Group
• What’s the Difference in SQL Server FCIs and AGs? – The SERO Group
• Why performing database restores before a crisis strikes is a good idea – The SERO Group
• Using VM Snapshots to Backup SQL Server? – The SERO Group
• How to Test SQL Server Backups Using dbatools – The SERO Group

Interested in some help?

Misalignments with RPO and RTO is one of the many areas we review in our SQL Server Health Checks.

If that’s something you’d like to learn more about, let’s have a conversation.