June 29, 2011 at 10:13 am
I am trying to find out what is the best practice for backing up to tape.
I am a DBA with two versions of SQL Server, 2005 SP3 and 2008 R2.
The 2005 servers use native SQL Server backup commands and then uses Backup Exec to swipe the backups to tape.
The 2008 R2 servers which were created by an offshore team use the SQL Server Agent for Backup Exec to swipe directly to tape.
Here are my questions:
1 - Are there any advantages of the costly SQL Server agent versus just using the native file agent with Backup Exec?
2 - I know that other machines that do not have the agent installed can not be a target of the tape backup. This leaves me in a jam if I need to move the database w/o purchasing another license ahead of time.
Thanks again for everyones input.
John Miner
Crafty DBA
www.craftydba.com
June 29, 2011 at 11:38 am
Is there a question with #2? It seems more of a statement.
And when you say "costly" in # 1, are you referring to the money cost of the third party software or the CPU cost of SQL Agent?
June 29, 2011 at 11:40 am
I think I may be missing something here. SQL Agent comes with SQL Server, and doesn't have a direct cost associated with it. I'm also not sure what you mean about servers with SQL Agent installed not being able to be the target of a backup. SQL Agent is just a job-scheduler that runs off of the msdb database in SQL Server. Backups through it are just scheduled native-SQL Server backup commands packaged up for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2011 at 11:48 am
What cost of SQL agent? It's free... once you paid for the sql server license!
As for hardware cost it's extremely limited.
I've seen backup exec work and I'll take my script anyday over what it does (really perfectionnist and I go much further than just backup something).
June 29, 2011 at 11:52 am
Ninja's_RGR'us (6/29/2011)
What cost of SQL agent? It's free... once you paid for the sql server license!
I think he's taking about the specific plugin for Backup Exec that enables it to integrate with SQL Server Agent (which would have a monetary cost associated with it). It just got phrased in a confusing manner.
June 29, 2011 at 11:56 am
Hi All,
I guess I was not specific on my wording.
http://www.symantec.com/business/products/family.jsp?familyid=backupexec
What I meant was the Backup Agent for SQL Server. Please see link above for over view of the product. There are agents for every server product that you can think of.
Right now, on the SQL Server 2005 machines we do the backup and maintenance using Brad McGehee's
ebook as a guide - "Brad's Sure Guide to SQL Server Maintenance Plans". Then, Backup Exec is used to write the backup (*.BAK) or transaction files (*.TRN) to tape.
This works rather nicely since I can restore the files to any server of my choosing. And restore the database via SSMS.
On the other hand, the offshore team in Asia did the own thing. I have Backup Exec installed on the server with the tape library and agents running on the server. It has been problematic at times.
I am in the process of testing these tape backups at the corporate headquarters. That is where my problem starts. I need to buy the full backup package plus agent for SQL Server for my development workstation to test the tapes, an additional $1500.
In summary,
What is the advantage of using Backup Exec Agents?
Does anyone have any real life experience with this software?
Have you had problems with the software? If so, what?
- John
John Miner
Crafty DBA
www.craftydba.com
June 29, 2011 at 12:04 pm
Short story short. You have a FREE working solution from Brad which is an MVP (AFAIK).
It's been tested and put in production for months without issue.
If it works for sql 2005, it WILL work for sql 2008 and R2 with extremely rare exceptions which you'll find out the 1st time you try the script.
Seems like a total no brainer to me.
Dump backup exec. You're way better off with your own personalised scripts.
They will eventually cost a few 100s $ in time invested but that's well worth it.
June 29, 2011 at 12:07 pm
j.miner (6/29/2011)
What is the advantage of using Backup Exec Agents?
You can back directly up to the tape drive without chewing up local hard disk space. You don't have to mess with SQL Server maintenance plans (because Backup Exec will do everything for you). There's less time involved in the backup since you're not backing up twice.
However, there are a few cons.
What happens if the tape is bad but you don't know it until after you try to restore? If you're only backing up to one place, that's a single point of failure (in my mind) that could cost you and the company.
What happens when the tape drive goes kaput? Will someone be monitoring closely so that backups can be switched to the local hard drive immediately (without loss of a day or two's backups)?
If you lose your Backup Exec license, and a day later you have a failure, can you use a backup created by Backup Exec to restore to your server without the Backup Exec software interface? Some third party backup tools have a command line interface that allows you to "decrypt" the backup to a SQL Server native backup for such contingencies.
Also, can you use the Backup Agent tool on a server where SQL is installed, but not the Backup Exec tools that integrate with SQL Server?
How much more difficult is the Backup Exec tool to use? Will using it cause problems with your other servers?
Does anyone have any real life experience with this software?
It's been a long time since I've used Backup Exec. Currently, I use Litespeed. As soon as we get to 2k8, we're getting off of that, though, and going back to native SQL Backups. Our primary concern had been needed a compression tool because of how much space we lost.
June 29, 2011 at 12:17 pm
Ah!! Two different things called "Agent". That's what confused me.
I can't say I've used the Symantec product recently. I think I may have quite some time ago, but I'm not sure.
What I can say is that every third-party backup tool I've used has created more problems for me than it's solved. Pre-SQL 2008, their only real advantage was compression, and even that is a limited advantage these days.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2011 at 12:24 pm
Good points -
I like the idea of having at least one or more full/diff backups and transaction logs on disk just incase the tape that we write to back goes bad.
I also like the idea of having the ability to restore to any server w/o having to use the agent which cost about $500 per server.
Question -
Are you using LiteSpeed for compression on SQL Server 2005?
What I am thinking -
I tried using a command shell in SQL Server Agent Jobs to compress large files 300 GB on the 2005 server. I installed 7 zip but it took like 4 hours to compress after a 2 hour backup.
Since disk is cheap, I am recommending on adding a drive instead of going that route.
Question -
Is the tape drive always located on the SQL server box?
Question -
Has anyone had bandwith issues on copying < 2tb backups between servers?
What I am thinking -
I think I have to tell my boss that I need to spend the money now so that we can test the backups from Asia.
Thanks all for your insight!
John Miner
Crafty DBA
www.craftydba.com
June 29, 2011 at 6:16 pm
I've used backup exec, tivolli, and a few others in the past to manage the backups and they work well. Brandi is right about not knowing if the backups on the tape are really good until you need them.
It's also expensive for the backup software's sql backup agents. What I found works best for me is to either back using SQL Server's Agent to file (either scripted or through maitenance plans ((I prefer scripting)))then let the backup program like Backup exec just backup the directories where the backup files are being kept. You can do this locally or even to a network share.
In the last few jobs I've had I haven't had direct access to the backup software. The companies are larger and other groups are responsible for it. So for me to restore something from tape means jumping through hoops. Getting them to restore something from a file system is much easier than trying to get them to restore a database and redirect it to a different sql server or a different database name.
Oh...and a final tip, make sure you test your restores often with whatever method you go with because your backups are only as good as what you can restore.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
June 29, 2011 at 7:05 pm
As a side bar, I don't backup to tape. I backup to disk and then back that up. It makes life a little more convenient for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2011 at 4:12 am
Jeff Moden (6/29/2011)
As a side bar, I don't backup to tape. I backup to disk and then back that up. It makes life a little more convenient for me.
dito.
Even "worse" with regards to disk space.
We backup to (local) disks.
As a last step of the backup job, we push the .bak file to a safe zone.
(i.e. network share)
The safe zone is copied to tape by another team on requested intervals and retention periodes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2011 at 6:19 am
ALZDBA (6/30/2011)
Jeff Moden (6/29/2011)
As a side bar, I don't backup to tape. I backup to disk and then back that up. It makes life a little more convenient for me.dito.
Even "worse" with regards to disk space.
We backup to (local) disks.
As a last step of the backup job, we push the .bak file to a safe zone.
(i.e. network share)
The safe zone is copied to tape by another team on requested intervals and retention periodes.
Backup goes to SAN, which also is backed up.
Goes to tape after 2 days before being deleted off SAN
Use LiteSpeed - 16 minutes to compress 160GB to under 15 GB.
So the theme seems to be - a bit of redundancy in your process to avoid single point of failure.
Most 3rd party tools might be easily justified by the time and media savings.
I'd think most vendors have a trial version to test with to see the benefits.
June 30, 2011 at 8:10 am
j.miner (6/29/2011)
Question -Are you using LiteSpeed for compression on SQL Server 2005?
Yes, I am. For now.
Question -
Is the tape drive always located on the SQL server box?
Not always. It just depends on how your environment is set up. As others have said, we backup to SAN and then the SAN gets backed up to tape by another department.
Question -
Has anyone had bandwith issues on copying < 2tb backups between servers?
Not usually. Depends on if the network is having a bad day or not. Most of our servers are in the same location, so it's not like we're copying data from one city to another.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply