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.
- 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.
- 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.
- 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.
- 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.
WITH BackupDetails AS (
bs.[database_name] AS [DatabaseName],
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],
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]
INNER JOIN msdb..backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
[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],
CAST([CompressedBackupSize]/[Duration_S] AS DECIMAL(15,2)) AS [Throughput (MB/s)],
WHERE [BackupType] = 'Full'
[DatabaseName], [BackupStartDate] DESC