November 5, 2009 at 9:37 am
Can anyone give me the pros & cons of using SQL Server backup vs other backup tools like AppAssure Replay for SQL?
Our network admin is swears by this product for backing up the Exchange server but I am wary of third party backup & recovery tools.
Any help/advice would be much appreciated.
Thanks
November 5, 2009 at 6:34 pm
I haven't used that product but most DBAs prefer to use native sql server tools like Management Studio to schedule and restore backups. Then your Systems people can backup your backups. Personally I would consider something like Quest's Litespeed or Red-Gate's Sql Backup.
Our company is considering switching to CommVault's sql backup tool and I'm opposing it but may be overruled. Commvault creates sql backups that cannot be used by native sql products like Management Studio so you are totally dependent upon the Commvault software, which of course means learning another tool.
November 6, 2009 at 4:25 pm
In my work place they are trying to get us to go to NetApp's SnapManager for SQL. I tested an older version, 2.1 and it had several setbacks. The biggest item that killed it for me though was no point-in-time recovery capability. A lot of my databases do not require that, but my mission application databases need that. Another thing was that I could not perform operations such as checkpoint checking of backups while they occurred.
We have the newest version, 5.0, and I am going to be testing that next week to see if it fixes any of those shortfalls.
Joie Andrew
"Since 1982"
November 7, 2009 at 9:45 am
Redgate, Idera & Litespeed give compressed, faster backups. You can get trial versions to test them out. I've used Redgate & Idera and have been satisfied with both. (mostly)
November 7, 2009 at 10:40 am
SQL server backup = no cost
AppAssure = how much?
Does the network admin realise backing up and recovering exchange server is not the same as for a SQL database?
This tool would have to meet all the criteria you require for a backup and recovery strategy plus offer you something the native backups don't as there is a cost involved.
I would also refer you to this thread
http://www.sqlservercentral.com/Forums/Topic691281-361-3.aspx#bm814907
---------------------------------------------------------------------
November 9, 2009 at 9:21 am
i've used Veritas/Symantec Netbackup for almost 10 years now. it's buggiest piece of software i've ever seen but i'll use it over native SQL backup any day. The old Computer Associates ArcCenter was probably the worst backup product ever, but i came to my employer after they junked it in favor of Netbackup. this year we upgraded to LTO-4 tapes from DLT and the performance is great. we use the native sql backup/restore only when we have to in special situations and never for production.
i've also used i365/Evault for a year. OK piece of software but you have to be crazy to trust hard disks over tapes to store your backups. but there are much better disk backup products now. the big thing now is dedupe while the backup is running, not post-process dedupe. We had some amazing compression with Evault but LTO-4 tapes are $55 each for 1.6TB of compressed data and i have a lot of my tapes holding almost twice that amount. you can't beat the price since you have to run 2 evaults to sleep at night.
one nice feature is automatic DR. it uses SQL 2005 Express as the backend to store the data and you can replicate it. you backup to one vault and it replicates to a DR vault automatically. If your main vault crashes it will backup to the DR vault. you rebuild the primary, replicate the data over and then backup to the primary again.
November 9, 2009 at 9:45 am
SQL Noob (11/9/2009)
i've used Veritas/Symantec Netbackup for almost 10 years now. it's buggiest piece of software i've ever seen but i'll use it over native SQL backup any day.
having said its the buggiest piece of sofware you have ever seen I am intrigued as to why you prefer it to native backups?
the big thing now is dedupe while the backup is running, not post-process dedupe.
dedupe appears to be the latest buzzword in storage circles. What does it mean?
---------------------------------------------------------------------
November 9, 2009 at 10:56 am
Native + 3rd party compression meets every requirement a DR scenario can present. SQL 2008 also now includes the compression, unsure how well it performs compared to red gate or litespeed.
November 9, 2009 at 11:12 am
first netbackup
it's very flexible and centralizes administration. we only have 30 SQL servers or so but if we had to keep track of backups on each server it would drive us crazy. with netbackup it's easy to track successful and failed backups and even without buying the reporting add on you get decent reporting capability. took me a few seconds to get data for SOX auditors. even the big bugs you can work around by taking the tapes out of the robot
and i will never trust keeping backups on disk. one time i had 2 different hard disks go bad within 3-4 days of each other on a 1TB archive database. i was at Whole Foods when i got the alert, walked back and ordered a new HD from HP. put it in the next day and it took 2 days to rebuild. literally within 3 hours of the RAID rebuilding another hard disk went bad. today i have another server that i have an open case with HP. possibility that the RAID controller is bad. imagine if you had years of backups stored on there.
dedupe is deduplication. say someone creates a spreadsheet, 10 people make changes and save it under 10 different names. Dedupe will figure out the same bits and compress the backup by taking them out. we got some really good compression numbers with evault, but tapes are so cheap and the performance was the same that it didn't make sense. Reason is you have to buy 2 sets of disk backup in case the primary crashes due to OS or hardware issues. if my netbackup install crashes and i have to rebuild from scratch all i have to do is restore the catalog and/or reimport the tapes again and the catalog will be rebuilt. i won't lose actual backups.
it's good practice to remove tapes from the library on a regular basis and send them somewhere else. with disk it's too easy either through a policy change or through foul play to destroy years of backups
didn't like evault since it didn't support differential backups. only full and log backups. and restoring to a different server was a 2 step process. restore a file then run sql code to restore the database from the file. with netbackup it's in 1 step
one time i had to fight with a consultant to move backups to tape. he set it up to back up to local disk and by accident i found that they backups weren't running for 3 weeks because the disk was full and some process to delete old backups didn't work. now we backup to tape, send offsite and can recover data going back months or years if need be. we always get requests to restore data from 3-5 years ago that is missing from the archive database and we have to order tapes. the SOX auditors would kill us if we didn't have this capability. what if you get sued and you get a discovery subpeona to restore data from 3 years ago and you can't because the disk crashed or some other reason? what if there is a criminal investigation that needs data from 5 years ago and you proudly tell the law enforcement people that you deleted it? imagine you were backing up Bernie Madoff's data and you tell people you deleted the old data because you didn't have enough disk or the disk crashed?
November 9, 2009 at 2:40 pm
i had to fight with a consultant to move backups to tape. he set it up to back up to local disk and by accident i found that they backups weren't running for 3 weeks because the disk was full and some process to delete old backups didn't work
Monitoring backups would have revealed this problem quickly enough. Even if you backup to tape you can still have failed jobs if the tape is full and overwrite protection prevents it from being overwritten for a specified amount of time.
I agree with you that a 3rd party solution allows MUCH easier monitoring of backing up multiple systems. My question though is does NetBackup allow the type of backup/restore options that are provided in built-in SQL Server? Point-in-time recovery? Checksums of backups as they are occuring to prevent successful backups of corrupt data? Multiple backup schedules on different databases depending on operational need? I have used NetBackup and Backup Exec for both normal server and Exchange backups before, so I have good ideas of where they are good and bad compared to built-in offerings, but I have never done SQL servers with those products, so I am just curious.
Joie Andrew
"Since 1982"
November 9, 2009 at 2:54 pm
yes
netbackup and other third party products use Microsoft's API's and build upon them. if you do an sp_who2 during the process you will see it execute backup database and restore database depending on which job you are running
the only time i had tape problems was when we were still on DLT tapes and the amount of data we had to backup outgrew the environment. Otherwise netbackup manages the tape contents and automatically uses a new tape when a tape is full and keeps track of where the images are stored. there is a feature to use more than one tape at a time for backups, but we don't use it.
for point in time i used to run log backups on one of the servers but not anymore. we run full weekly or daily backups and daily diffs. for any database 1GB and less i have a separate policy set up to run a full daily backup. and you can run file backups as well via the agent
for sql 2005 servers i have all the db's set to use checksum and the backup process checks for corruption during the backup. it can also do it during the restore.
and i have my backups split into 5 policies and they all kick off at different times and on different schedules
to create backups and restores you use the netbackup agent GUI on the client to create a batch file and customize it a bit. restores usually need more customization since you have to change drive letters, database names, etc. One nice thing is that if you do differential backups it will do a restore from the full backup and differential in one step using one batch file.
December 1, 2009 at 12:25 pm
Hi all-
Just wanted to throw in my 2 cents from the AppAssure Replay perspective.
There are a few significant advantages to consider, some of which have already been mentioned here, but bare repeating:
-Third-party products like Replay from AppAssure integrate advanced functionality like deduplication and far more significant compression, which can help drastically reduce your storage footprint for backups, as well as your bandwidth utilization if you replicate your backups offsite.
-Storing your backups as snapshots to disk allows for much smaller RPOs (recovery point objectives, meaning you can recovery data from 15 minutes ago, not 24 hours ago when the last job was run), and much faster RTOs (Recovery Time Objectives, meaning the time it takes to recover a backup is much shorter because you're recovering from disk, not tape).
-Solutions like Replay also allow for more granular recovery, such as recovering individual objects from a point in time without having to recovery an entire database.
-These solutions also allow you to restore to hot standbys, dissimilar hardware, and virtual machines.
You can find more information on our solution at http://www.appassure.com/applications/replay-4/replay-for-sql/. I'd also be happy to answer any questions you might have. Feel free to e-mail me at leadgeek@appassure.com or find us on Twitter at @appassure.
Thanks!
Joshua Hoffman
Lead Geek @ AppAssure
December 1, 2009 at 1:35 pm
If I am the DBA responsible for the data, and someone else is a network admin responsible for running the tape backup jobs, I am very uncomfortable with any tool like Backup Exec that claims to be able to backup/restore my databases. Unless the whole Veritas setup and the tape hardware are dedicated to SQL Server, and I have full admin rights to the backup tools and schedules, it's just asking for trouble.
Remember, backups are not the primary issue. Restores are. If something bad happens, I need a restore right now. If I track the other guy down and tell him to drop whatever he's doing, possibly interrupt whatever the tape is doing right now, load my tape from last night and restore it, what's going to happen? Even if he does it (because maybe a high-level manager is breathing over my shoulder), he's not going to be very happy about it. At one place I worked, after the boss's-friend-pretending-to-be-a-sysadmin replaced the sysadmin-who-knew-what-he-was-doing, it took at least four hours just for him to figure out how to load the tapes and select the desired files.
If on the other hand I keep several days of backups on disk, I can immediately restore to any recent point in time if there is a crisis. The network admin only has to worry about backing up the fileshare where I dump all the .bak/.trn/.dif files. He doesn't have to get involved in varying backup schedules, retention policies, or any of the nuances of partial backups, striped backups, or whatever you need to handle a variety of OLTP databases, archive databases, development databases, sizes ranging from 1MB - 1TB, and so on. Another benefit is that the SQL backup jobs write to a fileshare (mostly) at night and can be backed up during working hours, leaving the whole night open for backing up Exchange and other file servers.
Anyone who says they are going to centrally manage backups for 30 SQL Servers directly to tape from one Backup Exec job with one set of policies needs to be introduced to a bag of lime and the abandoned tape safe in the cellar.
Once you rule out any kind of remote backup tool that is out of the DBA's control, there are a number of third-party backup tools to choose from. Compression is the number one feature (now available in SQL 2008 native backups), followed closely IMHO by object-level recovery. I only have personal experience with SQL LiteSpeed, and liked it very much. You just have to decide if there is some feature that will provide a benefit that justifies the cost, compared to free native backups.
December 1, 2009 at 1:44 pm
we have 10 or more policies on netbackup for backing up SQL. i just added 4 more the other day. and there is never a need to hunt for tapes since any decent tape library can store months of tapes.
December 1, 2009 at 4:06 pm
there is never a need to hunt for tapes since any decent tape library can store months of tapes
So you're saying you never remove tapes for off-site storage?
I've seen many installations where they apparently did not have the budget for a "decent" tape library by your standards. I have seen a lot of fairly expensive tape units, but I have also seen databases grow to 100s of GB faster than management wants to replace equipment, and hoards of people who don't understand why they have to keep their Exchange inboxes under a gigabyte. Then there are the people who think they need to keep a copy of every datafile they ever generated. And it all has to be written to tape once a day.
There are many companies where you can explain all day how a shiny new deduping tape library will solve all their problems, but you can't get past a mindset of "the old one ain't broke". If you've never experienced these limitiations, then hooray for you.
Having a fancy tape library with essentially unlimited storage makes backups easy, but doesn't guarantee a quick restore. If I have a database with a weekly full backup plus one or more daily transaction log backups, the files I need for a restore might be on seven different tapes. I could go to Management Studio and do a tail log backup, then tell it to restore to the desired point in time and it could finish the job before your tape library has the first tape loaded.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply