SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)

  • Kevin-458339

    SSCrazy

    Points: 2717

    Comments posted to this topic are about the item SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)

  • Anipaul

    SSC-Insane

    Points: 24681

    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?

    🙂

  • Kevin-458339

    SSCrazy

    Points: 2717

    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

  • Mª del Prado Barba

    SSC Journeyman

    Points: 99

    Hi,

    and what about to restore? Do you need more additional space on disk to uncompress first?

    Thanks in advance,

    Prado

  • Kevin-458339

    SSCrazy

    Points: 2717

    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

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    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

  • Anipaul

    SSC-Insane

    Points: 24681

    Kevin (5/5/2008)


    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

    Thanks Kevin on your prompt reply. This clears my doubt. 🙂

  • Kevin-458339

    SSCrazy

    Points: 2717

    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

  • Mª del Prado Barba

    SSC Journeyman

    Points: 99

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • Kevin-458339

    SSCrazy

    Points: 2717

    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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Mr Tang

    Grasshopper

    Points: 11

    I regularly strip my backups into 5 files to speed up the process. Is this still an option when using compression?

  • Kevin-458339

    SSCrazy

    Points: 2717

    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

  • Kevin-458339

    SSCrazy

    Points: 2717

    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 55 total)

You must be logged in to reply to this topic. Login to reply