June 9, 2008 at 5:32 am
Hi,
We're about to set up a new SQL 2005 environment and I have a question about backups.
When is it recommended to backup to tape and when is it recommended to backup to disk first (and then take regular filebackup to tape)?
Is full backup good to backup to tape directly? Pros/cons?
How about transaction backup to tape? Pros/cons?
Backup software is Legato Networker.
Is it a good link available to read more about this?
Thanks in advance,
ricfors
June 9, 2008 at 8:01 am
Not-worker??? Auuugh, run..now! 😉
First question(s), are you using advanced file types on the Networker backup server? If so, what are they made of and how many? Do you have the SQL backup module licensed for Networker? Also, what version of Networker (if you want to get to that detail)?
My experience with Networker has been to backup to disk via native SQL and then have Networker backup the files to tape. I've not used the agent backup module myself, I can do a better job with my own code (than what Networker offers for SQL backup)...
Your friendly High-Tech Janitor... 🙂
June 9, 2008 at 8:19 am
Hi Gordon,
Thanks for your reply.
Actually, we're setting this up for a customer. I don't know about the advanced file types since I'm not the one doing the backups.
They don't have the SQL backup module but they will if needed.
Currently, backups are done with native SQL and then Networker backup file backup to tape.
I was wondering if there is any advantage backing up directly to tape than sql natively and then files to tape.
I read somewhere that it wasn't recommended to backup to tape directly (don't remember where, but it was this person's personal opinion), especially transaction logs. Therefore, I wanted to get more input in this issue to have a wider base to make the necessary decision.
Thanks,
ricfors
June 9, 2008 at 8:30 am
ricfors (6/9/2008)
I was wondering if there is any advantage backing up directly to tape than sql natively and then files to tape.
Are you referring to having the SQL server backup directly to mounted tape? "BACKUP DATABASE Foo TO DISK = '\\.\tape0"
If so, there are a couple of things to bear in mind...
The tape has to be local to the SQL server. Which is something that I don't think you want - having to have the Networker server running on the SQL server as well. Just no good. 😉
When SQL server mounts a tape, it places a 'lock' on the tape drive and will not release the lock until the service is stopped (and quite possibly the server restarted...). This means that Networker loses access to how many tape drives SQL server has mounted.
I couldn't tell you about the differences between the Networker agent backup and native SQL server backups. Both offer plenty of flexibility (Networker is only really executing native commands through an API), but using my own custom stored procedures for backup offers an 'ease of use' over the Networker gui or scripting.
Your friendly High-Tech Janitor... 🙂
June 9, 2008 at 8:46 am
Gordon (6/9/2008)
ricfors (6/9/2008)
I was wondering if there is any advantage backing up directly to tape than sql natively and then files to tape.Are you referring to having the SQL server backup directly to mounted tape? "BACKUP DATABASE Foo TO DISK = '\\.\tape0"
Yes, that's right.
If so, there are a couple of things to bear in mind...
The tape has to be local to the SQL server. Which is something that I don't think you want - having to have the Networker server running on the SQL server as well. Just no good. 😉
When SQL server mounts a tape, it places a 'lock' on the tape drive and will not release the lock until the service is stopped (and quite possibly the server restarted...). This means that Networker loses access to how many tape drives SQL server has mounted.
I couldn't tell you about the differences between the Networker agent backup and native SQL server backups. Both offer plenty of flexibility (Networker is only really executing native commands through an API), but using my own custom stored procedures for backup offers an 'ease of use' over the Networker gui or scripting.
So, the Networker agent runs the sql scripts from the API instead of setting up SQL server backuping up to tape? Is that how it works?
Thanks,
ricfors
June 9, 2008 at 9:18 am
ricfors (6/9/2008)
So, the Networker agent runs the sql scripts from the API instead of setting up SQL server backuping up to tape? Is that how it works?
For the most part, yes. As I said, I never tested the SQL agent backup, but I use the Exchange agents and assume it to be similar, yet more flexible due to the number of options offered by native SQL backup versus the number of Exchange backup options...
One more thing to add about backing up to mounted tape on the SQL server directly - performance. Tape drives are significantly slower than spinning disk (in most cases, at least the cost to performance ratio is significantly higher). We're using LTO-4 drives over 2GB FC and I only get about 40MBs on the tape drive. Going directly to (SAN) disk, I can saturate the local HBA to about 200MBs sustained during a backup. 😉
Your friendly High-Tech Janitor... 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply