Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Slow SQL backup across network

Posted on 2014-01-07
4
Medium Priority
?
605 Views
Last Modified: 2014-02-24
We currently have a SQL (2008) job that creates a backup of a 200Gb database and uses another networked server as a target. Both servers are on a Gig backbone. From there the file is backed up daily to tape.

The time taken to create the SQL backup on the target server is becoming excessive, ranging from 9 hours upward.

Backing up the file from the target server to tape (Netbackup) is magnitudes faster and I'm left wondering if we are going about things the wrong way. I don't believe this is a network issue and have monitored both servers/network for bottlenecks.

Questions;
1) This backup process is a hold-over from an old, non-SQL based system which was recently replaced. What is best practice/the fastest way to back up large SQL databases? Would we be better off installing a Netbackup SQL agent on the source server (and if so why)?

2) What sort of transfer rates are others seeing when creating a SQL backup across the network in such a fashion?

We're pulling our hair out trying to prove/disprove theories at this point so guidelines from anyone with experience in this field would be welcomed.

Thanks!
0
Comment
Question by:agradmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 1500 total points
ID: 39763311
With things as touchy/crucial as backups, I generally like to backup my databases to a local backup LUN on the SQL Server and then copy the completed backup from the local LUN to our backup server.  Reason for this is you eliminate the network overhead from your backups as well as the chances for corruption.  It also gives you a clearly defined source of the overhead, whereas if you're backing up to a network location: is SQL Server bottlenecking you, or is it the source disk, or is it the source cpu, or is it the destination disk ?

Edit:
As for the other part of your question.  I always like to have native SQL Server backups for something so crucial to everything.  Native compression has come a loooong way for speeding up the backup/recovery times. (Our 1.2 TB database takes about an hour and 15 minutes for a full backup)
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39766290
Also are you trying to do a verify as part of the process?

If you are that takes a significant amount of time because it is basically unpacking the backup.

I agree with Padawan -- see if you can do a local backup and copy the resulting file over.

If you have to do it to the UNC then skip the verify. You can always setup a SQL instance on the other server just to do a verify. Not used for any other purpose it shouldn't need a license.

I still have not found a SQL backup agent that I trust.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39770077
In addition to backing up to a local drive, if you have an edition that supports it consider using compression.  If you don't consider purchasing a third party tool such as Red-Gate's SQL Backup
0
 

Author Closing Comment

by:agradmin
ID: 39884156
Thanks for the help. We are still trying to speed things up but the information will help us make decisions.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
"Any files you do not have backed up in at least two [other] places are files you do not care about."
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question