Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Backup and Recovery of Memory-Optimized Databases

Memory-optimized tables are backed up as part of regular database backups. A full backup of a database with one or more memory-optimized tables consists of the allocated storage for disk-based tables (if any), the active transaction log, and the data and delta file pairs (CPFs) for memory-optimized tables. It’s important to keep in mind that the storage used by the CPFs for memory-optimized tables can be much larger than its size in memory, and it affects the size of the database backup.

For databases where the activity on the memory-optimized tables is primarily inserts, most of the memory-optimized tables data files will be in the Active state, fully loaded, and with very few deleted rows. The size of the database backup will be close to the size of data in memory.

For databases with frequent insert, delete, and update operations, each of the checkpoint file pairs in the worst case scenario could be only 50% loaded, after accounting for the deleted rows. The size of the database backup could be at least 2 times the size of data in memory. Additionally, there will likely be few checkpoint file pairs in states ”Merge source” and ”Required for backup/high availability” that will add to the size of database backup.

Differential backups are also allowed with databases containing memory-optimized tables. Differential backup of a database with memory-optimized tables contains the differential backup for filegroups storing disk-based tables plus the active transaction log. For a memory-optimized data filegroup, the differential backup uses the same algorithm as full database backup to identify the data and delta files for backup but it includes only memory-optimized table data files if they’ve been closed since the last full database backup. All delta files are still included since a delta file can be modified anytime in its lifetime.

If memory-optimized tables are a significant portion of your database size, the differential backup can be significantly smaller than a full backup.

  • + Share This
  • 🔖 Save To Your Account