| Author |
Topic |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2011-09-28 : 16:06:50
|
| Hi,server hosting company is maintaining our SQL Server 2008 production cluster server.As part of database backups, they configured evault backup- directly stores in tapes onlile, not like copying from disk to tape.We asked them to configure backups locally, but they are it is not a good practice to take backups locally also.Please guide me on this.What is if they lose tape in case of any nature disaster?Thanks. |
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-28 : 16:10:21
|
| They are right that you shouldn't also backup to the local disk but that's only true for diffs and tlog backups. You see, it'll miss up the LSNs. Do one or the other, but not both. If you do go to disk first, then only sweep the files to tape.Going straight to tape is a perfectly valid backup plan. Going to disk first is considered "better" so that you can restore "fast". Just depends on their tape infrastructure. We were actually considering going straight to tape for a bit, although we didn't end up doing it.By the way, this is tkizer. My tkizer account is broken at the moment. |
 |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2011-09-28 : 16:24:51
|
| Thanks Tkizer! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-28 : 18:10:57
|
| I disagree with you Tara on the backup direct to Tape being a comparable alternative. Yes there are SQL Agents that are respected, but there are plenty of questions in this forum that I have read where people have had difficulty with restoring direct from Tape to Database using a SQL Agent.Unless there is no other option I think that backup to Disk, and then from Disk to Tape is preferable.You have the fast restore, that Tara mentions. Probably all the more important with a hosting company - how quickly will they be able to mount the tape to restore a backup for you? what about if that backup is corrupted and you need a different one - will that be another X amount of time to wait for the tape to be mounted?What about transaction backups? Will that be done to tape every, say, 15 minutes? What about a one-off adhoc backup you want to take before you attempt something you are not confident about - deleting lots of rows of data, or making some upgrades to the database schema for example?What happens if you cannot restore the tape? Probably less of a problem with a hosting company, as they will have alternative identical hardware they can use, but in a small company you might be able to mount the tape on a drive in a different machine - but if that machine has no SQL Server then you can't restore database from tape-to-SQL-Server - but if the tape contained just a backup file you could restore that and then copy across the LAN to the SQL Server box, and then restore from disk file.I also think that, as a last resort, a data recovery specialist is more likely to be able to restore files off tape than having the added complication of restoring direct-to-database.There is risk that a service pack will introduce a subtle variation which may mean that the SQL Agent no longer makes backups that can be restored (without an upgrade to SQL Agent).What about restoring the database after an upgrade to a new version of SQL Server - is that likely to still work?And so on ... |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-28 : 18:19:55
|
| Well it's totally a viable alternative if you have the right IT department and the right tape infrastructure. Most companies do not have this though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 02:40:22
|
| You're spot on there Tara.I'm still not sure how I would cope (if backing up direct to tape) with needing to make an adhoc backup just before rolling out a new version, for example. Presumably I would have to get the IT people to pause the tape schedule so that the adhoc backup could be made, their life would be a nightmare!I suppose that Tlog backups would still have to be made to disk so that they could happen on time and also on demand. |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-29 : 09:21:55
|
| We take local disk backups(full, diff and t-logs) and these are copied to tapes daily. The retention period of backups on disk is 48 hours. We find this a very good option, as we respond to databases refresh requests immediately and do not have any dependency on tape backup team (for most of the requests). There have been cases where we've had to restore the entire database with the latest backups. Keeping backups on disk has definately reduced turn around time.. |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 12:59:17
|
quote:
Originally posted by Kristen
You're spot on there Tara.I'm still not sure how I would cope (if backing up direct to tape) with needing to make an adhoc backup just before rolling out a new version, for example. Presumably I would have to get the IT people to pause the tape schedule so that the adhoc backup could be made, their life would be a nightmare!I suppose that Tlog backups would still have to be made to disk so that they could happen on time and also on demand.
For the adhoc backup, you could just take a backup to disk and specify COPY_ONLY in the WITH. COPY_ONLY means it doesn't interfere with the LSNs.Tlog backups would not need to be to disk with a tape solution. The entire thing could be to tape. With the right tape infrastructure, the backups would be super fast and very cost effective. Every backups of VLDB's are super fast because of the technology to only write the change blocks. When we were considering this solution, we were getting dedicated tape libraries for the database servers. One of the main reasons why we didn't change our solution to this was because of the need to write batch files to do the backups and really have to invent the wheel for custom code. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 15:11:41
|
"you could just take a backup to disk"I get the impression the folk that are reliant on SQL Agent don't have the disk space for a local backup as a consequence of using SQL Agent + Tape - so they are rather "hosed" in that regard.I'd forgotten about COPY_ONLY, that's a handy trick, thanks.I can understand having a dedicated tape system for a VLDB. With a VLDB its not hard to envisage that a pukka backup solution could be designed, afforded and deployed.Put yourself in the shoes of someone a level or two down from VLDB though. At that level I'm struggling to see SQL Agent backups are a Good Idea.The hosting company we use have amazing robot-loading tape systems. They also have additional systems that mirror-tape-backup at some remote location in case an asteroid  hits the data centre. Its incredibly impressive, and its also impressive that they can restore a file in a reasonable time, but even so its an hour or so from request to start of restore, and thus if we tell them the wrong filename, or once we've got it restored the file is corrupted and we need an earlier backup file restored, then that's another hour.I haven't asked them specifically, but I don't think they could do an adhoc backup to tape for us. (They might be able to, we have a dedicated NIC for backups, so its possible that we could just "dump" a file across the NIC and it would be queued on some disk space pending a tape backup .. but I think I'm probably clutching at straws on that one).Assuming that an adhoc backup of that sort is a bit of a pipe-dream then me asking something like "we need TLog backups every 2 minutes during Index rebuilds" is going to be a complete non-starter.Scaling that down to a within-SME-company tape solution I really can't see how a SQL-Agent-to-Tape solution is a good bet. Lots of possible pitfalls. I can't see how to do urgent adhoc backups to tape [in that sort of environment], I expect that once a SQL-Agent-to-Tape solution is put in place the company will scale back on free disk space and.or put more databases on the server, so there will even less chance of having spare disk space for a slew of "one off adhoc safety backups". For me, that then leads to all the possible SNAFU failure-point scenarios for restore-from-tape that may mean, when push-comes-to-shove, that they have no restorable backup ...The saving grace might be that in the real-world the need to restore for a complete disaster is very rare. Bit of a pity when it happens and the company then finds that it is hosed  .I remember seeing a statistic for the the number of businesses that go out-of-business following a major fire at their primary premises. Although those sorts of fires are rare, the subsequent failure-rate made sober reading.What I need is a naive question from a newbie saying "I'm a phD student looking for a project, what do you recommend" so I can get them to research the rate of bankruptcy of SME businesses who use SQL Agent backups ad then have a failure to restore their line-of-business database ...Perhaps with Cloud Computing this is a non-event though? |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 15:18:22
|
| I definitely wouldn't recommend direct to tape backups for most companies. You need the right people in place to design the proper solution. Backing up just the change blocks is a huge benefit though to the direct to tape backups. Imagine our environment with hundreds of database servers, just how much storage we are wasting on full backups alone. I've seen the solution in action, and it is amazing. You need the right people and the right infrastructure to implement it. Most companies will have neither. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 15:21:05
|
| Thanks Tara. Very useful insight. |
|
|
|
|
local disk backups