Differential Database Backup with Memory-Optimized Tables

This blog describes how differential database backup is taken on database with one or more memory-optimized tables. For full database backups, please refer to Database Backup with Memory-Optimized Tables.

For disk-based tables, the differential database backup includes only the pages that have changed since the last full database backup.  SQL Server 2014 supports differential backup on databases with memory-optimized tables. The differential backup of a database with memory-optimized tables contains the following data

• The differential backup component for the disk-based tables is exactly the same as it is in databases without memory-optimized tables
• Active transaction log. This is exactly the same as with full database backup.
• For a memory-optimized filegroup, the differential backup uses the same algorithm as a full database backup to identify data/delta files for backup but it then filters out the subset of files as follows:
  • Data File – A data file contains newly inserted rows and once it is full, it is closed. Once the data file is closed, it is only accessed in read-only mode. A data file is backed up only if it was closed after the last full database backup. In the other words, the differential backup only backs up data files containing the inserted rows since the last full database backup.
  • Delta File – A delta file stores references to the deleted data rows. A delta file is always backed up. Since any future transaction can delete a row, a delta file can be modified anytime in its life time, it is never closed. Note, the delta files typically take < 10% of the storage.

Note, this optimization is only available in RTM. In CTP2, the differential database backup included all data/delta files just like in full database backup.

Let us walk through an example showing the difference in size of full database backup and a differential database backup. The example is based on the following database and the table schema. Please refer to blog State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables  for details on the state of CFPs in the following examples.

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

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

declare @i int = 0

while (@i < 8000)

begin

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

       set @i += 1;

end

— Do Manual checkpoint

Checkpoint

Example-1 Full and Differential database backup a database after loading 8000 rows and completing the manual checkpoint

• Checkpoint File Pairs (CFPs) – 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. Also, there are 5 CFPs in ACTIVE state as explained in the blog State-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.

• Full Database Backup: Wait 5 minutes and then do the full database backup. The 5-minute wait is more of a safety factor to make sure relevant data files are not missed due to time-drift. This is not a concern in production environment there as typically there is significant time span between checkpoints, full database backup and subsequent differential database backup.

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.

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 8612 pages for database ‘imoltp’, file ‘imoltp_log’ on file 1.

100 percent processed.

BACKUP DATABASE successfully processed 16980 pages in 1.592 seconds (83.323 MB/sec).

• Result: The size of the full database backup is around 138 MB. This includes size around 70MB of transaction log and around 70MB of CFPs.

• Differential Database Backup: Since we have completed the full database backup, the differential backup will skip data files as no new using the following command

BACKUP DATABASE [imoltp] TO 

DISK = N’C:dataimoltp-diff-data.bak’ WITH DIFFERENTIAL, NOFORMAT, INIT, 

NAME = N’imoltp-diff Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

• Output:

Processed 48 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.

30 percent processed.

40 percent processed.

51 percent processed.

60 percent processed.

71 percent processed.

81 percent processed.

91 percent processed.

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

100 percent processed.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8661 pages in 0.760 seconds (89.031 MB/sec).

• Result: The size of differential database backup is around 72 MB predominantly consisting of transaction log. The output shows that the existing data files in ‘ACTIVE’ state are not backed up as they were not changed since the last full database backup.

Example-2 Differential database backup after deleting 4000

 — now delete 50% rows

declare @i int = 0

while (@i <= 8000)

begin

       delete t_memopt where c1 = @i

       set @i += 2;

end

go

checkpoint

go

• Checkpoint File Pairs (CFPs) – No changes as only deleted existing rows

• Differential Database Backup: Execute the following command

— do the differential database backup

BACKUP DATABASE [imoltp] TO 

DISK = N’C:dataimoltp-diff-data-del50-chkpt.bak’ WITH  DIFFERENTIAL , NOFORMAT, INIT, 

NAME = N’imoltp-diff Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

• Output: the pages reported are computed at 8k size.

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

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

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

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

100 percent processed.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 8980 pages in 0.941 seconds (74.552 MB/sec).

• Result: Note, that transaction log has few more pages to account for deleted rows and the checkpoint. Also, 47 pages from files in memory-optimized filegroup were included in the backup. Out of this 47, the 32 pages are from the empty data file that was created with manual checkpoint. The internal page size in data files is 256K or 32 8k pages. The first page in the data file contains header information therefore it is part of the backup. Other 15 pages are from delta files. The total size of the backup is around 74.4MB