SQL 2008 Compressed Backup is amazing!!!

jwilder

We are moving from SQL 2005 Enterprise x64 to SQL 2008 Enterprise X64 next week. Our Navision version is NAV 2009 executables & NAV 5.0 codebase.

I have done some testing in regards to back ups and restores and feel I must share the results.

We have a 111 gb database. A normal backup takes 25 minutes (and the size is 111 gb). Using the new SQL 2008 Compressed backup it not only reduces the backup time to 8 minutes but the backup file ends up being 20 gb!

Even crazier is that when I restore a compressed backup it takes only 5 minutes!!!

Does anyone know of any reason why I shouldn't upgrade to SQL 2008?

ara3n

I don't see any reasons. I suggest to also read this blog on compressing actual tables.

http://mibuso.com/blogs/ara3n/2009/02/1 ... -database/



You can also create filtered indexes on sql, which will make the db even smaller.

stryk

Hi!

Well, Enterprise Edition of SQL 2008 is not realy cheap, and that for good reason ... its features refarding "Performance" are fantastic! If you like "Backup Compression" you'll love "Data Compression" 8)
But thre are alos lots of other smart features like "Resource Gouvenor" etc..

So if you could afford SQL 2008 EE then GO For IT \:D/

But:
Rashed describes in his BLOG how to compress all Tables and all Indexes - I cannot agree with this proceeding.
Compression should be only used on tables where the records are not changed much afterwards!
Applying Compression on heavily used (read & write) tables like "Sales Line" etc. could actualy decrease performance. Intensive usage of Compression will put pressure on the CPU - to calculate the compression and de-compression - but when data is changed, this could get worse, also resulting in physical I/O.
Ideal candidates for Compression are all those large "Ledger Entry" tables in NAV (G/L Entry, VAT Entry, etc.) - they are usually just written once (or maybe updated twice) and that's it.
In any case, you should only compress the tables (= clustered index) and be careful with indexes (= non-clustered indexes): if a record is written to, this change might result in an index update, too. Hence, the SQL Server must de-compress the relevant index pages, change the data, then compress again. The more pages are affected, the more could be the negative impact on performance.
(Sames goes for table changes (= clustered index)).
To avoid this kind of problem you actually need to partition the table/index, and apply the compression only on the "inactive" partition, so to speak.
So I recommend to start failsafe with large "Ledger Entry" tables (not the indexes) and then just try out more - always having an eye on the CPU load and the physical I/O ...

Regards,
Jörg

davmac1

Vardecimal would greatly reduce sizes as well. Unfortunately, it is only supported in 2005 and 2008 Enterprise editions. Unless they add it to Standard, we may never see it in NAV....

As far as backup compression, many production shops use third party SQL Server backup (like Redgate) that provides compression as well as other management tools. Much cheaper than buying Enterprise if you don't need the other features.

ara3n

I just want to add that MS just released best practices on compression for sql 2008. You can read it here.

http://msdn.microsoft.com/en-us/library/dd894051.aspx