March 26, 2009 at 10:34 am
We migrated from standalone servers running SQL2000 to VMWare & SAN, running SQL 2005. Our Large DB used to take 16 hours to backup, and is now taking 3 days !!!! Is there anything SQL related that could possibly affect the time to run a backup ?? I've tried pointing the backup location to the SAN and to a standalone server on the same rack & both are 3 days. The backup starts out fast at about 1 gig per minute but then just gets slower & slower. I'm assuming the problem is some configuration in VMWare, or the SAN or NICs ..... but if there's anything within SQL that I can look I'd like suggestions.
TIA
March 26, 2009 at 12:43 pm
Does it run faster if you stripe to multiple files?
Backing up to a compressed volume/folder?
Did the OS Change?
March 26, 2009 at 1:05 pm
I haven't tried multiple files. The old OS was Win Server 2003 Standard Edition SP2, and the new OS is Win Server 2003 Enterprise Edition SP2.
We're using Redgate SQL Backup before & after.
Before the upgrade, I backed up to a standalone server (16 hours), removed the drives, installed the drives in another standalone server at the new location to restore. Now backing up to that same standalone server takes 3+ days, as does backing up to the SAN.
March 26, 2009 at 1:15 pm
Wow that is a really long time to do a SQL backup (at least in my experience). How big is the DB if you don't mind my asking?
Some things to consider, when you were on the stand alone server, how many disk drives were you writing the backups to and how many other servers shared those drives? Now that you are on the VM how many drives are you writing to and are they being shared (my experience with VM attached to SAN is that you typically are at the mercy of many other things utilizing that same disk IO). Jumping from 16 hours to 72 is drastic, but if you have 1/3 of the IO available to you to write out those backups to disk it would make perfect sense.
March 26, 2009 at 1:24 pm
The database is about 850 Gig. Previously I was writing to a Buffalo terraserver attached drive, and then the standalone server a few times. Backup times were comparable and they only had 1 or 2 drives. The SAN has 16 drives but is shared by various other servers in different LUNs. My smaller databases ( less than 100Gig) seem to backup in a normal amount of time. It's just the big one that bogs down after it's written about 200 gig.
March 27, 2009 at 9:10 am
That is really strange. AFAIK, the SQL backup process just throws pages out as quick as possible. No reason inside SQL to have it run slower.
I would lean towards the issues with VMWare, possibly the way it's set up to use IO. IO is a big deal with virtualization, and I'd call them and get support from them.
Are you sure that proper resources are given to the SQL VM?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 27, 2009 at 9:20 am
Wow! I would agree with Steve and look at the VMWare setup. You can have IO issues with virtualized setups, and that would be the first place I would look. Also,
16hrs seems really long for a db that size. I get my 700 GB db backed up with Litespeed in less than an hour, so you may want to re-evaluate your tool set as well.
March 27, 2009 at 12:57 pm
Insure that your SCSI controller in VMWare is set to LSI Logic. I have had a problem where the SCSI controllers on one VM somehow changed to the "other" contoller type and performance was down the drain...
I also use diskpart and the align=64 parm when creating the disk partition. I then format using NTFS and an 8192 allocation size to get the best performance from my SAN/LUN's. You need to test your SAN to get the best performance.
Hope this helps...
March 27, 2009 at 1:03 pm
Even 16 hours is way too long. I had a 3TB database that I used to administer that took significantly less than that to backup - and that using SQL Server native backup.
The one time I had a problem with backup duration was when I tried to backup to a networked drive. Now that was a nightmare.
March 27, 2009 at 2:55 pm
Agree with Meredith, My 776GB db nightly backup takes just over an hour with Litespeed, would recommend using that.
March 27, 2009 at 3:28 pm
SQLRocker (3/27/2009)
Agree with Meredith, My 776GB db nightly backup takes just over an hour with Litespeed, would recommend using that.
How is your backup drive configured on the SAN? 775GB in an hour is really impressive compared to what we see. Does Litespeed really help backup times that much?
March 27, 2009 at 3:32 pm
justin harper (3/27/2009)
SQLRocker (3/27/2009)
Agree with Meredith, My 776GB db nightly backup takes just over an hour with Litespeed, would recommend using that.How is your backup drive configured on the SAN? 775GB in an hour is really impressive compared to what we see. Does Litespeed really help backup times that much?
I can't speak to how other folks get litespeed to work, but I know it really does help my backup times that much. My largest DB is just under 700GB and my average full backup time to SAN disk is 68 minutes. That is running on a dual quad core proc, 32gb ram Egenera pblade connected to a 3Par san.
March 31, 2009 at 11:38 am
With SQL Backup, I would recommend changing the MAXDATABLOCK parameter to a lower value. By default it's about 4MB, and this can cause trouble for SANs and network backups. Changing this value to 524288 usually works a lot better for networks and SANs.
exec master..sqlbackup '-SQL "BACKUP DATABASE [x] TO DISK=... WITH MAXDATABLOCK=524288..."'
I hope this helps.
Regards,
Brian Donahue
Red Gate Technical Support
March 31, 2009 at 12:03 pm
FWIW, our database has lots of compressed sound recordin stored in IMAGE data type fields. Not sure if that is a factor.
April 2, 2009 at 6:31 am
In that case, the bottleneck may not be network latency, it may be the processor. Aside from increasing the number of threads, which I think has already been mentioned, you may want to set the compression level to 1 or even 0 in the backup job.
Trying to recompress data that has already been compressed results in poor performance with a really marginal reduction in the backup size (<10%). The next version of SQL Backup is planning to have a compressed data detection feature that will automatically leave BLOB data alone if it's determined to have a compression algorithm already applied to it.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply