November 3, 2009 at 1:41 pm
Hi all,
I'm not sure how to start on this, but here's what our network admin is trying to implement now:
He wants to take every server we have (data, sql server, backups, files, etc) on a tape, every day.
Well, when this looks like a good idea, I told him we need to do SQL Backups (Full and diffs) in a directory, that he will then be able to take in backup. He said that his new software takes care of the sql server databases, and backups everything we need, so I do not need to do sql backups anymore (real .bak files).
I read a bit on the software, and where it sounds really interesting, I would like to have a few opinions on this, since I'm really not comfortable in having no sql backups at all, and just relying on a software that should do it all. Is it safe, what do you experts think backup wise?
Most of our servers are not "that" critical, meaning none of them need to be restored as a point in time, but we have invoice history servers that need to be backed up and sent to a relay center every month, so we can recreate invoices, if the building gets destroyed. So all our databases are in simple recovery mode, and it's probably staying that way for a long time.
I would really like to have some input on this, I want to stand up against that fact, but only if it really is not a good idea.
Thanks in advance,
Cheers,
J-F
November 3, 2009 at 1:51 pm
There were couple of threads regarding this topic and the almost everyone thought that it was not a great idea since as a DBA you do no have control over the backups. You have no way of testing if the backup that is being taken is good or not.
Also you dont want to be waiting for a network admin to get you all the back up files that are needed when the DB goes down for some reason or the other and you have to restore them. (middle of the night)
-Roy
November 3, 2009 at 2:01 pm
Roy Ernest (11/3/2009)
There were couple of threads regarding this topic and the almost everyone thought that it was not a great idea since as a DBA you do no have control over the backups. You have no way of testing if the backup that is being taken is good or not.Also you dont want to be waiting for a network admin to get you all the back up files that are needed when the DB goes down for some reason or the other and you have to restore them. (middle of the night)
I think this is more or less my thinking on the topic as well. Yes I know that BE is able to backup SQL databases without downtime and such, however you said that these backups are then being stored directly to tape? If one of your databases needs to be restored, how long will it take said network admin to get the correct tape and then read the database back in via the Backup Exec software? If it's me and my environment, I'd still be taking my full or diff backups and storing them locally on the server to give me quicker recovery time in the event of an emergency. And BTW if you're doing this, and you have them scheduled prior to your network guy doing his backup you might just have the best of both worlds. Since he'd be backing up your backups and your database. Of course depending on the size of the databases this may be a bit of overkill...
-Luke.
November 3, 2009 at 2:04 pm
November 3, 2009 at 2:09 pm
Luke L (11/3/2009)
Roy Ernest (11/3/2009)
There were couple of threads regarding this topic and the almost everyone thought that it was not a great idea since as a DBA you do no have control over the backups. You have no way of testing if the backup that is being taken is good or not.Also you dont want to be waiting for a network admin to get you all the back up files that are needed when the DB goes down for some reason or the other and you have to restore them. (middle of the night)
I think this is more or less my thinking on the topic as well. Yes I know that BE is able to backup SQL databases without downtime and such, however you said that these backups are then being stored directly to tape? If one of your databases needs to be restored, how long will it take said network admin to get the correct tape and then read the database back in via the Backup Exec software? If it's me and my environment, I'd still be taking my full or diff backups and storing them locally on the server to give me quicker recovery time in the event of an emergency. And BTW if you're doing this, and you have them scheduled prior to your network guy doing his backup you might just have the best of both worlds. Since he'd be backing up your backups and your database. Of course depending on the size of the databases this may be a bit of overkill...
-Luke.
Thanks guys, those are very interesting thoughts.
So for now, I got the time it takes to restore a backup issue, since I will not have the files locally for the restore, and will have to ask the Network admin everytime, and trust me, this takes a long time ;).
What about the backup exec functionnality though, I read it can take full backups, diff backups, transaction logs backups, copy only backups, all kinds of SQL Server Backups. So my question would be, when doing a diff, or a full, at a certain time, won't that product break my backup chain? Let's say I do a full backup at every night 1AM, and a diff every 4 hours, and No transaction logs backups.
Then his product would take a full backup at 3 AM, and a diff every 4 hours too. I think my full backup from 1AM would be completely useless, since his full will have overriden the backup chain, so I will need his backup to restore the database. I'm pretty sure the software takes real SQL Backups in the background, so probably disrupting the backup chain.
Am I right to be cautious about this?
Cheers,
J-F
November 3, 2009 at 2:10 pm
Also keep in mind that once you go to SQL 2008 you can always use the SQL Server native compressed back up that reduces the back up size from 300 + Gig to 100 Gig.
-Roy
November 3, 2009 at 2:14 pm
Luke L (11/3/2009)
Also, I know you said you are not taking t-log backups, but if you are using differentials, the BE software may cause some issues with the log chain... Just something to watch out for.-Luke.
Ouch, exactly what I was thinking about, so I was right, this could lead to some severe issues.
How about if he uses that BE tool for backups, but leaves a usable copy on the harddrive before copying on the tape, how would that look?
Cheers,
J-F
November 3, 2009 at 2:15 pm
Roy Ernest (11/3/2009)
Also keep in mind that once you go to SQL 2008 you can always use the SQL Server native compressed back up that reduces the back up size from 300 + Gig to 100 Gig.
Thanks for the info Roy, I've not used sql 2008 yet, I did not know there was a compression tool for backups. Always a plus for talks about migrating databases with manager 😛
Cheers,
J-F
November 3, 2009 at 2:30 pm
I would want to keep the SQL Server backups to local disk and have the Network Admin backup those files to tape. Plus, if you keep tow or three days of backup files on disk, your backup files will have been (hopefully) backed up to 2 or 3 tapes giving you some redundancy should a tape fail (a fairly normal occurance actually).
November 3, 2009 at 3:11 pm
Not a fan of direct to tape backup solutions. I like to have my backups to disk and then a tape backup copy the bak file to tape from disk. I also don't like to have the netadmins responsible for the database backups. Best ammunition right now is to be able to show the need for multiple layers of protection (backup to disk, and then copy those baks to tape), as well as frequency of needing to perform a restore, and last show expected turnaround time of waiting for netadmins vs. turnaround time if you do it yourself. If after all of that, they still are willing to pay the extra $$ for the sql agents with BE, and potentially break SLA's, then your hands are clean.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2009 at 3:50 pm
I'd be less worried about the SLA and more about the ability to recover.
I've had really bad experiences with trials of these kinds of backups in the past and have never gone into production with them simply because I found that I could never rely on the restore process actually working correctly.
To this day it's still a dump to disk and then move that to tape for me.
November 3, 2009 at 7:13 pm
J-F Bergeron (11/3/2009)
Roy Ernest (11/3/2009)
Also keep in mind that once you go to SQL 2008 you can always use the SQL Server native compressed back up that reduces the back up size from 300 + Gig to 100 Gig.Thanks for the info Roy, I've not used sql 2008 yet, I did not know there was a compression tool for backups. Always a plus for talks about migrating databases with manager 😛
Only when using Enterprise Edition. Bah!:crazy:
November 3, 2009 at 9:31 pm
Enterprise only yeah, but you can drop a grand on a decent backup compression tool if you don't have the finances for EE and get something equally as good.
November 4, 2009 at 12:51 am
We're using Tivoli Data Protection for SQL Server and it doesn't look that different from the one that you are describing.
We're happy with it, no issues at all.
-- Gianluca Sartori
November 4, 2009 at 6:35 am
CirquedeSQLeil (11/3/2009)
Not a fan of direct to tape backup solutions. I like to have my backups to disk and then a tape backup copy the bak file to tape from disk. I also don't like to have the netadmins responsible for the database backups. Best ammunition right now is to be able to show the need for multiple layers of protection (backup to disk, and then copy those baks to tape), as well as frequency of needing to perform a restore, and last show expected turnaround time of waiting for netadmins vs. turnaround time if you do it yourself. If after all of that, they still are willing to pay the extra $$ for the sql agents with BE, and potentially break SLA's, then your hands are clean.
Easiest way to get the business on board with your side and overrule the network admin is to let them know that with a local backup (Full + Diff/Full + trans/etc.) recovery in case of disaster would be much quicker than having to copy back from a tape. This would cause less downtime and loss of revenue if recovery from a disk drive (local or SAN) was done. After the local backup is done, let the network admin copy over the backup to wherever he wants to his heart's delight.
Plus, if anything goes wrong with the remote backup, you as a DBA can't guarantee you'll be able to support it as it is being done outside your maintainence roles by a third party.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply