SQL Server Portal

Taking backup is the core responsibility of Database Administrator, either he takes the backup manually or he automates it but it does not matter. What actually matters is the database size because if it is very big then obviously it will take too much time in taking backup & occupy lots of disk space as well.

In SQL Server 2022, two new database backup compression algorithms were introduced as follows:

1. MS_XPRESS
2. QAT_DEFLATE

In this article, we are going to discuss one of the compression algorithms namely QAT_DEFLATE, which compresses backup size & reduce backup time drastically. We can use this compression algorithm for any types of database backup which are Full, Differential & Log backup. This algorithm will take less disk space and will reduce the backup time as well.

Let me demonstrate, how to implement this algorithm step by step.

Step 1:

Let’s check the database file size by using sys.database_files and as you can see the database size is ~10 GB.

USE SampleDB;
GO

SELECT DB_NAME()  AS DatabaseName
     , name       AS FileName
	 , size/128.0 AS CurrentSizeInMBs
FROM sys.database_files; 
GO

Step 2:

Let’s take full backup of SampleDB to see how much disk space & time it takes:

BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\Backup\FullBackupSampleDB.BAK';
GO

Step 3:

Let’s check the backup details especially the backup size and time. As we can see below, it took ~50 seconds to take Full backup and a disk space of ~10 GB

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date,
	DATEDIFF(second, backup_start_date, backup_finish_date) AS time_in_seconds
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Step 4:

We MUST install the Intel® QuickAssist Technology Driver in order to use QAT_DEFLATE algorithm. Recently I have written an article on Intel® QuickAssist Technology Driver installation that can be accessed from here.

Step 5:

Let’s configure QAT_DEFLATE compression algorithm as a default in SQL Server as shown below.

EXEC sp_configure 'backup compression algorithm', 2;   
RECONFIGURE; 
GO

Step 6:

Let’s check the configuration of backup compression by using sys.configuration. As we can see below, the value is 2 which means QAT_DEFLATE is successfully setup.

SELECT configuration_id
     , name
     , description
     , value   
FROM sys.configurations   
WHERE name = 'backup compression algorithm' ;  
GO

Step 7:

let’s configure the hardware_offload option which is also mandatory for QAT_DEFLATE algorithm as shown below.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE
GO

EXEC sp_configure 'hardware offload enabled', 1;
GO

RECONFIGURE
GO

ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD = ON(ACCELERATOR = QAT); 
GO

Step 8:

Please make SURE to RESTART the SQL Server in this step.

Step 9:

Let’s take the full backup of our database with compression but since we setup the default compression algorithm as QAT_DEFLATE in earlier step then by default it takes full back with QAT_DEFLATE backup compression algorithm, so NO need to write the name of the backup compression algorithm in the script as shown below.

BACKUP DATABASE [SampleDB] 
TO  DISK = 'D:\BackupFullBackupSampleDBWithCompression.BAK'
WITH COMPRESSION;
GO

Step 10:

Once the backup is taken, let’s check the backup details by using given below query. As we can see below, the backup compression algorithm is QAT_DEFLATE, the backup compression size is ~1.3 GB & it took 24 seconds to take back up. It is almost reducing the size by ~87% and time by ~52% as compared to original backup and which is a big achievement.

SELECT bs.database_name,
    backuptype = CASE 
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
        END + ' Backup',
    CASE bf.device_type
        WHEN 2 THEN 'Disk'
        WHEN 5 THEN 'Tape'
        WHEN 7 THEN 'Virtual device'
        WHEN 9 THEN 'Azure Storage'
        WHEN 105 THEN 'A permanent backup device'
        ELSE 'Other Device'
        END AS DeviceType,

	BS.compression_algorithm,
    backup_size_mb = CONVERT(DECIMAL(10, 2), bs.backup_size / 1024. / 1024.),
    compressed_backup_size_mb = CONVERT(DECIMAL(10, 2), bs.compressed_backup_size / 1024. / 1024.),
	bs.backup_start_date ,
	bs.backup_finish_date
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bf
    ON bs.[media_set_id] = bf.[media_set_id]
INNER JOIN msdb.dbo.backupmediaset bms
    ON bs.[media_set_id] = bms.[media_set_id]
WHERE bs.backup_start_date > DATEADD(MONTH, - 2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name ASC,
    bs.Backup_Start_Date DESC;

Conclusion:

I used QAT_DEFLATE backup compression algorithm & its results are quite good. Do let me know, which backup compression algorithm are you using and what is the outcome.

Read Full Post »