Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times) Expand / Collapse
Author
Message
Posted Monday, May 5, 2008 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
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
Post #494932
Posted Monday, May 5, 2008 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #494936
Posted Monday, May 5, 2008 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 8:26 AM
Points: 1, Visits: 68
I regularly strip my backups into 5 files to speed up the process. Is this still an option when using compression?
Post #494938
Posted Monday, May 5, 2008 7:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
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
Post #494953
Posted Monday, May 5, 2008 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
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
Post #494969
Posted Monday, May 5, 2008 8:02 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:19 PM
Points: 35, Visits: 863
Great post!

Question: Is this feature available with Std Edition or only Enterprise?



KU
Post #494984
Posted Monday, May 5, 2008 8:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
SQL 2008 does still support this feature of splitting the Backup over multiple files.

During Backup and compression it was writting to each of the 5 files in a series basis and I am not sure how multiple procs and disks would affect, imporve this?

The compression was approx 190MB per each of the 5 files - meaning that the 980MB was split amongst the 5 files.

(Time wise it seemed to take longer a minute or two - but I would need to confirm this)

Thanks
Kevin
Post #494997
Posted Monday, May 5, 2008 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
I think this is an Enterprise feature only
Post #495000
Posted Monday, May 5, 2008 9:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Very interesting numbers. Since I have to ship backup files across a low-speed network, this could come in very useful.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #495078
Posted Monday, May 5, 2008 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 335, Visits: 3,163
Yes, in SQL 2008 - the Database Mirrioring and Log Shipping* also benefit from this compression technology built into the engine.

I am not sure if this is default or a setting for the above, but I will investigate and provide more details in future articles.

Thanks
Kevin
Post #495091
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse