Best reason to upgrade from 5.2 to 5.3

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

  petey March 14, 2008 21:03

  - 0 +
• 
  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

  abroadway March 14, 2008 22:25

  - 0 +
• 
  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

  ChrisAVWood March 17, 2008 12:56

  - 0 +
• 
  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.

  petey March 17, 2008 22:56

  - 0 +
• 
  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

  ChrisAVWood March 18, 2008 10:49

  - 0 +
• 
  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.

  Brian Donahue March 18, 2008 11:33

  - 0 +
• 
  We are running SQLBackup on both SQL2000 and SQL2005. Does this matter when using the THREADCOUNT parameter?
  
  Thanks
  
  Chris

  ChrisAVWood March 19, 2008 09:42

  - 0 +
• 
  Hi Chris,
  
  I don't know of any reason why multithreaded backups would work differently on SQL 2000 and SQL 2005.

  Brian Donahue March 19, 2008 10:39

  - 0 +
• 
  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

  ChrisAVWood March 19, 2008 10:57

  - 0 +
• 
  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

  ChrisAVWood April 02, 2008 16:49

  - 0 +

Add comment

Please sign in to leave a comment.