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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kevin van der Merwe... » SQL 2005 vs. SQL 2008 Part 1 - (Backup File...

55 posts, Page 2 of 6

««1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Kevin-458339

Posted Monday, May 05, 2008 7:20 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

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

Jack Corbett

Posted Monday, May 05, 2008 7:24 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 10,613, Visits: 11,958

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

Mr Tang

Posted Monday, May 05, 2008 7:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:19 AM
Points: 1, Visits: 55

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

Post #494938

Kevin-458339

Posted Monday, May 05, 2008 7:36 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

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

Kevin-458339

Posted Monday, May 05, 2008 7:48 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

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

SequelDBA

Posted Monday, May 05, 2008 8:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 35, Visits: 771

Great post!

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

KU

Post #494984

Kevin-458339

Posted Monday, May 05, 2008 8:15 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

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

Kevin-458339

Posted Monday, May 05, 2008 8:17 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

I think this is an Enterprise feature only

Post #495000

GSquared

Posted Monday, May 05, 2008 9:26 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

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

Kevin-458339

Posted Monday, May 05, 2008 9:42 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 07, 2013 1:36 AM
Points: 334, Visits: 3,136

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 »

55 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions