Veritas Backup
-
Our organization has been advised to use Veritas to back up our SQL Server databases. For the past several years I've heard that this is problematic and database corruption is common with this approach.
Most of the people I have talked to over the years have used the native SQL Server tools to backup the database, then used Veritas (or something similar) to move the backup files to tape.
Does anyone have recent experience with SQL Server database backups using Veritas?
Happy/unhappy with the results? Why?
Thanks in advance.
-
All I can tell you is that I spent the better part of 3 months trying to get it to work (this was NetBackup 4 for Windows) and we eventually decided it wasn't worth the hassle.
One thing I do remember is that NetBackup's SQL client simply inserts itself as a "shim" in the SQL backup subsystem. SQL still backs up the data but it has a virtual location to write to that is actually the tape on the NetBackup server. This introduced two issues:
First, if you need to restore a backup quickly you have to wait for tape to spin and restore the database from tape. We were only able to do this about 80% of the time in our testing. The other 20% failed after a long time in the attempt. This is the main reason I think many DBAs opt to backup to local disk and then to tape. It leaves at least one backup local for quick restores when there is data corruption but not a system failure. Tape is better for recovering from a system failure.
Secondly, if you backup to tape you can no longer do log shipping to a standby server because the backups are on the tape and the log shipping mechanism can't find them. Making transaction log backups go to disk and the fulls go to tape doesn't make much sense for DR purposes so that was out.
If you are looking to shrink and speed up your DB backups there are a few products that do that really well, but I agree with what you've heard. Don't bother using NetBackup for SQL if you can avoid it.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
I have agree with J.T. In my experience with 3 major tape vendors, not one could provide the reliability I have needed to meet SLAs for recovery.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Tape is a very volitile media and Disk is much more stable. I highly recommend backing up to disk first so that you can be sure that you have at least one valid backup on disk for fast recovery.
-
Just to play Devils advocate, although we now use Data Protector (which seems less intuitive), I had no problems with corruptions when using Veritas over the last couple of years.
Where disk space permits, I take my own backups to disk via scheduled sql jobs as well as Veritas DB/OS backups.
-
Here's a question: How do you backup to disk and to tape without losing checkpoint information?
I assume you are not doing transaction log backups and your databases are not in full recovery mode. If they were, you would only be able to use tape or disk, not both. The reason being that a transaction log backup resets the checkpoint which tells SQL in what sequence to apply the restores. If you backup to tape and the next transaction log backup goes to disk you will have problems performing a restore using those backups.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
Yes they are just full backups. Although thats an interesting point as the one final DB we are moving from Veritas does a backup of the DB and I'm taking tran log backups to disk. The set up on Data Protector will be similar. However from what you say this is not possible. I appreciate that SQL understands the sequence of restores but why does it worry that one backup is on disk and another on tape?? I've had times when tape units fail so I run backups to disk until they are fixed. Do you think I wouldn't be able to restore in this case ?
Thanks for your input
-
Database backups to tape and TLog backups to disk should be okay. What you cannot do is backup TLogs to tape sometimes and TLogs to disk sometimes. This will cause the problem.
Likewise, all TLog backups to tape and all database backups to disk should work as well.
The problem we had was that we were trying to do log shipping to make a reporting server. Can't do that with direct to tape backups because the remote server can't restore the TLog backups.
Sequence numbers are only important in the TLog backups. A database backup is always the whole database but TLog backups have to be applied in order. Backing up on to tape and the next to disk messes up the sequence. Also, imagine the nightmare of backing up logs every hour, say, and the backup ops team changes tapes twice during the day and one of those tapes has a problem. All the TLog backups after the failure are useless and cannot be restored.
Does that make sense?
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
Hi, yes all makes sense thanks, and I understand the importance of sequences when restoring trans logs after the DB restore.
However I still don't quite know why there should be a problem with mixing tape and/or disk trans log restores as part of the process, as long as they are restored in the correct order ?
-
I use Veritas Backup Exec. I no longer use the Agent for SQL Server. Why not? Well...there's a story....
We bought our servers, tape library and Backup Exec from the same vendor. The vendor set up our system as a cluster and we installed SQL Server 2000 clustered on Windows 2000 Advanced Server. The vendor knew all this before they provided us with Backup Exec. Prior to us getting BE 8.6, there was a hotfix issued. We never were told about this and thought we were getting the latest version. Turns out the Agent for SQL Server didn't work IF, you had 8.6 with a specific build level (can't remember which one right now), and were backing up SQL Server 2000 and it was on a Windows 2000 Advanced Server and Clustered. Wow...we met all the conditions, but didn't know it. The vendor had to pay BIG BUCKS to recover our tapes when our system crashed. (We don't have any test equipment, so we couldn't test our tape backups).
Since then, I backup to disk and use Veritas to copy the backup files to tape. Don't need to spend extra money for an agent that may or may not work.
-SQLBill
-
You are right. The correct order is the issue.
Say you alternate TLog backups to tape and to disk every hour for 16 hours during the day. 8 go to tape, 8 go to disk. The database is corrupted at the end of the day before the database backup runs.
To restore the database you now have to:
Restore the database backup from last night
Restore the first TLog backup from tape
Restore the second TLog backup from disk
Restore the third TLog backup from tape
etc...
Really slow, involved process.
Beyond that, I'm having faint recollections that the Veritas SQL client, when doing TLog backups, always restarted the sequence number. i.e. it used its own internal process for keeping the TLog restores in order that didn't coincide with SQLs native one. If I am remembering right then a to tape TLog backup would not be able to be followed by a subsequent to disk TLog backup as they have different sequence numbers for start and end.
Something to try:
Backup a database to tape using your current system
Backup a TLog backup to tape using your current system
Backup a TLog to disk
Now, try to restore the database using all three. If you can, great. I think Veritas broke after the TLog backup to tape. The TLog backup to disk, even though it was done later, could not be restored because SQL didn't recognize it as the next TLog. This made Veritas's solution a "Veritas only" solution and that we didn't want.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
I'm using Veritas NetBackup 4.5GA MP6 for SQL backups. It works just fine. It is tricky to configure backups just right, but once they're on tape, I have never had a corruption problem. Also, restores are painful until you get the hang of it. Sometimes the client does not create the restore batch correctly, requiring some NOTEPAD action.
With BackupExec 8, I've had nothing but problems restoring. It works great as long as you don't need to restore from backup. (heh) Turns out that there was a patch that you needed to apply before you backed up, but you didn't find out about the patch until you ran into the problem restoring. Fun for all.
I've had great luck with SQL <==LiteSpeed==> DISK <==> TAPE
jg
-
SQLBill...
By "Fun for All", I meant "Fun for Me and at least SQLBill". Looks like you and I got bit by the same BENT bug.
jg
-
Yeah, the SQL-Litespeed-Disk-Tape process is great. Small, fast backups that work very closely to the native SQL backups.
Love LiteSpeed.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
Yep, sure does look that way. And they didn't advertise the hotfix very well because it was only needed under VERY specific situations. Kinda like Microsoft and their PSS patches. Even after that issue, we still haven't been able to get a test bed.
-SQLBill
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply