Overview
In-Memory OLTP, also known as ‘Hekaton’ and ‘In-Memory Optimization’, is Microsoft’s latest in-memory processing technology. In-Memory OLTP is optimized for Online Transaction Processing (OLTP) which can enable businesses to instantaneously run analytics and reports that previously took hours or days. This real-time information results in better decisions that can lower costs, improve productivity, and increase competitiveness.
In-Memory OLTP is supported with SQL Server 2014 onwards and mainly features two new data structures which are Memory-Optimized Tables and Natively Compiled Stored Procedures. They are integrated into SQL Server’s Database Engine and can be used in the same manner as their traditional counterparts, allowing applications to leverage new performance benefits with minimal changes to the database and the application.
However, since the underlying storage format is different (data held in RAM is also persisted on localized disk) it calls for backup applications to protect the In-Memory database to offer consistent backup and recovery functionality.
Rest assured, starting in Backup Exec 21.2, we now support In-Memory-Optimized databases for backup and recovery. They are backed up like any other traditional tables such as log backups and can be restored in various ways. Additionally, Backup Exec also supports memory-optimized filegroups and relative file stream data present on disk to ensure the restore can reconcile checkpoints without any loss of data.
Backup and Restore Process
Memory-optimized tables are backed up as part of regular database backups. They will be visible to browse, select for backups, and can be identified by an ‘InMemory’ attribute as shown in the figure below.
A full backup of a database with one or more memory-optimized tables consists of the allocated storage for disk-based tables, the active transaction log, and the data and delta file pairs (also known as checkpoint file pairs) for memory-optimized tables.
So apart from executing the regular SQL DATABASE BACKUP commands and creating the checkpoint backing up the database streams, Backup Exec also protects the File Group container by backing up the on-disk file stream data.
Backup Exec supports full, incremental, and differential backups of the In-Memory databases just like normal databases.
Backup Exec supports all restore scenarios including redirected restores to match that of the normal databases. Given that Backup Exec provides multiple restore options, we’ve created the following comparison table for reference.
| Operation | Target | Options | Traditional SQL database MDF & LOG |
In-Memory OLTP On Disk Container |
| Normal Restore | Restore data to the original SQL Instance and database | Default | Restores to the Instance default path | Restore recreates the File Group Container folder on disk. All original folders and subfolders are also recreated |
| Redirected Restore | Redirect to the original SQL Instance. |
1. Database field left blank 2. Use destination Instance's default data directories |
Restores to the Instance default data path. | Restores to the Instance default data path. |
| Redirected Restore | Redirect to the original SQL Instance. |
1. New Database name specified 2. Use destination Instance's default data directories |
Renamed database MDF and LOG files are restored to the Instance data path. |
Renamed File Group Containers are restored to the Instance data path. If the Container path contains the database string, it renames that that string with the new database name. Otherwise, the new database name is added as a prefix to the Container path. |
| Redirected Restore | Redirect to a different SQL Instance. |
1. Database field left blank 2. Use destination Instance's default data directories |
Restores to the Instance default data path. | Restores to the Instance default data path. |
| Redirected Restore | Redirect to Original or different SQL Instance. |
1. New Database name specified |
Restores to the Instance default data path. |
Restores to the Instance default data path. If the Container path contains the database string, it renames that that string with the new database name. Otherwise, the new database name is added as a prefix to the Container path. |
| Redirected Restore | Redirect to same or different SQL Instance. |
1. Database field may be left blank or a new user-specified path |
Restores to the user-specified path |
Restores to the user-specified path. Will only create the File Group Container path. Intermediate folder path elements may not get created. |
Memory Considerations
Memory-optimized databases frequently have substantial memory requirements, and you should consider those requirements when planning for backup and recovery. Although the actual memory needed for the database often depends on the application which relies on said database, a ballpark figure would be roughly two times the size of the database tables. Keeping that figure in mind, make sure that you have extra memory available for processing restore activity.
The recent Backup Exec 21.1 release added support for SQL Server’s Always On Availability Groups and now the Backup Exec 21.2 release adds seamless support for SQL In-Memory OLTP databases. These features allow the Database Administrator (DBA) to leverage SQL Server capabilities and confidently plan for SQL server backup and recovery to meet the business’s needs.
These types of improvements come from our continued commitment to quickly add to new features based on feedback from our customers.
If you are not a current Backup Exec customer, we invite you to learn more about the solution at the following link: www.backupexec.com
