October 8, 2008 at 12:25 pm
I have a database 100 GB, the full backup (litespeed backup to a remote NAS location) takes about 1 hour. The backup file is 30 GB.
Another database is 600 GB on the same SQL server and the backup goes to the same location. The full backup takes over 20 hours, the backup file is 300 GB.
If do the math, this 600 GB database should finish in 6 hours or 10 hours. What could be the problem for the slowness?
Anyone has a similar problem?
October 8, 2008 at 12:30 pm
You've probably hit the tipping point on one of the server's resoruces to where things start getting exponentially worse. You may consider monitoring the box during the backup to see what is getting pegged. Also, with a DB of that size, you may want to consider using filegroups to split the data up and help manage your backups. For examle, you could move all of your in-frequently updated data to a filegroup that only gets backed up once per week. Are you using differential backups now?
October 8, 2008 at 3:05 pm
At my prior job, we used litespeed to backup a 250Gb database to a remote location and it only took 40 minutes. I'd have the network folks look at the wire to see what your capabilites are for throughput. Check the NIC and anything else they can think of (disk maybe??). You've obviously got a bottleneck which appears to be hardware related - IMO.
-- You can't be late until you show up.
October 8, 2008 at 3:10 pm
There are some that say you can get pretty decent speed out of the NAS devices but we struggled with one working with the BIG vendor to get it performing for the exact scenario you describe and never got it to the place where we were comfortable. Additionally if you are doing VERIFY on your backups, and I hope you are, you will see exponentially worsening times, especially if someone else is writing disks that are a part of the volume that you are reading from at the same time.
So, yes the storage is cheaper BUT, it is not worth the time it takes. Hopefully you can find another solution.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 9, 2008 at 11:59 am
Thank you all for your response.
I am planning to have a workaround, like differential backup, or tape backup. The question was this is not a very big database; is it a vendor product limitation or network related, NAS I/O related?
Restore is not a problem, but to a different location/server.
David, may I ask, what did you end up with your backup in your case?
October 9, 2008 at 12:18 pm
I wouldn't go to a NAS. Asking for issues. Any network hiccup and your backup fails. And it will fail the day you need to restore. Disk is cheap. Get enough to back up locally and then copy to the remote device.
October 13, 2008 at 5:36 am
Hi,
the compression rate for the first db is 1:3, for the second db it's 1:2.
Perhaps the problem lies in the data - and litespeed needs a lot of time for the actual compression and not for the transfer. Did you have a look at the cpu resources during the backup of the second db?
Is litespeed set to also encrypt the data?
Is the compression setting for both backups the same?
regards
karl
Best regards
karl
October 14, 2008 at 11:27 am
Karl Klingler (10/13/2008)
Hi,the compression rate for the first db is 1:3, for the second db it's 1:2.
Perhaps the problem lies in the data - and litespeed needs a lot of time for the actual compression and not for the transfer. Did you have a look at the cpu resources during the backup of the second db?
Is litespeed set to also encrypt the data?
Is the compression setting for both backups the same?
regards
karl
I used xp_backup_database @database = 'mydb',
@filename = 'NAS share path' for the backups.
It uses the default value for arguments. Yes, the same backup command for both DBs.
October 15, 2008 at 7:15 am
Vivien,
Sorry for taking so long to reply to this. We did actually tier storage and used a cheaper variation for backups but not as cheap as the NAS solution. We had multiple SAN's, one really high end and the other mid level. We ended using the mid-level for backups with larger disks which provided sufficient speed. Not as fast as the high-end stuff but very sufficient for backups. When we coupled that with compression we were able to get the same speed as we were getting uncompressed to the high-end storage.
I know that not everyone has the ability to have multiple SAN configurations but, my guess is if you were to talk with your storage guy / gal you would be able to get them to find a mid-range alternative configured for primarily write activity that would be more than sufficient for backups.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 15, 2008 at 1:17 pm
Thank you David for sharing. This is a good learning for me.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply