Relational databases form the core of many enterprises in a wide variety of industries. Most departments, for example, purchasing, manufacturing, warehousing and sales derive all their relevant product data and contact information from complex databases.
Online shops are inconceivable without underlying product databases containing descriptive texts, product images, prices and inventories. Even Web portals are supplied from content management systems taking content from a database.
In fact, the most successful Web 2.0 applications such as Xing, Facebook or YouTube would probably not exist were it not for databases.
Database Requirements
The ever-increasing dependency of many enterprises on IT entails an enormous risk – the total loss of a database would cost some companies a great deal of money and, in the worst-case scenario with the collapse of the foundation of their business, would lead them into bankruptcy. It must not be forgotten that there are also legal requirements under GDPdU, KonTraG and Basel II, which demand the long-time archiving of digital data in companies.
The corresponding database components must therefore possess the highest flexibility and faultlessly supply data at all times (data consistency) while tolerating only the minimum downtime. It comes as no surprise, then, that IT managers look for solutions which firstly ensure the stable, fail-safe operation of the database and secondly permit the most complete backup of the database and a recovery scenario without data loss.
To understand the possible problems and repercussions of an incorrect or incomplete data backup, we must first deal with the functionality and operation of the databases used.
Generally, in addition to the actual database, a transaction log is generated, in which all transactions and database changes made are recorded. Both components must therefore be backed up, if possible at regular intervals, and always with 100% synchronization.
Incorrect Backup Strategies
Databases are inherently characterized by dynamism. New data is constantly being written, and existing database entries modified or deleted. It's therefore not appropriate in practice to regularly back up the database to tape or hard disk during the night-time hours or at the weekend.
Databases do not fit in the context of a conventional backup strategy. For example, if the database must be additionally shut down during the backup (cold backup), it is not available in the production environment (downtime). In an online shop open 24 hours a day; no orders can be placed during that downtime.
Much more dramatic than that, however, is the fact that, in the event of recovering an old database backed up several hours before the crash, it's inevitable that valuable information added since the last backup will be lost. In certain circumstances, this could mess up the entire logistics of a company: For example, orders and deliveries vanish from the system, and the goods receiving and goods issuing departments are dealing with incorrect figures. In the best case, the missing data can be added to the transaction log although labour intensive – however, that is more the exception than the rule.
Special data backup tools work better, but are not ideal. With the aid of so-called open file backup options, these tools wait for an access-free time before backing up a more or less 1:1 image (snapshot) of the opened file to the server’s hard disk. However, caution is warranted. Only if the data is buffered during the backup and not written to the transaction log does the database remain consistent.
Recovery with Tricks
Even if a complete or differential backup of the database and transaction logs exists, a recovery can still be beset with problems and can fail: In Microsoft SQL Server, for example, all existing transaction logs (created after the last complete or differential database backup) must be recovered in chronological order. If a transaction log in this log chain becomes lost or damaged, only the transaction logs before the missing one can be recovered.
A step forward is the Point in Time Recovery procedure using the Write-Ahead Logging (WAL) technology. All transactions are thus saved to log files from a consistent status (the so-called checkpoint). In this way, it is possible to restore a database to a consistent status after a crash. When restarting the database server, it starts from the last checkpoint, reads the transaction logs and executes all the transactions logged in them. In this way, the system is restored to the status it had before the crash.
Security is Not a Question of Budget
The operation of primary and secondary servers is secure, though expensive and technically complex to implement. In the event of a system failure in the database server, in a fraction of a second it switches to the standby system which always possesses an identical database. Many companies recoil from such a solution because, apart from the duplicated hardware requirements, they are also lumbered with double licence fees for the operating system and database system if open-source solutions are not used.
As a further development of Point in Time Recovery, the technology known as Automatic Recovery to Point of Failure aims to become established as the modern and reliable backup solution for databases. It aims to make the same high security and data availability possible that can otherwise only be offered by hardware solutions with primary and secondary systems.
The basis of Automatic Recovery to Point of Failure is the complete and differential backup of databases and their associated transaction logs on the fly – fully automatically and without the manual intervention of the administrator. The highlight: In contrast to previous solution approaches, in the event of a disaster, the data is not recovered to a fixed backup time, but to the state immediately before the error occurred. In recovering a database backup, the transaction log of the database is read. Only in the rarest of cases is this log also affected by the crash. Using this log file, the last actions of the database can be duplicated fully, automatically, and the database can be brought back as close as possible to the moment before the crash. The automatic functions dispense with the laborious manual tracking of log files.
The author is General Manager of Acronis Asia |
