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

Question

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

Viewing 15 posts - 31 through 45 (of 54 total)

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

ABHILASH DHONGDI SSC Veteran Points: 248 More actions · Answer 1

Kevin,

Nice work man... going by the screenshots it seems like you had this running on UniProcessor Environment and with 1 GB RAM.

couple of questions - Any chance you have metrics on a better server? and secondly, was there anything else running on this box when you took either of the backups?

I am concerned about the spike in CPU load during the compressed backups. Seems to put quite a lot of load the box. Wondering if there are any settings to limit the compression algorithm to minimal/medium/full... etc..

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 2

Hi

The environment was a Virtual PC - running XP SP2 with 1GB RAM.

There was no other applications running on this machine at the time of the backup.

Thanks

Kevin

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 3

I do not have any info on a better spec'd server - but may in the near future.

I have not investigated the possibility of adjusting the compression percentage, but this would be an interesting idea.

The only things that you may save in a less compression setting is speed and CPU, but this may be required if you consider the limitations on disk speeds and CPU utilization etc

Thanks

Kevi

Nik Southworth Newbie Points: 3 More actions · Answer 4

My Test Results.

SQL 2008 w/ Compression

Size: 3.39GB

Time: 12 Mins

CPU%: AVG 40%

SQL 2008 wo/ Compression

Size: 14.0GB

Time: 21 Mins

CPU%: AVG 30%

Restore details to follow.

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

Nik Southworth (5/6/2008)
My Test Results.
SQL 2008 w/ Compression
Size: 3.39GB
Time: 12 Mins
CPU%: AVG 40%
SQL 2008 wo/ Compression
Size: 14.0GB
Time: 21 Mins
CPU%: AVG 30%
Restore details to follow.

Good findings. In terms of speed and size the difference is almost more than 50%. The more big the database the more difference will come I believe. But one thing what will be the compression rate of image field?

Michael Whiteley SSC Enthusiast Points: 134 More actions · Answer 6

Be interesting to see how this compression compares to software products that already do compression, is the sql 2008 better, worse or the same ?

Rick Sheeley SSC Rookie Points: 40 More actions · Answer 7

Having worked with the Redgate folks for over three years now, I would say they are more interested in advancing technology and making good products for their customers, reahter than being worried about the platform.

They have been hands down, one of the 5 best vendors I have ever worked with. But one would expect no less from a bunch of Guiness swilling British engineering types!! 😀

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

Excellent article and and a very nice analysis of a new feature in 2008. Of course, the feature is much less impressive to people like me already using something like SQL Backup 5 which has excellent compression, but its nice to have the option available.

I would also like to join the call for similar metrics and analysis of restore. It would also be interesting to see a comparison between this and what SQL Backup 5 does in terms of speed and compression ratios.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

cameron.gibbs Valued Member Points: 57 More actions · Answer 9

Of course SQL backup compression is great....but my understanding is that it is only available with SQL Enterprise edition - which costs a helluva lot more than Standard.

Why oh why Microsoft woudl you make something like backup compression an Enterprise only feature....I can understand the BI and analysis stuff being pared down...but not THIS

Michael Whiteley SSC Enthusiast Points: 134 More actions · Answer 10

backup compression is available in all editions.

Data compression of the online database is an enterprise only feature, which is different from backup compression.

That is what I was told at a microsoft seminar.

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 11

Hi

That does sound about right. If the SQL Engine has the compression built in then it would be crazy to have diffirent versions or limitations based on the edition.

But yes having data compression on the fly when storing data to the datafile is still seen as a new exciting feature and would more than likely be a selling feature for Enterprise edition.

But the good news is that this technology now exists and it should eventually become a standard feature for all editions - we just hope that this does not take too lon to get to us.

Anipaul SSC-Insane Points: 24681 More actions · Answer 12

I also agree that it should available in 2-3 versions. 🙂

cameron.gibbs Valued Member Points: 57 More actions · Answer 13

Sorry Michael - refer to this document published by Microsoft which clearly states that backup compression is Enterprise only

THat's why we're looking at RedGate SQL Backup as an alternative

Cameron

Michael Whiteley SSC Enthusiast Points: 134 More actions · Answer 14

The document does say subject to change; and it doesn't say when it was compiled ( which is a bit annoying).

I will send an email to my microsoft contact to see he can confirm anything has changed.

Be a real shame, but I wonder if it is on purpose to keep the likes of red-gate products available..

does look purely marketing rather than that only enterprises would need to compress backups.

Will post a reply if I get one from MS contact.

Reply is :

"Both backup compression and data compression are enterprise edition features. Restoring a compressed backup can be done using any edition.

Usually the idea is that if it’s a scalability or availability related feature then its EE."

It must have been the restore bit i remembered.

JStiney Say Hey Kid Points: 671 More actions · Answer 15

Kevin,

Please state which edition(s) of SQL 2008 include the feature you are discussing in the series.

Thanks.