2008 Vs LiteSpeed

Question

2008 Vs LiteSpeed

Tara-1044200

SSCoach

Points: 15951
More actions
April 12, 2010 at 7:51 am

#213642

I am aware that 2008 does data compression and Backup compression too. AS of now my 2005 databases are backedup using litepspeed and it will compress upto 80% @ compression level =5 and i would like to compare this with 2008 compression, just thinking if i can get rid of litespeed when migrated to 2008 but i found from some articles that using 2008 backup compress by 18% when database are at row level compressoin and 47% at page lvel compression. That concludes that i can not replace litespeed with 2008 atleast for backups though i migrate to 2008 i might have to use litespeed.
Any comments please ?

Viewing 15 posts - 1 through 14 (of 14 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Litespeed, and RedGate's SQL Backup both do better compression than SQL 2008 does on its own, in most situations.

- 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

Toby White SSCertifiable Points: 6187 More actions · Answer 2

Redgate and quest also provide object level restores with their backup solutions. Additionally Quest offers the ability to save a backup as a self extracting .exe file that can be encrypted for easy transportation to environments that might not have Litespeed.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Litespeed and SQL Backup allow you to trade off compression for time/resources as well, with different levels. The native SQL 2008 compression is on/off only.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

In addition to what the others have stated, both Quest and SQL Backup utilize multiple 'threads' for the backup process - reducing the time it takes to backup in addition to the compression.

You should also note that backup compression is completely different from page/row/database compression. Backup compression only affects the size of the backup file, whereas page/row/database compression affect the size of the actual data (mdf) files.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

Toby White (4/12/2010)
Redgate and quest also provide object level restores with their backup solutions. Additionally Quest offers the ability to save a backup as a self extracting .exe file that can be encrypted for easy transportation to environments that might not have Litespeed.

...but, you need to be aware, that because of O/S restrictions, the .exe can not be over 4GB..... so if your (compressed) backup file is over that, forget about the .exe option unless you stripe it into smaller chunks.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Toby White SSCertifiable Points: 6187 More actions · Answer 6

...but, you need to be aware, that because of O/S restrictions, the .exe can not be over 4GB..... so if your (compressed) backup file is over that, forget about the .exe option unless you stripe it into smaller chunks.

I didn't bother mentioning the striping because it's so easy:

exec master.dbo.xp_backup_Database

@Database = 'NameofDatabase'

,@Filename = '\\BackupServer\Nameofbackup.exe'

,@Filename = '\\BackupServer\Nameofbackup1.BAK'

,@Filename = '\\BackupServer\Nameofbackup2.BAK'

,@Filename = '\\BackupServer\Nameofbackup3.BAK'

,@Filename = '\\BackupServer\Nameofbackup4.BAK'

,@Filename = '\\BackupServer\Nameofbackup5.BAK'

,@Filename = '\\BackupServer\etc.etc'

,@init = 1

,@CompressionLevel = 7

,@Threads = 4

,@DoubleClick = 1

,@cryptlevel = 6

,@EncryptionKey = 'StrongPassword'

All you have to do is figure out what the total size of the backup would roughly be and then divide that by the amount of files that would get you down to what you want your file sizes to be.

Richard M. SSCertifiable Points: 7401 More actions · Answer 7

I know that, but when you have a TB database size, compressed down to about 207GB... that still would be about 52 files!!!!

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

tony.turner Right there with Babe Points: 783 More actions · Answer 8

If you already have Litespeed you may not want to throw it out. However, if you don't, using row/page compression ion the database, backup compression, backing up to multiple disk files, and increasing the buffer numbers and sizes may be a reasonable alternative

Depending on your environment certain table schemas may compress particularly well using row/page compression, and the rediuced IO may more than compensate for any compression overhead. Microsoft's Dynamics GP would appear to be one such schema

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

Just as a note, if you have transparent data encryption enabled, back up compression does not have any effect. I am not sure if Litespeed or any other back up solution can compress it or not.

-Roy

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

From CSS: With TDE, limited Backup compression is achieved.

http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-sql-server-2008-backup-compression-database-compression-and-total-data-encryption.aspx

HowardW One Orange Chip Points: 29109 More actions · Answer 11

Remember native backup compression is only included in SQL Server 2008 Enterprise Edition, so if you have Standard Edition you're out of luck anyway.

2008 R2 will also include backup compression in Standard Edition.

For my 2 cents, SQL Backup/Litespeed are definitely worth the cash - they provide a huge amount of value in ease of backup/recovery automation and are feature rich. If going down the Red gate route, you can get bundles for a few servers/users that include SQL Prompt and SQL Compare (two other indispensable time savers for me) so it's a no-brainer.

P.S. I'm not in any way affiliated with Red-Gate, just a big fan of these particular tools.

Shabba SSCertifiable Points: 5007 More actions · Answer 12

For my 2 cents, SQL Backup/Litespeed are definitely worth the cash - they provide a huge amount of value in ease of backup/recovery automation and are feature rich. If going down the Red gate route, you can get bundles for a few servers/users that include SQL Prompt and SQL Compare (two other indispensable time savers for me) so it's a no-brainer.
P.S. I'm not in any way affiliated with Red-Gate, just a big fan of these particular tools.

Totally agree with you!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

Couldnt agree more! Litespeed or SQLBackup are both great products.

Redgate offer generous discounts too which make the product affordable and it has great features. I use it and recommend it!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

JohnMarks Ten Centuries Points: 1253 More actions · Answer 14

I disagree somewhat with the other posts. In my experience Sql Server 2008 backup compression is on par or better than sql litepeed (when sql litespeed is used with the default parameters). In at least one case sql backup compressed a 180 GB db faster and smaller than lite speed did.