Best reason to upgrade from 5.2 to 5.3

ChrisAVWood · March 14, 2008 4:31PM

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

petey · March 14, 2008 9:03PM

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.

abroadway · March 14, 2008 10:25PM

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

ChrisAVWood · March 17, 2008 12:56PM

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

petey · March 17, 2008 10:56PM

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.

ChrisAVWood · March 18, 2008 10:49AM

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

Brian Donahue · March 18, 2008 11:33AM

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.

ChrisAVWood · March 19, 2008 9:42AM

We are running SQLBackup on both SQL2000 and SQL2005. Does this matter when using the THREADCOUNT parameter?

Thanks

Chris

Brian Donahue · March 19, 2008 10:39AM

Hi Chris,

I don't know of any reason why multithreaded backups would work differently on SQL 2000 and SQL 2005.

ChrisAVWood · March 19, 2008 10:57AM

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 · April 2, 2008 4:49PM

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

Best reason to upgrade from 5.2 to 5.3

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk