Performance of backup across network
From: Scott Nichol (reply_to_newsgroup_at_scottnichol.com)
Date: 07/12/04
- Next message: Quentin Ran: "Repost: Databases with different collation"
- Previous message: Serverdba: "UPDATE STATISTICS"
- Next in thread: Greg Linwood: "Re: Performance of backup across network"
- Reply: Greg Linwood: "Re: Performance of backup across network"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 12 Jul 2004 10:10:36 -0400
I am hoping someone with experience can help me with this.
I have been doing differential and log backups across a network to a device
specified by a UNC path for some time. The backup sizes have been small
enough that performance was no issue. Full backups, which total about 250
GB, I have been doing to a RAID set on the local machine, then doing a copy
(literally the command line copy from a batch script) to the same UNC path
as the diff and logs. The backup itself takes about 2.5 hours, and the copy
another 4 hours. The network is dedicated gigabit. The target machine uses
RAID 10 and can do local I/O at >= 60 MB/s. The network can definitely
sustain 30 MB/s with an app that has a clue about the network. xcopy can
sometimes hit that pace, but it and copy more typically do 17 MB/s.
Because I want to use the disk space on the SQL Server box for something
other than backups, I now want to do full backups directly across the
network, using the UNC path as for diff and log. My problem is that this is
dog slow (no insult to dogs, most of which run much faster than I,
intended). I am getting about 7 MB/s, which means the full backup is about
10 hours! Considering that the performance from copy (17 MB/s) is hardly
exemplary, I find SQL Server performance pretty embarassing.
The only useful tip I found on the net was to backup to multiple devices.
When I do this, my network throughput actually *drops* to about 6.5 MB/s
(for 2, 3 or 4 devices). The same tip suggested that multiple devices might
not help if max worker threads is not bumped up. I have not had a chance to
restart SQL Server (this is a 24x7 public database), but we typically have
100-120 connections, so I am not sure that bumping this up will do anything,
anyway.
Does anyone have experience with network backups and is getting better
throughput? I have considered forcing opportunistic locking in the
redirector, but this seems like blind hope.
Do I need to use something with a clue about networks, like the Veritas SQL
Agent? We use Veritas to spin our tapes, but we abandoned SQL Agent 2 years
ago when we could not get it to work.
Help with backing up directly over the network would be much appreciated.
Comments from SQL Agent users are also welcome.
TIA
-- Scott Nichol
- Next message: Quentin Ran: "Repost: Databases with different collation"
- Previous message: Serverdba: "UPDATE STATISTICS"
- Next in thread: Greg Linwood: "Re: Performance of backup across network"
- Reply: Greg Linwood: "Re: Performance of backup across network"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
