Let me set the scene, one of our internal IT SQL Servers which stores a whole host of performance metrics has over the last few months’ experienced tremendous growth as we have started to monitor more metrics on more servers. A routine we have in place to collect and report on a multitude of server and database information including database sizes and growth highlighted this to me along with a significant decrease in free space on the backup volume. We use SQL Server to back up our databases as opposed to a third party product as any benefits we may gain are outweighed by the cost.
When checking the backup routine on the server I noticed that we were not using backup compression. Rather than allocating more expensive SAN storage to the volume I turned on backup compression, checking the server a day later I was pleased to report an 80% saving in cumulative backup size across the server, Happy Times! J
You can use the script below to calculate the backup compression percentage;
/*
-----------------------------------------------------------------
Calculate backup compression percentage per backup
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set database context
USE msdb;
GO
-- Calculate compression ratio of all backups taken in the last 24 hours
SELECT (((backup_size - compressed_backup_size) / backup_size) * 100) ASCompressionPercentage, database_name, [type],backup_start_date
FROM msdb.dbo.backupset
WHEREbackup_start_date > GETDATE()-1
ORDER BY backup_start_date DESC;
GO
Backup compression was introduced in SQL Server 2008 and although it may not have as many bells and whistles as some third party vendors it is still an extremely valuable addition to SQL Server and one which isn’t is used nearly as much as believe it should. The saving alone in disk space makes it a no brainer surely?! I mean 80% saving in storage across a single server is something we as DBA’s can ill afford to ignore, turning backup compression on, on an additional 4 servers this did however drop slightly to 71% but that is still a huge saving. Let’s say that per 1TB of enterprise storage costs £5,000 even with a 50% saving using backup compression that is £2,500 saved that can be used elsewhere for training courses, conventions, books etc. Add to this the fact that because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.
The amount of compression you achieve will vary depending on the below factors;
· The type of data.
Character data compresses more than other types of data.
· The consistency of the data among rows on a page.
Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.
· Whether the data is encrypted.
Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.
· Whether the database is compressed.
If the database is compressed, compressing backups might not reduce their size by much, if at all.
Before you blindly turn on backup compression there are a few things to be wary of though;
· Compressed and uncompressed backups cannot co-exist in a media set.
· Previous versions of SQL Server cannot read compressed backups.
· NTbackups cannot share a tape with compressed SQL Server backups.
· By default, compression significantly increases CPU usage.
As always, make sure any change you make has been thoroughly tested and any and all implications understood!
Enjoy!
Chris
