Manage your database well to avoid the worst

What are the company's data backup processes?

Let's start with a real-life anecdote... When Pixar was almost finished with "Toy Story 2," a co-worker mistakenly ran a root delete command... destroying absolutely every file in the process. He was able to do this because all staff had the same access levels to facilitate collaboration. No problem: there is a backup! Except that no one had realized that it was failing (the files to be backed up were bigger than its capacity!). It was just luck that saved Pixar: a telecommuting employee had a copy on her laptop... This rather simple example highlights the importance of upstream testing, regular checks, role management, etc. All of which we will address here.

You need to check that the backups are functional, so that you can trigger a restore in case of data corruption (whether due to a handling error, a malicious act or a technical problem). This includes checking several points:

• Identify the location of the data
• Validate the existence or not of copies outside the company
• Test the entire backup process

When was the last time you did a "CHECKDB"?

When SQL Server writes data to your disks, it simply assumes that everything is normal, until it needs to re-read the data. Unfortunately, if storage corruption occurs, the server is not always able to issue alerts. Sometimes it can even be the cause of the corruption. This is why it is essential to run the database consistency check periodically, to validate the integrity of the data and index pages. In case of a problem, error messages inform about the location, severity and suggested repair option. Performing a database consistency check allows you to:

• Identify issues early
• Avoid critical problems
• Avoid potential data loss

Do you test the reliability of your backups?

Most people see "successful" backups as a measure of success. However, the absence of errors does not guarantee anything. To really validate the process you need to apply the restore to a random database. Or better yet: reserve a time slot to simulate a major failure. By putting your backup strategy to the test, you can confirm that all scenarios are covered. This way, when an issue arises, you have better control of the situation.

If you are confused by any of the above points, you need to make adjustments as soon as possible.

How much does it cost to lose a day's worth of data?

Expensive, very expensive.

First of all, in terms of image: which customer is going to continue to trust you after you have informed him that you have lost data?

Then internally: such an event can generate demotivation and especially a huge loss of time that will be quantified in $$$, since instead of working on current projects and future deliverables, everyone will be monopolized to restore the situation.

Imagine all that happens in 24 hours: online transactions, synchronization and daily updates by scanners, cash registers, ERP and even manual entries... Such a recovery is simply unrealistic if the procedures are not in place.

If you have doubts about the capabilities of your current architecture, you have several options:

• Mobilize internal experts to perform an audit
• Call in external experts (if you lack the resources) to validate, adjust, or train your teams
• Invest in a robust solution

If you have resource issues, human or material, in managing your organization's backups, it may be time to choose a new one.

How to choose the right data backup solution?

Before implementing a new solution, it is important to ask yourself the right questions in order to choose the option that best suits your needs.

• How long will it take to recover the data?
• Can business continue during recovery?
• What is the recovery method?
• How much space is needed for backups and how fast will it grow?
• Where to back up: local, network or cloud?
• What is the retention period?

How to evaluate the space needed for backups?

To avoid unpleasant surprises, it is important to monitor the growth rate of backups. Techniques for monitoring the rate of database growth are easier when you know what is behind a database as well as how the data file (MDF) and transaction log (LDF) work.

Backing up locally, networked or in the cloud?

The only reason you back up is to restore as quickly as possible. While it is often easier to back up to a disk added directly to the SQL Server, consider the consequences if the SQL Server fails: recovery may be impossible. On the other hand, if the backups are on a network share or in the cloud, recovery can begin immediately. In any case, it is important not to neglect the management of roles and permissions in order not to be exposed to ransomware.

What retention period should I choose?

How long will the backup copies be kept? A longer retention period offers more latitude and flexibility, but increases storage requirements and access time. You need to determine what the ideal value is for your operations, as a long delay in accessing critical data can be detrimental. There may also be legal considerations, if your company has legal constraints on data retention for example.

Which recovery method is best?

There are several recovery modes for your databases.

• The simple mode facilitates the management and the size of your transaction logs, but it limits the possibilities of recovery (zero lost data).
• The full mode logs all daily transactions (insert, update, delete), but allows you to use your transaction log for backups.

The recovery mode you choose influences what you can recover from your databases, not to mention the impact on performance, the size of your backups and the transaction log.

What type of backup should I choose?

Depending on the recovery mode you choose, you can choose from three types of backup:

• It takes a full backup of your database.
• Differential. It takes all the different pages since the last full backup. This type saves time and disk space.
• Transaction log. It saves all the transactions of your databases and it offers the "zero data lost" (return to a specific point in time.

Most companies are satisfied with a single backup per day, due to a lack of information on the options available. Deciding which type of backup to favor involves having a good understanding of RTO and RPO.
RTO: "Recovery Time Objective" or the time required to bring your servers back online. RPO: Recovery Point Objective or the amount of data you are willing to lose.
These two indicators help determine the right plan to implement. And the higher your RTO and RPO requirements, the more you need to budget accordingly.

Compressed backups or not?

Compressing data reduces the size of backups, the amount of writes, and the overall duration of the backup process. On the other hand, compression uses more CPU. You need to know the subtleties of compression before using options like this.

You're ready in theory... but in practice?

Where to start before restoring your databases?

It is not after the server has failed that you need to ask yourself questions. You need to prepare upstream to meet the most pressing needs, with the least amount of uncertainty. This means having:

• Write functional maintenance plans
• Test the solutions in place
• Identify recovery processes
• Define roles and responsibilities
• Locate data recovery points

An action plan based on best practices minimizes the risk of loss and allows you to come back 1 second before the disaster.

Provided that key players are identified.

Who is in charge of data recovery?

Never assume that anyone will be able to do the job, especially in the context of a data recovery. You need to have the right people identified. To maximize the chances of success during a restoration, it is imperative to identify :

• The resources involved
• Their level of expertise
• Their knowledge/mastery of the procedure

Hence the importance of having a trained team, able to maintain operations in the long term.

Conclusion

If you don't have a handle on all aspects of data backup or if you have doubts about your strategy, it's time to take corrective action. You probably won't be as lucky as Pixar, and in any case, luck shouldn't be a deciding factor in your backup strategy. A well-trained team will be able to either optimize the existing solution or implement a new one with the right requirements.

To learn more :