Sql-server – Backup Compression SQL Server vs PURE

sql serverstorage

We are having a SQL 2016 Standard Edition Server holding 4TB of data. And the compressed backup using maintenance plans is taking around 1.5 hrs. Our storage team claims that as PURE FlashArray is used, SQL compression is not required as PURE compresses the backup. PURE documentation here – https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference – talks about uncompressed backups and de-dupe. Brent's article about de-dupe – https://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/.

What is the best practice SQL compression or Uncompressed backups on PURE. How do i measure it, as the stats are more powerful than statements.

Best Answer

There is not really a recommended choice for compressed versus uncompressed when the storage subsystem has compression/de-dupe features as there are too many variables, generally though I always enable compression even with storage subsystem compression.

You need to determine which is best for your particular environment and implement that. There are several areas you can check for comparison to validate which is the best choice.

  1. Query speed - Check the overall duration of backups with and without compression. Also, ask your storage team to disable de-dupe and compression at their end for your backup disks (if possible) and run a SQL compressed and non-compressed backup and measure the speed of those backups as well.
  2. Query throughput - Related to the overall duration, record the throughput in MB/s for compressed and uncompressed backups. Again, if you can run these tests with and without storage level compression that gives you more data to validate your choice.
  3. Compression ratio - You want to see who is getting the best compression ratio - if SQL is saving you more space than your storage subsystem for little to no cost in time then it may be the choice, or if it is only marginally larger in size but much quicker, again this may be an acceptable trade-off.
  4. Overall size - This is similar to compression ratio, but requires you to calculate the storage requirements taking into consideration your retention periods. If you need to keep daily FULL backups for 10 years, how much total storage is required, and how much space does the storage layer de-dupe save in that scenario? To answer these questions, you need the compression statistics from both SQL Server and the storage layer.

The below query will get statistics from your backup history to help you collate the data from a SQL Server perspective, and you need to get your storage team to extract their own statistics to show size, compression, throughput etc for comparison.

You should also run these tests on a non-Production SQL Server where you can change your configurations without impacting production.

Script:

WITH BackupDetails AS (
    SELECT 
        bs.[database_name] AS [DatabaseName],
        CASE
            WHEN bs.[type] = 'D' THEN 'Full'
            WHEN bs.[type] = 'I' THEN 'Differential'
            WHEN bs.[type] = 'L' THEN 'Log'
            WHEN bs.[type] IS NULL THEN 'No Backup'
        END AS [BackupType],
        COALESCE(CONVERT(VARCHAR(20), bs.[backup_start_date], 120),'-') AS [BackupStartDate],
        CASE
            WHEN DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date]) < 1 THEN 1
            ELSE DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date])
        END AS Duration_S,
        bs.[user_name] AS [BackupCreator],
        CAST(((bs.[backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [BackupSize],
        CAST(((bs.[compressed_backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [CompressedBackupSize],
        bmf.physical_device_name AS [File]
    FROM
        msdb..backupset bs
        INNER JOIN msdb..backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
)

SELECT [DatabaseName],
    [BackupType],
    [BackupStartDate],
    [BackupSize] AS [BackupSize(MB)] ,
    [CompressedBackupSize] AS [CompressedBackupSize(MB)],
    CAST(CAST(ROUND([BackupSize]/[CompressedBackupSize], 2, 1) AS DECIMAL(15,2)) AS VARCHAR(25)) + ' : 1' AS [CompressionRatio],
    [Duration_S],
    CAST([CompressedBackupSize]/[Duration_S] AS DECIMAL(15,2)) AS [Throughput (MB/s)],
    [File]
FROM BackupDetails
WHERE [BackupType] = 'Full'
ORDER BY
    [DatabaseName], [BackupStartDate] DESC
Related Question