Database Backup with Memory-Optimized Tables

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.  

Full Database Backup

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.  

CFP State	Backup in CTP2	Backup in RTM
PRECREATED	File metadata only	File metadata only
UNDER CONSTRUCTION	File metadata + allocated bytes	File metadata only
ACTIVE	File metadata + allocated bytes	File metadata + used bytes
MERGE SOURCE	File metadata + allocated bytes	File metadata + used bytes
MERGE TARGET	File metadata + allocated bytes	File metadata only
REQUIRED FOR BACKUP/HA	File metadata + allocated bytes	File metadata + used bytes
IN TRANSITION TO TOMBSTONE	File metadata + allocated bytes	File metadata only
TOMBSTONE	File metadata + allocated bytes	File metadata only

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

GO

ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE imoltp ADD FILE (name=‘imoltp_mod’, filename=‘c:dataimoltp_mod’) TO FILEGROUP imoltp_mod

GO

use imoltp

go

— 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)

Go

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

from sys.dm_db_xtp_checkpoint_files

order by file_type_desc, upper_bound_tsn

Example-1: Backup a database with no rows in memory-optimized tables

• Checkpoint files – Since I am running on a machine with < 8 cores with < 16GB of physical memory, I have 8 CFPs in ‘PRECREATED’ state with 16MB sized data file and 1MB sized delta file. There is 1 CFP in ‘UNDER CONSTRUCTION’. Please refer to blog state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.

• Backup Command:

BACKUP DATABASE [imoltp] TO DISK = N’C:dataimoltp-empty-data.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,STATS = 10

• Result: The size of the full database backup is around 5.3MB.  As you can see even though the total storage taken on the disk is (16MB*9) + (1MB*9) = 153MB, but the size of the backup is much smaller. Other thing to note is that even though there are no data rows inserted, still significant storage (i.e. 144 MB) was taken primarily because of the fixed storage overhead.

Example-2: Backup the database after loading 8000 rows

— load 8000 rows. This should use 5 16MB files

declare @i int = 0

while (@i < 8000)

begin

       insert t_memopt values (@i, ‘a’, replicate (‘b’, 8000))

       set @i += 1;

end

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 Command:

BACKUP DATABASE [imoltp] TO  DISK = N’C:dataimoltp-full-data.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

• Output:

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).

• Result: The size of the full database backup is around 73.5MB.  Note that the size of the transactional log is 70MB and there is no data actually backed up because the CFPs are either in ‘PRECREATED’ or ‘UNDER CONSTRUCTION’ as show in the Table-1. The data is only guaranteed to be in the data/delta files after CFP transitions into ‘ACTIVE’ state.

Example-3: Backup the database after taking an explicit checkpoint

— do an manual checkpoint

checkpoint

• Checkpoint files: Besides 8 CFPs in ‘PRECREATED’, there are now 5 CFPs with state ‘ACTIVE’. The manual checkpoint transitions the CFPs in ‘UNDER CONSTRUCTION’ state to ‘ACTIVE’.

• Backup Command:

— the backup will include full log and the data. So the size is double – 138MB

BACKUP DATABASE [imoltp] TO DISK = N’C:dataimoltp-full-data-chkpt.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Output:

Processed 304 pages for database ‘imoltp’, file ‘imoltp’ on file 1.

10 percent processed.

20 percent processed.

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.

100 percent processed.

BACKUP DATABASE successfully processed 16916 pages in 1.872 seconds (70.594 MB/sec).

• Result: The size of the full database backup is around 138MB. It consists of 70MB of transaction log and also 66MB (8064 8k pages) of contents from data/delta files.  

Example-4: Backup the database after deleting 50% rows and merging the CFPs

— now delete 50% rows

declare @i int = 0

while (@i <= 8000)

begin

       delete t_memopt where c1 = @i

       set @i += 2;

end

checkpoint

— 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

go

— This installs the merge leading to 5 CFPs as MERGE SOURCE and the merge target

— transitions to ACTIVE state

Checkpoint

go

BACKUP DATABASE [imoltp]

TO DISK = N’C:dataimoltp-full-data-chkpt-del50-merge-chkpt.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Checkpoint files: Besides 8 CFPs in ‘PRECREATED’, there are now 5 CFPs with state ‘MERGE SOURCE’ and 2 CFPs as ‘ACTIVE’.

• Backup Command:

— the backup will include full log and the data.

BACKUP DATABASE [imoltp] TO DISK = N’C:dataimoltp-full-data-chkpt.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Output:

Processed 304 pages for database ‘imoltp’, file ‘imoltp’ on file 1.

10 percent processed.

20 percent processed.

Processed 12143 pages for database ‘imoltp’, file ‘imoltp_mod’ on file 1.

30 percent processed.

40 percent processed.

Processed 8815 pages for database ‘imoltp’, file ‘imoltp_log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 21262 pages in 1.794 seconds (92.588 MB/sec).

• Result: The size of the full database backup is around 172 MB. It consists of 72MB of transaction log and also 100MB of data/delta files. The reason the size has grown around 50% even though we have deleted 4000 rows is because ‘MERGE SOURCE’ CFPs still have all 8000 rows.

Example-5: Backup after MERGE SOURCE CFPs transition to TOMBSTONE state

checkpoint

go

— Do the log backup. This log backup is around 71MB

BACKUP LOG [imoltp] TO DISK = N’C:dataimoltp-log-1.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-log Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

checkpoint

go

— Do one more log backup. This backup reduces active transaction log size

— to 7MB

BACKUP LOG [imoltp] TO DISK = N’C:dataimoltp-log-2.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-log Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

checkpoint

go

— do one more log backup

BACKUP LOG [imoltp] TO DISK = N’C:dataimoltp-log-3.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-log Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Checkpoint files: Besides 8 CFPs in ‘PRECREATED’, there are now 6 CFPs ‘IN TRANSITION TO TOMBSTONE’ and there 5 CFPs ‘ACTIVE’.[JdB1][SA2]  Only one ACTIVE CFP contains 4000 data rows other ACTIVE CFPs were created due to manual checkpoint. In a production environment, the checkpoints will occur automatically and log backups will be taken as part of regular operational process.

• Backup Command:

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)

BACKUP DATABASE [imoltp]

TO DISK = N’C:dataimoltp-full-data-chkpt-del50-merge-chkpt-logbkup3.bak’

WITH NOFORMAT, INIT, NAME = N’imoltp-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Output:

Processed 288 pages for database ‘imoltp’, file ‘imoltp’ on file 1.

10 percent processed.

Processed 4128 pages for database ‘imoltp’, file ‘imoltp_mod’ on file 1.

Processed 23 pages for database ‘imoltp’, file ‘imoltp_log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 4439 pages in 0.394 seconds (88.012 MB/sec).

• Result: The size of the full database backup is around 38 MB. This is because we have around 32MB of data in ACTIVE CFP. There are no CFPs in ‘MERGE SOURCE’ or ‘REQUIRED FOR BACKUP/HA’ states.

Summary

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.