SQL2008 Backup Compression: Backup More Than 1GB per Second

Backup Compression in SQL2008 performs very well and it is easy to use. You could either use “With Compression” along with the “Backup” command or simply enable default backup compression through sp_configure by setting ‘backup compression default’ value to 1. It is very convenient for backups of large databases in our customer lab environment. A […]

Backup Compression in SQL2008 performs very well and it is easy to use. You could either use “With Compression” along with the “Backup” command or simply enable default backup compression through sp_configure by setting ‘backup compression default’ value to 1. It is very convenient for backups of large databases in our customer lab environment.

A few things you could do to make the backup compression perform better. The hardware we used is 32way HP Superdome and StorageWorks XP12000 disk array.

· Test to find the ideal number of backup devices to use

First database we backed up is 1.27TB in size with 12 database files (on 12 LUNs). The second database is 2.95TB in size with 76 database files across 45 LUNs. We tested backup using different number of backup files (8, 16, 26 and 32), and 16 backup devices demonstrate best performance in our environment.

The following table shows database sizes, backup execution times and total size of all backup files. The database is not page or row compressed and the backup devices are local.

Database SizeExec TimeBackup SizeAvg. MB/sec# Core
1.27TB20min320GB105932
2.95TB34min33s482GB142464

· Backup over the network (10G Ethernet):

  1. Jumbo Frame helps
  2. Set Buffer Count = 512

In our test, 1.27TB is backed up over the network in 24.5 minutes (862MB/sec).

Backup operation is IO intensive. Backup compression writes less pages to disk comparing to uncompressed backup, as long as you system is not bottlenecked on CPU, backup compression should executes faster than backup without compression.

Source:→ Microsoft

Microsoft, SQL Server 2008, SQL2008, Backup, Compression, Tips, Tricks, Tips and Tricks, Knowledgebase