Designing a Data Recovery Strategy

Recoverability is the ability to restore data after a data loss incident such as a server failure. Designing a recoverable database server requires adequate planning, preparation and server data recovery. This article looks at some of the considerations that go into building an effective recovery strategy for a database servis server.

The first step of creating a disaster recovery plan is to assess your data recovery needs. This lesson walks you through a series of questions that you can use to begin designing your data recovery solution.

To design a backup and restore strategy, you need to begin by taking inventory of the data in your organization. The recovery requirements of a database depend on the nature of its data: its value, its volatility, and other factors. To assess the data and determine your recoverability strategy, you can begin by asking yourself the following questions:

  • Which database type is it?

Different database types have different recovery needs. Some databases, such as the tempdb database, do not need to be backed up at all. You might also determine that other databases, such as those used merely for testing, do not need to be backed up.

However, other databases must be backed up at varying levels of frequency. For example, SQL Server relies so significantly on the master database for operations that if you lose your master database, you lose the entire SQL Server installation.

For this reason, the master database needs to be 100 percent recoverable. Despite this need for high recoverability, however, changes occur relatively infrequently to the master database, and you do not need to back it up as frequently as you do most production databases.

In fact, you need to perform backups of the master database only when you add a new database, change configuration values for a database or the associated server instance, or configure SQL Server logins.

You should back up production databases, meanwhile, with a regularity corresponding to the frequency with which they are updated—as rarely as once a year to as often as every five minutes.

  • How volatile is the data?

Data is said to be volatile when it changes frequently. As a general rule, the more volatile the data, the more regularly you should back it up. For example, read-only databases do not normally change and therefore do not need to be backed up regularly.

A database whose data changes once a day, however, should be backed up daily. And a database whose data is continually changing needs to backed up repeatedly throughout the day.

  • How much of the data can the organization afford to lose?

Some data, such as that used only in testing and development, is naturally less valuable to an organization than other data, such as that used in production. A database administrator must stay informed about the shifting importance of an organization’s database applications to ultimately determine the relative value of database data.

For the purpose of designing a backup strategy, the amount of data that the organization can afford to lose is often best measured in time, not in bytes. In other words, when you determine how much data within a given database your organization can afford to lose, it is often useful to specify the maximum acceptable span, such as an hour or day, during which time updates made to that database can be lost.

For example, if a database is damaged at 11:30, determine whether the recovery should enable you to restore the database to the state the database was in at 11:00 the same day, midnight the night before, or some earlier point.

The trade-off for ensuring a brief window of data loss is the corresponding need to perform more frequent backups, which use more resources. Another trade-off is that backup strategies that require more frequent backups require a longer process of disaster recovery.

In general, the more mission-critical or volatile a given set of data, the shorter the period of acceptable data loss. And the less mission-critical or volatile a given set of data, the longer the period of acceptable data loss. For more information about data recovery or mac data recovery, please visit Data Recovery Group.