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) AS
CompressionPercentage, database_name, [type],
backup_start_date
FROM msdb.dbo.backupset
WHERE
backup_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
