• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

SQL backup speed - tape vs disk

should backup to disk always be faster than to tape- or at time, could tape be faster, if the disk is not high config? in a db environment.

thanks
0
anushahanna
Asked:
anushahanna
  • 13
  • 6
  • 4
  • +5
18 Solutions
 
Aaron ShiloCommented:
hi
as a general rule the HDD will perform faster than the tape, and you should use it at the top of your backup pyramid
0
 
Tomas ValentaCommented:
It depend on your HW environment. Normally is not recommended backup to local disk drive only.
You can backup to Tape drive locally (LTO3 drive - backup speed approx. 1 100MB/s) and the second
backup should be done to USB external HDD (backup speed of SQL db is approx 850MB/s).
Another point of your backup should be disk array connected directly to server (Fiber, SAS). This
is faster then external USB drive but cannot be moved outside if you would like to have a copy of your data
outside building. The last is backup to NAS (Network attached storage) - here speed may vary based on network connection
- often is used separate network segment for improving backup speed. The backup speeds I mentioned were from our real system.
0
 
andyalderSaggar makers bottom knockerCommented:
You're not likely to find a disk that you can write to at 140MB/s native - 280MB/s compressed native speed, which is what you can get with an LTO5. The things are so fast you need to have an array of disks just to keep up with them.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Eugene ZCommented:
also it depends where is your tape device located (network or attached..)and  you are running backup (local or network HDD) , databases- backup size, etc... what software and from where you are using to backup...

---
to make sure - you need to try to backup and see what is faster: it is up to your environment setupt,  configuration, business, etc
0
 
SelfGovernCommented:
It's a common misconception that "Tape is slow".   As Andy points out, for at least several generations of LTO, it's been the source disk that has trouble keeping up with tape!   I don't know where Tominov gets his numbers from -- they don't match to any reality I've ever seen -- but the real performance numbers for tape are:
Drive type             Native Speed
LTO-3                   80 MB/second
LTO-4                  120MB/second
LTO-5                  140MB/second

In the real world, it takes a fast array of disks and a fast RAID controller to be able to get that much data to a tape drive over long periods... and if your data is compressible, multiply the numbers above by the compression you achieve to get the even higher performance tape will achieve (that is, if your data compresses at 2:1, LTO-4 will hit 240MB/sec.   At 1.5:1, it will hit 180MB/sec -- **if** your disks can get it the data that fast!).

That compression -- the ability to fit significantly more data on a tape than its native capacity, and with no performance overhead on the backup server -- is a real benefit.   Other benefits in LTO-4 and LTO-5 tape drives are hardware encryption, which means that your data is secure even if the tape is lost, and WORM capability, ensuring that data can't be changed or overwritten once it's on the tape.

Sure, you can do short bursts from cache from most hard drives and see better numbers.   You can run benchmarks that access the disk by sector and not by file and get great numbers -- but that's not how backups are commonly done, because then file or object restores take too long.

The reason that big companies are spending tens and hundreds of thousands of dollars on disk-to-disk appliances (aka virtual tape libraries or VTL) is not because a particular server gets backed up faster compared to tape -- it's typically because:
1) The server can't keep a single tape drive used to maximum capacity -- the server isn't fast enough
2) Many more backups can be run to a VTL simultaneously, meaning that the overall backup window shrinks.
3) Some causes of tape backup failure are eliminated, such as dirty tape heads, wrong media, etc.
4) Backup to disk allows for faster single-file restores
5) Backup to disk with deduplication allows for easy low-bandwidth replication of backup jobs to a remote site, such as a data center

As a side note -- I also get worried when people recommend you backup to a USB drive.   Not only will performance be less than you expect (the cheap consumer-quality drives in these enclosures just aren't up to the task), but if you're counting on that backup being available in a year or three, you may well be very disappointed; disks are not designed for archive.   They have to be powered on to keep the data refreshed.   Long periods of sitting unused on a shelf will lead to increasing failures over time.     Tape, on the other hand, is known to be a good archive media for twenty or thirty years.

Yes, tape is being used less for the day-to-day backups in many businesses.   But it's still the best for archive, and the price/performance beats disk by an order of magnitude.
0
 
Tomas ValentaCommented:
The data is from our BackupExec SW. Backup of MS SQL DB of 200GB size takes 140minutes. Backup was done to the local LTO3 drive.
0
 
anushahannaAuthor Commented:
Andy
>>You're not likely to find a disk that you can write to at 140MB/s native - 280MB/s compressed native speed, which is what you can get with an LTO5.

are you suggesting LTO5 over disk, and that is can handle upto 280 MB/sec?
0
 
anushahannaAuthor Commented:
SelfGovern
could you comment on how these folks hit a lot of MB/sec
http://tinyurl.com/348bmte

Am I understanding you right that LTO-5 or 4 can be a better option than to disk, but not LTO-3?
0
 
anushahannaAuthor Commented:
in the link search for '65'
0
 
anushahannaAuthor Commented:
Tominov, 200 GB in 140 minutes is 24.38 MB/sec?
0
 
andyalderSaggar makers bottom knockerCommented:
Yup, LTO5 is faster than disk. 280MB/s is what you might get if the data is compressible but you'lll get at least 140MB/s as long as you can read from SQL that fast.
0
 
anushahannaAuthor Commented:
if i am getting 6MB/sec for min and 56 MB/sec for the tape backups right with SQL Server, does that indicate the backupserver had LTO3?
0
 
anushahannaAuthor Commented:
56 MB/sec max
0
 
andyalderSaggar makers bottom knockerCommented:
LTO3 native 80MB/s so the figure of 56MB is reasonable, the limit's most likely how fast you can red from your SQL server.

HP's specs - speeds are at the bottom...
http://h18000.www1.hp.com/products/quickspecs/13572_div/13572_div.html#Technical%20Specifications
0
 
kevinhsiehCommented:
Disk can be faster than tape if you can't feed your tape drive fast enough. That sounds backwards, but tape can only write fast and faster. Disk can write as slow as you need, so it will go the speed that you require until it maxes out. With tape, if you don't feed it fast enough it has to start and stop while it waits for more data. It is the stopping and starting that kills the throughput. If your current environment can't keep LTO3 happy and full with data, LTO4 would probably perform worse because the speed mismatch between the backup source and the backup tape drive just got bigger.
0
 
SelfGovernCommented:
Kevin, your information is out of date, at least for HP LTO drives.   I've seen data fed to them from 1MB/sec all the way up to full native speed, and the throughput was the same as the feed speed.   It used to be the case with DLT tapes that not feeding them fast enough actually had a performance penalty.

Now, there is an issue if you feed a drive so slowly that you get significant buffer underrun incidents.  In this case, though, the problem won't be speed, it will be early tape drive wear (from all the stop/rewind/restart).

To the OP: Something you have to remember is that each backup system has only one bottleneck.  This is the thing that slows you down.   If the bottleneck is "A", then getting a faster "B" won't change the throughput, because A is the bottleneck, and the bottleneck is what limits your performance.  If you fix A, then B or C or D might become the new bottleneck... but until you fix A, fixing the others gains you no performance benefit.

That presentation you linked to explains common performance problems and tells you what to do about them.    It's got very good information, so study it and see what you can learn.  I can't put everything I know here, because it's taken me 11 years in data protection to know what I know.  That said, the linked-to presentation does tell you a lot, and I've summarized some of the common fixes below.

If you have LTO-3 today and are seeing 56MB/second, then the tape drive is not your bottleneck, because that drive can go at least 80MB/sec.   Unless Windows Perfmon shows you that you're memory or processor constrained, your current disk is probably your bottleneck.  To fix it, you can get faster disks (15K vs, 10K), better RAID striping, more disks in your RAID system, better RAID controller, split your data over multiple RAID system (or use something like an HP EVA that does broad striping), perhaps a newer version of the database program, reduce contention when the backup is running, or tune the backup process in your backup/DB system -- among others.    I can't tell you from here which is the best for you; people who analyze these things need access to your system, and probably charge a pretty penny -- but you've got some clues to help get you started thinking in the right direction.    
0
 
andyalderSaggar makers bottom knockerCommented:
The data rate matching values are in the same place as the max speed in the queckspecs I posted earlier.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Latest LTO readers are very fast but all has to do with the restore target time you want as part of your recovery plan.  

Tape media adds an additional step to transfer first from tape to disk to allow final restore, while disk backup is already there to be restored.  Depending on database size the total restore time may be very long when having to do both.  Personally, I keep one week disk retention on disk and 8 weeks on tape.  

There are great benefits from that first being that I don't need to call the tape team everytime I need to restore, copy or move a database.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
In other words, I believe that the speed of media is not be dictating your recovery plan.  If you have no more than 1 hour to bring back a big database online, I guarantee you that you will have better chances on meeting that time constraint by restoring data that is already on disk at time of crash.
0
 
anushahannaAuthor Commented:
SelfGovern, thanks for the recommendation to check the disk config.

In the tinyurl, on page 17, the achieved 650 + MB/Sec.. how is this possible (regardless if disk or tape type 5)?
0
 
anushahannaAuthor Commented:
andy, is "data rate matching" like the min and max (like mine was 6-56)
0
 
anushahannaAuthor Commented:
Racimo, SQL Legato Network is able to restore directly from Tape to SQL, as we have it now; is that impressive?
0
 
andyalderSaggar makers bottom knockerCommented:
Data rate matching for LTO is a form of maximum / minimum, the drive motor speeds up and slows down in an attempt to keep the tape streaming and avoid the stop/start "shoe-shining" that Kevin refers to above.

The article you posted in tunyurl is all about SQL and says nothing about what they were backing up to, they may well have been backing up to a null device just to prove how fast the backup could be done. Quite possibly they had an array with a lot of hard disks in it. A tape is faster than a disk but it's not faster than an array of 20 disks.
0
 
SelfGovernCommented:
> In the tinyurl, on page 17, the achieved 650 + MB/Sec.. how is this possible
>  (regardless if disk or tape type 5)?

Andy's got it right, above.   The target would have either had to be a null device (i.e., not actually written anywhere, in order to benchmark the pure backup limit), or, a big VTL like an HP VLS9000 with multiple simultaneous targets, or, several LTO tape drives, each doing an independent piece of the backup.

The adaptive write speed, or data rate matching, of LTO is the minimum and maximum incoming data rates that the tape drive motor can handle without stopping and starting.  With LTO drives, it's typically 1/3 of the full write speed*.   So with uncompressible data (or compression turned off) a 120MB/sec LTO-4 drive will be able to handle incoming streams as slow as 40MB/second without emptying the buffer and having to stop, rewind, start writing again.     If your data is 1.5:1 compressible, that number would be 60MB/second.    Earlier tape technology (DLT, DAT) did not have this feature; if you couldn't feed them at their rated speed, they for sure would empty the buffer, and that's not a good thing.

* Some algorithms are better than others.   HP's is constantly variable for best real-world use; IBM's at least used to be steps, and only adjust at end-of-track, which meant you could have some really bad performance for some data streams.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Racimo, SQL Legato Network is able to restore directly from Tape to SQL, as we have it now; is that impressive?>>
I am sorry but you are completely missing the point of my comments.  Requirements such as target restore time should determine what tools, approach is to be used to meet those requirementd, not the way around.   Recovery plan involve people, calendars, human error, processes, not just tapes and sofware.

Until you determine the best mix of each, beginning your thought as a contest between tape vs disk medias, or as a contest to what iis the best technology, simply has little value in determining any integrated recovery plan solution.

IMHO - Hope this helps...
0
 
anushahannaAuthor Commented:
very much appreciate your input. i wish MS mentioned what target they used.

with the above logic in mind, with tape, I get max of 50-60 MB/sec in tape, but with Disk (SAN Lun), 225 MB/sec.

0
 
anushahannaAuthor Commented:
Racimo, after you set your target restore time, depending on what technology is faster/better, you will adapt to the better technology in that environment- is that what you are saying?
0
 
andyalderSaggar makers bottom knockerCommented:
Your SAN based LUN isn't one disk, it's an array of them. That's why it's faster than your tape.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<is that what you are saying?>>
What I am saying is meeting the target restore time the end while the combination of hardware, software technology and process to be used.remains a mean.
0
 
anushahannaAuthor Commented:
>>Your SAN based LUN isn't one disk, it's an array of them. That's why it's faster than your tape.

Is it because there are many spindles to do parallel writes? in that case, if, theoretically, if unlimited number of disks should be used in a LUN, we could have GB/s worth of throughput on disk possible? (like in the example from the tinyurl, 650+ MB/sec was possible; is there a limitation that you could'nt go beyond 650 MB/sec - what is the highest throughput you have seen with (array of) disk?)
0
 
anushahannaAuthor Commented:
Racimo, thanks- I see your thoughts more clearly now..

have you seen any case study paper written on the concept of starting with target restore time, so i can follow it with a real example of how it was deduced (the means)

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 6
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now