Best reason to upgrade from 5.2 to 5.3

ChrisAVWoodChrisAVWood Posts: 361
edited April 2, 2008 4:49PM in SQL Backup Previous Versions
Hi,

We are running 5.2 currently and would like an idea of what the most compelling reason to upgrade to 5.3 is likely to be from the fairly long list of changes/improvements?

Thanks

Chris
English DBA living in CANADA

Comments

  • peteypetey Posts: 2,358 New member
    If you are using the multiple thread single file option (THREADCOUNT) for backups, there is a very rare bug that causes your backup files to be incomplete. In most cases, the backup file can still be restored by first converting it to native SQL Server backup format.

    There is also a bug that cause restores to fail, again when the backup file was created using the THREADCOUNT option. The workaround is to first convert the file to native SQL Server backup format, and restore.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter and Chris.

    I concurr with Peter.
    (It seems after reading this, its what was causing our problems.)

    We're using Multiple threads with the backup option. Since the upgrade and subsequent file copy - all restore tests were good.
    (And here I was thinking I had to replace my RAID array due to dodgy disks!)

    Chris - DO the upgrade. For us it was painless and took only minutes. And we are VERY anal about upgrades on production servers!

    Adam :)
  • Thanks guy's for the answer. We are not using multiple threads at this time. Maybe one of you can give me some answers on using mutilpe threads for backups? How much extra memory or CPU does this option add and normally how much faster does it make the backup run?

    Thanks

    Chris
    English DBA living in CANADA
  • peteypetey Posts: 2,358 New member
    As usual, the asnwer is 'it depends'.

    SQL Backup has 2 modes for using multiple threads:

    - each backup thread writes to a different file
    This is identical to native SQL Server backups that write to multiple files, where you take advantage of multiple processors and multiple disk devices available on your system. See the following for references:

    Ref 1
    Ref 2
    Ref 3

    - backup uses multiple threads, but writes to a single file (THREADCOUNT options)
    This takes advantage of multiple processors on your system, but not multiple disk devices.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • At this time THREADCOUNT sounds like what I might want to try. Does it increase the CPU needed to run SQLBackup and what is the best option for an 8 CPU server with hyperthreading?

    Thanks

    Chris
    English DBA living in CANADA
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Chris,

    Multiple threads increase performance by spreading the load across multiple CPUs. The recommendation is to back up using (number of CPUs)-1 threads for maximum performance. I'm personally unsure what to recommend with hyperthreading -- whether to use 7 threads or 15 -- but with multi-core you would consider each core as an independent CPU.

    Note that the absolute maximum throughput is attained by backing up to multiple files, because backing up multiple threads to the same one file incurs a bit of overhead for the thread synchronizer needed to ensure that two or more threads do not attempt to write to the backup file at the same time.
  • We are running SQLBackup on both SQL2000 and SQL2005. Does this matter when using the THREADCOUNT parameter?

    Thanks

    Chris
    English DBA living in CANADA
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Chris,

    I don't know of any reason why multithreaded backups would work differently on SQL 2000 and SQL 2005.
  • Great. I will give it a try. I am supposing that it will take less time to run than running without the parameter?

    Thanks

    Chris
    English DBA living in CANADA
  • I have noticed a problem in 5.3 when you are running an SQL Agent SQLBackup job that has a VERIFY parameter. It sticks under the In Progress tab even when it has finished.

    See Completed Backup stuck in "In Progress" queue thread.

    Chris
    English DBA living in CANADA
Sign In or Register to comment.