Post reply

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

  • May 5, 2008 at 8:02 am

    #810693

    Great post!

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

    KU

  • May 5, 2008 at 8:15 am

    #810706

    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

  • May 5, 2008 at 8:17 am

    #810709

    I think this is an Enterprise feature only

  • May 5, 2008 at 9:26 am

    #810772

    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

  • May 5, 2008 at 9:42 am

    #810784

    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

  • May 5, 2008 at 9:51 am

    #810785

    Nicely done article. However, we can't assume linear performance on larger databases - 100GB or 10 TB. (Is 100 GB still considered large? I remember considering 30 MB huge, but that's a long time ago).

    I've got the 100GB databases, but no time to install 2008 and report back, I'm afraid.

    Certainly, we've always got to test these days - so many old rules of thumb about relative speed of the parts of the IO chain are different than once upon a time. I've tended to avoid compression: I can write to a good iSCSI array faster than the CPU can do the compression. But compression keeps improving as well.

    Thanks

    Roger L Reid

  • May 5, 2008 at 10:06 am

    #810792

    Hi

    I have a 109GB, 110GB, 111GB.. Database that I am in the process of testing with and I will be doing a follow-up article - possibly in more technical detail.

    I will post to this forum and possibly in a follow-up article on the findings

    Thanks

    Kevin

  • May 5, 2008 at 10:30 am

    #810799

    It would be interesting to compare the backup restore vs. the current backup champ Redgate SQL Backup........

    Also, as the forum sponsor, any word on when Redgate will have a 2008 product?

    Rick Sheeley, BannerAMC

  • May 5, 2008 at 10:36 am

    #810804

    This is an Enterprise only feature for 2008. That could change in the future.

    Restore times should be much quicker, similar to backup time savings. Less reading from disk (slow) and quick decompression in memory.

    Red Gate has committed to 2008 support when 2008 ships. The products are being tested against 2008 versions now, but the 2008 code is not feature complete nor completed, so the products aren't necessarily 2008 compliant now.

  • May 5, 2008 at 10:49 am

    #810805

    Glad to hear it (about the bigger tests). Of course, different setups of IO, CPU, concurrent work load etc are issue too, but you're putting a down a good base to guide the rest of us in our own benchmarks.

    I'm going to assume that All Is Scriptable. I expect this simply adds a WITH COMPRESSION = XXXX to the SQL and/or a property to the SMO.

    Scriptability and the "SCRIPT THIS" button (for when you can't seem to get the 47 settings of a restore correct) is only thing that's saved my sanity (and my wrist) - being able to write my backup scripts in Perl/DBI and run them out of a Unix cron job.

    I don't know how anyone handles, say, 140 replicated databases plus development copies across 26 instances across 4 slabs of metal clicking guis and logs.

    Roger L Reid

  • May 5, 2008 at 10:53 am

    #810806

    Egad - where are my manners! I forget for a moment that redgate sponsors this excellent forum, and here I'm talking about SQL Server backup from Unix with Perl.

    I don't think they have to worry about a stampede in that direction. It's just my background showing. As usual, there's More Than One Way to Do It, and I know redgate does it for a lot of people.

    Roger L Reid

  • May 5, 2008 at 11:12 am

    #810813

    I took backup with both compress backup and do not compress backup. I was able to restore database with backup (do not compress backup) with out any issue. But, the one I took with compress backup option, I am not able to restore it. Is there any other parameter need to be given for this restore.

    Error I get is invalid backupset. Though I took backup thrice and all failed. Had any one got this issue?

    -- one more thing, I am using backup and restore with wizards and not commands.

    SQL DBA.

  • May 5, 2008 at 11:22 am

    #810817

    I will test this for you - when back at the office and give you feedback

    Thanks

    Kevin

  • May 5, 2008 at 1:40 pm

    #810896

    I am very interested to see the restore metrics. What OS are you based on ?

  • May 5, 2008 at 2:28 pm

    #810922

    I'd be interested in knowing if there is a difference in SQL 2008's compression compared to compression from SQL Litespeed.

Viewing 15 posts - 16 through 30 (of 54 total)

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