SQL 2008 Compressed Backup is amazing!!!

jwilderjwilder Member Posts: 263
edited 2009-05-30 in SQL General
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?
Jason Wilder
[email protected]


  • ara3nara3n Member Posts: 9,253
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer

    blog: https://dynamicsuser.net/nav/b/ara3n
  • strykstryk Member Posts: 645

    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/

    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 ...

    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • davmac1davmac1 Member Posts: 1,282
    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.
  • ara3nara3n Member Posts: 9,253
    I just want to add that MS just released best practices on compression for sql 2008. You can read it here.

    Ahmed Rashed Amini
    Independent Consultant/Developer

    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.