May 3, 2008 at 1:08 pm
Comments posted to this topic are about the item SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)
May 5, 2008 at 2:19 am
The speed of the backup is a good news. But I have couple of questions? Will it take less space in the tape drive due to compression? Will it be a reliable backup?
🙂
May 5, 2008 at 3:08 am
Hi
On your 2 questions:
1) Space on Tape: if your tape device does compression on the fly as ours does. Our usual space saving is approx 65% when going onto tape.
This means that the 3.5GB file is stored on tape as 1.2GB - this is about 200MB more than the SQL 2008 Compressed backup.
If the original 3.5GB file is zipped using maximum compression then the ZIP file is approx 820MB (note: this maximum compression takes longer than the SQL 2008 Compression Backup)
So, this means that there is potentially 200MB that could be saved when storing onto tape for this 3.5GB database backup.
2) Reliability: because the SQL engine is doing the compression and not a third-party vendor, I am reasonably confident that this backup should be seen as reliable as the original .BAK without compression.
Thanks
Kevin
May 5, 2008 at 4:12 am
Hi,
and what about to restore? Do you need more additional space on disk to uncompress first?
Thanks in advance,
Prado
May 5, 2008 at 4:55 am
Hi
The "uncompress" actually happens inside the SQL Engine - so no additional space is required for a "temp" file. The only space required is the size of the Database and Transaction Log.
Note: If a backup file is 900MB - the actual size of the database Data file is 3.5GB and the Log 100MB - then this is the amount of space required on disk for the restored Database files (Data and Log)
Thanks
Kevin
May 5, 2008 at 5:32 am
Nice work Kevin!
The compress backups feature will come in handy in many environments - certainly will save space.
One question - I didn't see metrics for restoring. I'd be interested in seeing what the differentce in restore time would be for a compressed backup vs. non-compressed.
The reduced backup time was an interesting effect, it shows that the disk is probably the limiting factor in this instance.
Mark
May 5, 2008 at 5:45 am
Kevin (5/5/2008)
HiOn your 2 questions:
1) Space on Tape: if your tape device does compression on the fly as ours does. Our usual space saving is approx 65% when going onto tape.
This means that the 3.5GB file is stored on tape as 1.2GB - this is about 200MB more than the SQL 2008 Compressed backup.
If the original 3.5GB file is zipped using maximum compression then the ZIP file is approx 820MB (note: this maximum compression takes longer than the SQL 2008 Compression Backup)
So, this means that there is potentially 200MB that could be saved when storing onto tape for this 3.5GB database backup.
2) Reliability: because the SQL engine is doing the compression and not a third-party vendor, I am reasonably confident that this backup should be seen as reliable as the original .BAK without compression.
Thanks
Kevin
Thanks Kevin on your prompt reply. This clears my doubt. 🙂
May 5, 2008 at 5:55 am
Yes, the restore from compressed .BAK file will be an interesting item to investigate.
I will get my environment up and running and either post feedback on this discussion or provide feedback in the form of a more in-depth technical article on the topic of Restore & Backups with SQL 2008.
Thanks
Kevin
May 5, 2008 at 6:20 am
Hi again,
A lot of thanks for your answer. Your explanations are very useful so we'll wait your feedback about the test for restore.
Thanks,
Prado
May 5, 2008 at 6:33 am
Thank you for highlighting this valuable feature.
Saving +60% is certainly worth investigating it to the bone, considering the vase amount of backup data we produce.
Nowadays we compress the backup by using disk compression at our safezone. (backups are procuded local and xcopied to safezone)
Won't compression on compression produce an overhead ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 5, 2008 at 7:20 am
The compression on compression is an interesting question...
If the backup software placing data onto disk is sophisticated enough and it encounters a file with a zip header - does it "skip" compression for this file? I am not 100%.
I think that SQL 2008 and the compression that it does - has to deal with speed versus compression size, or the smallest .BAK file in a reasonably quick time.
Having said this, I believe that there is still a percentage of compression that can still be done on the compressed .BAK file (maybe only 50MB more on the 980MB file).
The fact is that the file is 28% of its initial size before the tape backup started this relates to:
* Quicker network transfer speeds to tape - if over a network
* Less data to process onto tape (less shoe-shine during compression of say 3.5GB)
* Potential header details within file describing construct of file that can be identified by backup software?
* Smaller storage on Disk (3 times more backup files can be retained than before)
But yes, more techinical analysis of backups and compression on compression would be an interesting concept to investigate and get real hard facts and figures for us all to understand.
Thanks
Kevin
May 5, 2008 at 7:24 am
Nice article. I also would be interested in the any difference in restore times. I am not working with VLDB'd or even LDB's so backup time is not an issue, but I am also interested in speeding up restore times.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 5, 2008 at 7:27 am
I regularly strip my backups into 5 files to speed up the process. Is this still an option when using compression?
May 5, 2008 at 7:36 am
From the article the DB used was able to be backed up in approx half the time and the size of the backup file was approx 1 third or 30% of its original size.
I am not 100% sure in what way you split your backups into 5 files - but based on the above - if the results are similar then you could have 5 smaller files created faster.
The nature of the space saving would need to be tested on your Database as each database has very different data contained within it and the design may vary drastically.
I would be interested to hear from someone who has tested backups using compression on SQL 2008 and their related database backups with and without compression.
Thanks
Kevin
May 5, 2008 at 7:48 am
If you mean adding multiple files as the destination for the Backup - then yes the above is true, and you may even be able to experiment with reducing the number of files - checking the difference is speed etc.
Another factor to consider is multiple processors and multiple disks and the I/O performance improvements this may bring
As far as I have seen the functionality with backups has not undergone any other major changes and functionality on the whole is the same as was for SQL 2005
(I will confirm that this is still available under the compression setting and let you know)
Thanks
Kevin
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply