Ontrack PowerControls for SQL

A full backup

Just like any other file on the network, the key to a successful database recovery is to have a full, current backup of the MDB and MDF files. You should be taking incremental copies of the database files at least once a day, combined with a full backup at least once a week.

A log file backup

A 100% accurate database restore is reliant on having a completely up-to-date set of log files that corresponds with data access and update operations. For industries that are heavily regulated, like finance and banking, the log files are essential to prove compliance.

Microsoft is keen to point out that you must have “enough” log backups available for a restore sequence, including the tail-log backup, before beginning the process.

Sufficient spare disk capacity

In order to test the restored data is correct, you will need to restore the database elsewhere first. This will then give you a chance to test the restored data and ensure that there is no corruption, and that you have an up-to-date set of records that gives a true indication of state before the crash/deletion/corruption.

Built-in provisions

Hekaton allows DBAs to select high priority tables and objects that need to be stored in RAM for super-fast querying. However, the underlying architecture has also been engineered to create a localized backup of that data:

Transaction log

Just like a standard disk-based database, Hekaton also relies on a transaction log record to track operations, and assist with SQL Server recovery. To ensure complete coverage, Hekaton integrates both disk-based and memory-based transaction logs, writing both to disk in batches.

Understanding the Data File

Rather than the traditional page structures used for data rows, Hekaton uses a pair of sequential files – Data and Delta files. The Data file contains free-form data rows that are then linked using in-memory indexes. Every time a row is changed by an INSERT or UPDATE statement, the changed row is appended to the end of the Data file.

To maintain the speed of operation and to maintain an accurate audit trail, when a row is deleted or updated from an in-memory table, it is not removed from, or changed in the Data file.

For very large Microsoft SQL Server databases, a complete restore operation can take many hours. During this time the database cannot be used to prevent data being entered and lost as information is copied back from tape or disk. In a high-availability environment, any downtime is costly, so keeping it to a minimum is essential.

Fortunately, page-level restore techniques can be used to keep recovery times to a minimum by reducing the amount of data that needs to copied back from the backup media. Since the release of Microsoft SQL Server 2005, DBAs have had the option of carrying out a ‘page level restore’ which allows them to recover a ‘handful’ of pages, rather than having to restore entire datasets and copy the information back into the original database.

The page-level restore operation is perfect for situations where data becomes corrupted during writes through a faulty disk controller, misconfigured antivirus software or an IO subsystem. Better still, restore level operations can be performed online for Enterprise editions of Microsoft SQL Server.

Before making a move to an SSD-based SQL Server data stores, CTOs need to decide:

• How great a risk SSD failure poses to SQL Server recovery operations.
• Which technology provides the best balance of performance and reliability (SSD vs. HDD).
• How to monitor the SSD drives on which logs are stored to assess health.
• How to recover data from a failed SSD in the event of a disaster or drive failure.

It is also important to remember that the strongest feature of SSDs – a lack of moving parts – is also one of the greatest weaknesses.  SQL data recovery from a failed SSD drive is an expert job, usually requiring professional assistance. Where platters could be removed and cloned from a traditional spinning disk in a cleanroom environment, SSD memory chips are typically harder to recover.

Data recovery from SSD memory cells is possible, but it remains a complicated procedure. And because any SQL Server deployment needs to include data recovery provisions as part of its plan, CTOs and systems architects need to consider and mitigate the impact SSD outages can have on availability.

Ontrack uses a number of techniques and custom software to extract, rebuild, and recreate corrupted SQL database files. With unique expertise and capabilities, Ontrack excels at recovering databases from the most challenging data loss scenarios.