Designing a Successful Database Backup Strategy

Maintaining the security and accessibility of an organization’s data across their entire ecosystem is a core responsibility for database administrators whether you’re engaging them through a database managed service offering or a database consulting service offering. It’s a multi-faceted, all-encompassing obligation with many things to consider – from planning to architecture to implementation. Therefore, it’s imperative that a successful database backup strategy is put in place.

Backups and their related strategies are a key component in a modern data estate. Planned and executed correctly, they can save organizations from themselves, and in more way than one. But just as database strategies and scalability plans are numerous and vast, so to are your options when planning how you map out your backup and disaster recovery strategy.

It’s important to begin planning your backup strategies when you architect your systems, so you can relate them directly to your organization’s specific requirements. In the early phases of planning your strategy, DBAs need to balance business applications and data retention requirements at every level. This provides a solid plan before you start creating and scheduling of thousands of SQL Server jobs.

Backup Models

Start by making sure that you understand the differences between each of the backup models available to you, as this will directly inform how you choose to design your backup strategy. Through properly incorporating each of these models successfully, you will be able to minimize data loss and prevent unnecessary overlap between your performed backups.

These backup models include:

Full – Backs up the entire database, including part of the transaction log for recovery. Represents a consistent image of the database at the time when the backup is finished. Pros: Simple, easy. Cons: Large in size, time-consuming, and require scheduling.

Partial — Backup primary and all read/write filegroups with a part of the transaction log for recovery. Pros: Small backup size. Cons: Read-only filegroups not backed up.

File/Filegroup — Backs up specified files(s) or filegroup(s) with a part of the transaction log for file recovery. Pros: Flexibility in scheduling. Cons: Complex, files must be online.

Differential — Works in conjunction with full backups to identify changes that have been made since the last Full Backup and stores modified data.

Copy only — Full database backup, independent of the backup sequence. Great for one-off backups intended for restoring to non-production.

Transaction log — Backs up a portion of the transaction log for recovery, then frees up this space in the transaction log for other use.

While the varieties across these models may seem daunting, they each play a crucial role in effectively protecting the data within your ecosystem.

Requirements Gathering

Now that we are familiar with the tools we have available to us, we must now understand the data’s requirements, how it’s accessed, and where it’s stored. Once you map each database and instance location, then match its applications and the access points for each, it’s important to understand that you have more business requirements for securing your data.

Many organizations must meet certain legal hold requirements when storing their data. This added layer of complexity means relevant backups must be made immutable (stored in an inaccessible location to prevent database manipulation) in case of litigation. Incorporating these requirements incorrectly could result in major legal implications for your organization. Before designing a backup strategy, work with your business and compliance units to better understand your HA/DR and security compliance responsibilities. Document these requirements in the information gathering process and ensure your database team keeps the business accountable for adhering to them.

It’s also important to understand the unique requirements for applications and servers in terms of set maintenance windows, recovery models, legal hold parameters, and service level agreements. Theoretically, you could create different backup jobs catering specifically to each of your databases’ retention guidelines or backup regulations, but that is a time-consuming, inefficient process that could require thousands of backup jobs.

A better approach involves taking a step back and looking at your data environment through Tiers of Service.

Tiers of Service

By dividing your ecosystem into Tiers of Service, you can prioritize applications for your backup strategy based on their required availability and quality of service. You can also set recovery points to determine the cadence and priority of your backups.

Tier 1 contains the most essential applications and related data infrastructure and is typically offered by many database managed service providers. Here you will prioritize mission-critical applications that require a near-zero data loss, virtually 24/7 availability, and the fastest recovery timelines.

Tier 2 is for applications that are still involved in production but have more leniency in their recovery timelines and availability. This is something that is offered with Fortified Data’s Code Red managed services offering.

Tier 3 therefore consists of non-production applications that only require 24-hour recovery point windows and more flexible recovery timelines.

Assigning Tiers of Service ensures the most important aspects of a database remain protected, available, and scalable when it’s not possible to offer a full backup for every application.

Using a tiers of service approach for your backup and recovery models will foster an increased sense of flexibility in your database management. Not only this, but it also provides greater ease of use for members of your organization, and creates a prioritized framework in case of disaster recovery being required.
Ben DeBow – Founder and CEO Fortified Data

Conclusion

When planning your backup strategy, give yourself ample time to first understand the current requirements for your data within its ecosystem, then prioritize these needs into tiers of service. Next, use this information to design a backup strategy that utilizes the different backup models available to you to meet these requirements. By doing this, you’ll not only gain a more holistic understanding of your environment, but you’ll also increase the chances of your strategy’s overall success in your organization.

For more information on backup strategies and how I would recommend incorporating them into your data ecosystem, stay tuned for “Implementing your Backup Strategy“.

Additionally, Fortified Data is always available to help with troubleshooting errors and implementing database strategies into your business processes.