Official News from Microsoft’s Information Platform
The memory-optimized tables are backed up as part of regular database backups so you don’t need to do anything special to manage backups on databases with memory-optimized tables. Like for disk-based tables, the CHECKSUM of data/delta files is validated as part of database backup to proactively detect any storage corruption. However, as described in the blog storage allocation and management, the storage used by memory-optimized tables can be much larger than its footprint in the memory. A full backup of a database with one or more memory-optimized tables consist of the allocated storage for disk-based tables, active transaction log and the data/delta file pairs (i.e. checkpoint file pairs) for memory-optimized tables. This blog focuses on the size of database backups that you can expect in database with memory-optimized tables.
For the discussion here, we will focus on the database backups for databases with just durable memory-optimized tables because the backup part for the disk-based tables is the same irrespective of the existence of memory-optimized tables. The data/delta file pairs, also referred to as Checkpoint File Pairs or CFPs residing in the filegroup could be in various states at a given time. Please refer to merge-operation-in-memory-optimized-tables for details. The table below describes what part of the files is backed up both in CTP2 and in RTM.
Backup in CTP2
Backup in RTM
File metadata only
File metadata + allocated bytes
File metadata + used bytes
REQUIRED FOR BACKUP/HA
IN TRANSITION TO TOMBSTONE
Table - 1: CFP and Database Backup
You will find that the size of database backup in SQL Server 2014 RTMis relatively smaller than what you had in CTP2.
Let us walk through a few examples to show the size of the backups. All these examples are based on the following database and the table schema using pre-RTM bits. The state of checkpoint file pairs (i.e. CFPs) in the example here please refer to the blog state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.
CREATE DATABASE imoltp
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod', filename='c:\data\imoltp_mod') TO FILEGROUP imoltp_mod
-- create the table with each row around 8K
CREATE TABLE dbo.t_memopt (
c1 int NOT NULL,
c2 char(40) NOT NULL,
c3 char(8000) NOT NULL,
CONSTRAINT [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH (c1)
WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
We will use the following query to look at the states of CFPs
select file_type_desc, state, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn
order by file_type_desc, upper_bound_tsn
BACKUP DATABASE [imoltp] TO DISK = N'C:\data\imoltp-empty-data.bak'
WITH NOFORMAT, INIT, NAME = N'imoltp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10
-- load 8000 rows. This should use 5 16MB files
declare @i int = 0
while (@i < 8000)
insert t_memopt values (@i, 'a', replicate ('b', 8000))
set @i += 1;
Checkpoint files: Besides 8 CFPs in ‘PRECREATED’, there are now 5 CFPs ‘UNDER CONSTRUCTION’, each storing up to 1870 rows, to contain the 8000 data rows just inserted. Please refer to blog state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.
BACKUP DATABASE [imoltp] TO DISK = N'C:\data\imoltp-full-data.bak'
WITH NOFORMAT, INIT, NAME = N'imoltp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Processed 304 pages for database 'imoltp', file 'imoltp' on file 1.
Processed 0 pages for database 'imoltp', file 'imoltp_mod' on file 1.
10 percent processed.
20 percent processed.
Processed543 pages for database 'imoltp', file 'imoltp_log' on file 1.
100 percent processed.
BACKUP DATABASE successfully processed 8847 pages in 1.191 seconds (58.027 MB/sec).
-- do an manual checkpoint
-- the backup will include full log and the data. So the size is double - 138MB
BACKUP DATABASE [imoltp] TO DISK = N'C:\data\imoltp-full-data-chkpt.bak'
Processed 8064 pages for database 'imoltp', file 'imoltp_mod' on file 1.
30 percent processed.
40 percent processed.
Processed 8548 pages for database 'imoltp', file 'imoltp_log' on file 1.
BACKUP DATABASE successfully processed 16916 pages in 1.872 seconds (70.594 MB/sec).
-- now delete 50% rows
while (@i <= 8000)
delete t_memopt where c1 = @i
set @i += 2;
-- Do the manual merge. It generates merge-target and other files stay as regular files
-- The transaction range here is picked up by querying the DMV
-- sys.dm_db_xtp_checkpoint_files. Please find the appropriate range for your test.
exec sys.sp_xtp_merge_checkpoint_files 'imoltp', 1877, 12004
-- This installs the merge leading to 5 CFPs as MERGE SOURCE and the merge target
-- transitions to ACTIVE state
BACKUP DATABASE [imoltp]
TO DISK = N'C:\data\imoltp-full-data-chkpt-del50-merge-chkpt.bak'
-- the backup will include full log and the data.
Processed 12143 pages for database 'imoltp', file 'imoltp_mod' on file 1.
Processed 8815 pages for database 'imoltp', file 'imoltp_log' on file 1.
BACKUP DATABASE successfully processed 21262 pages in 1.794 seconds (92.588 MB/sec).
-- Do the log backup. This log backup is around 71MB
BACKUP LOG [imoltp] TO DISK = N'C:\data\imoltp-log-1.bak'
WITH NOFORMAT, INIT, NAME = N'imoltp-log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
-- Do one more log backup. This backup reduces active transaction log size
-- to 7MB
BACKUP LOG [imoltp] TO DISK = N'C:\data\imoltp-log-2.bak'
-- do one more log backup
BACKUP LOG [imoltp] TO DISK = N'C:\data\imoltp-log-3.bak'
Since the CFPs are converted to either TOMBSTONE or are in transition to it, the size of database backup is now 38MB (only 1 copy of data as transaction log has been backed up and freed)
TO DISK = N'C:\data\imoltp-full-data-chkpt-del50-merge-chkpt-logbkup3.bak'
Processed 288 pages for database 'imoltp', file 'imoltp' on file 1.
Processed 4128 pages for database 'imoltp', file 'imoltp_mod' on file 1.
Processed 23 pages for database 'imoltp', file 'imoltp_log' on file 1.
BACKUP DATABASE successfully processed 4439 pages in 0.394 seconds (88.012 MB/sec).
The size of backup of databases with one or more memory-optimized tables is typically bigger than the in-memory size of memory-optimized tables but smaller than the on-disk storage. The extra size will depend upon number of Checkpoint File Pairs (i.e. CFPs) in the states ‘MERGE SOURCE’ and ‘REQUIRED FOR BACKUP/HA’ which indirectly depends upon the workload.