Backup Performance

cousinsp

I was wondering if anyone would be able to give me a few pointers. I have a live and development server, both running SQL 2005 Enterprise Edition and the latest Redgate Backup software. I backup a database on the live server and it takes 2 hours. Restore the backup onto the development server (1.5 hours). Finally backup the database on the development server - 20 minutes!! This is with compression level 1 and 7 threads.
Live server:
4 dual core AMD opterons 8GB RAM Disk on entry level SAN
Dev Server:
2 quad core Intel 32GB RAM local disk arrays.
Generally these servers perform similarly.
My question is - is it the extra memory on the development machine which is making the backup so fast? The CPU cores all run at about 30% during the backup, whereas only about 4% on the live server.
Or is it more likely to be our disk arrangement.
Many thanks

petey

It's most likely due to the disk arrangement, either of the database files, or the backup file(s), or both.

The speed at which SQL Server can provide backup data to SQL Backup is one limiting factor. You can run something like this to test:

EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = [<AUTO>] WITH THREADCOUNT = 7, NOCOMPRESSWRITE" '

(adjust the above if you are backing up to 7 files instead of 1)

The NOCOMPRESSWRITE measures how fast SQL Server is able to read the database files for backup purposes, without actually compressing nor writing them to disk. Compare the numbers obtained on both the servers, and you can tell if the layout of the database files is what's causing the difference in backup throughput.

If you think it's due to the CPU architecture (not likely IMO), you can use the NOWRITE option e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = [<AUTO>] WITH THREADCOUNT = 7, NOWRITE" '

This causes SQL Server to pass backup data to SQL Backup, and also causes SQL Backup to compress the data, but not write them out to disk.

If the metrics obtained using NOCOMPRESSWRITE and NOWRITE show that throughput is fast, then the performance difference is due to the disks where the backup files are created on.

cousinsp

Many thanks for that tip. Use my full size database, this test is taking 15 mintues on the development box, and 58 minutes on the live box. It's as I feared - the SAN disk is 4 times slower than the SCSI array.
The sqlBackup even gave me throughput measurements.
thanks again.
Pete Cousins.