August 17, 2011 at 7:41 am
Our full compressed backup size is about 150gb.
When we backup to locally attached storage it takes about 45 minutes.
When we backup to another server via an UNC path it takes 15 hours.
However when we copy & paste a 150gb file from the locally attached storage to the same server via the same UNC path it takes 30 minutes, so it isn't a network issue.
Has anyone experienced any pain when backing up directly to another server via an UNC path and if so why and what was your solution?
Thanks!
Alex.
August 17, 2011 at 9:14 am
yes I've found network backups are slow ( windows not sql ) I never do network sql backups. The usual reason is network bandwidth - the average didk interface is 3 - 6 GB but typical ethernet is 1gb or slower. I'd not use a network backup other than with 10GBe. My solution is to always backup locally and copy afterwards.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2011 at 9:17 am
Thanks for the reply. This is actually backing up to box in the same data centre which we then copy offsite afterwards.
Also, like I said before, I can perform a file copy using exactly the same endpoints very very quickly.
Alex.
August 17, 2011 at 5:28 pm
Alex Webber (8/17/2011)
Thanks for the reply. This is actually backing up to box in the same data centre which we then copy offsite afterwards.Also, like I said before, I can perform a file copy using exactly the same endpoints very very quickly.
Alex.
I second everything Grumpy said. Backup to DAS and then copy to the network location as a subsequent operation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 17, 2011 at 5:36 pm
opc.three (8/17/2011)
Alex Webber (8/17/2011)
Thanks for the reply. This is actually backing up to box in the same data centre which we then copy offsite afterwards.Also, like I said before, I can perform a file copy using exactly the same endpoints very very quickly.
Alex.
I second everything Grumpy said. Backup to DAS and then copy to the network location as a subsequent operation.
Backup operations across the LAN are very slow. As the other two have said, backup to DAS and then copy after that - it will go much faster.
If not possible, you can mitigate the delay by compressing the backup and specifying that the backup be done to multiple files using multiple threads.
A few reasons not to backup across the LAN -
Any network blip can cause your backup to become corrupt.
A network blip can cause your backup to fail altogether.
Backing up to DAS first gives you an extra layer of protection in the event of disaster.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 1:09 am
Thanks for the replies guys. I totally agree with you all about not backing up over a network, however we don't currently have any other option until we upgrade hardware, all DAS spindles are currently being used.
I missed out another little detail though, before we started using this other box to backup to, we were backing up to another box over the network and it was taking about 1hr which is acceptable. But like I said before I can copy a 150gb file between the same endpoints very quickly.
As regarding backup corruption, as soon as backups are copied offsite they are restored and CHECKDB is run on them so that should mitigate that risk.
Alex.
August 18, 2011 at 4:20 am
I don't really think you can compare a network backup with file copy. as suggested you could try writing to multiple files - I found I could get more performance on sql backups this way - but not tried across a network.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 18, 2011 at 5:04 am
True. What I just don't get is that it's gone from 1 hour when backing up to 1 machine over the network to 15 hours when backing up to another machine over the same network (and in the same switch).
August 18, 2011 at 9:36 am
Alex Webber (8/18/2011)
True. What I just don't get is that it's gone from 1 hour when backing up to 1 machine over the network to 15 hours when backing up to another machine over the same network (and in the same switch).
Have you by chance tried different ports on that switch? Even though on the same switch, is it the same vlan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 9:43 am
Yeah we're using the same ports and the same VLAN as before.
August 18, 2011 at 9:45 am
I can see more as to why it is puzzling.
Have you checked all the firewall and nic settings on the destination server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 9:54 am
Alex Webber (8/18/2011)
True. What I just don't get is that it's gone from 1 hour when backing up to 1 machine over the network to 15 hours when backing up to another machine over the same network (and in the same switch).
Sorry if I missed it in the churn, but how long does it take to do a simple file copy (any file of non-trivial size) from the database server to the 1hr-server, and how long for the same file to the 15hr-server? Just thinking on whether it could be isolated to server h/w and/or network and take SQL Server software out of the equation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 18, 2011 at 10:06 am
NIC settings and firewall are the same.
So a single simple file copy of 150gb takes under 30 minutes on the 1hr server and the same on the 15hr server.
August 18, 2011 at 1:54 pm
Please keep in mind that when you are copying the file over to your network share, the user is you who is probably a Admin on that box. When the back up is done over to the Server, the Service account might not have the admin rights.
I am not sure if Instant File initialization has to be set up for the back up server as well.
other than that I do nto see anything that could create this situation. Just throwing in an idea.
-Roy
August 18, 2011 at 1:57 pm
How could user permissions have anything to do with it? Surely you either have the right permissions or you don't?
And as for Instant File Initialisation, that's only for creating new data files (skipping the zero'ing phrase) so how does that come in to play?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply