| Author |
Topic |
|
TonyTheDBA
Posting Yak Master
121 Posts |
 Posted - 2006-12-04 : 04:37:11
|
Need a few quick responses here if possible, for a meeting later on today.We are in the process of putting in place a Dual Site Dispersed SQL Server Cluster, with SAN Storage (With CA Replication) at each site. Each SAN Will be attached to a Tape Backup Library device. Now I have always Backed my SQL Server DB's to Disk using BACKUP DATABASE dbname DISK= . . . and then had the Backups copied to tape on a daily basis for Archive purposes. We have agreed with the Supplier (HP) to use HP Data protector as the backup software. HP is insisting on us using DP to backup the databases online, even though I am sure that best practice is to have disk backups, and I am holding out for this.The current Backup Strategy is to Backup the DB's morning and evening with an incremental backup at lunchtime. The Tranlogs are backed up to disk every 15 minutes uring the working day and once an hour during the night. My personal view is that this just cannot be implemented using Tape Backups alone.So quick straw poll how many DBA's would work in an environment where the only backups (DB and Tran Log) were on tape?Thanks-- RegardsTony The DBA |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-12-04 : 05:17:53
|
| Hi Tony, Since you are having DIFF backup, why not put those to TAPE as well? I do not have any DIFF backup running on any of my box, so I only have FULL and TLog backup to TAPE. The only good use of a DIFF is for restore, and if you are not backing it up to TAPE, then you cannot use that for restore when needed, so why bother take it at the first place? |
 |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-04 : 06:30:11
|
| Hi Westley,I have no problems with the Disk backups being copied to tape for Archive purposes, and in fact that is what we are doing on all our current SQL Servers. The problem I have is relying on Data protector and tape backups as my ONLY form of backup.What Happens Currently06:30 Full Database Backup taken (Copied to 'spare' SQL Server)06:45- Log Backups Every 15 Minutes (Copied to Spare Server)13:00 Incremental Backup Taken (Copied to Spare Server)19:00 Full Database Backup taken (Copied to 'spare' SQL Server)20:00 Log Backups (Hourly Intervals Copy To Spare Server)21:00 Backup Directory Archived to TapeWhat the Supplier is proposing is to do away with the disk backup and back it up directly to tape with the Data Protector Plug In for SQL Server . . . Yet More software for me to learn . . . Whats wrong with BACKUP DATABSE . . . DISK = . . . AND RESTORE DATABASE . . .? How much simpler does it get?-- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-04 : 07:20:23
|
"I am sure that best practice is to have disk backups, and I am holding out for this."I don't know anything about HP Data protector backup software. Maybe it is brilliant. However, this forum is littered with questions from folk who have used direct-to-tape backup routines and had problems restoring etc.I can see a case for it if you have insufficient disk space to make a backup to disk (although in that circumstance I would advocate buying more disks first 0) Tape backup software integrated directly into a 3rd party application (like SQL Server) will always be one step behind. What happens if a HotFix comes out and the tape software stops working until the Tape Vendor issues a fix? Will you even know that the tape software is no longer reliable in such circumstances?1a) How easily can you get your data back off tape? Do you have to find the SysAdmin, and the tape, and wait for something else the tape drive is being used for to finish?1b) And what about getting the tape that the FULL is on, then the tape for the DIFF and then the tape(s) for all the TLogs - how much of a hassle / downtime is that?2) Is it quicker to restore off Tape than from Disk? (I doubt it, but if the Tape is on a separate channel its possible - but then the Disk Channel you backup to should be separate from Data channel anyway)3) How easily can you change your backup processes? For example, you are about to rollout a patch to your system. For comfort you would like to take a DIFF backup. Is that going to be easy to do, on a one-off basis, to tape? (See 1A above for availability etc.)OK so maybe you can just do a DIFF backup to disk in such circumstances (although I hate having two different paths to backup solutions, likely to be a Snafu because of it sooner-or-later)But what about this scenario. You are about to do some "risky procedure". As part of that you are going to Shrink/Reindex/UpdateStats/etc. and that task is going to generate a lot of log. So you might need to do:HeavyTransactionTaskTLog backup (to dump the abnormally huge TLog)Diff backup (so you can quickly resume from here)Some "risky" tasks(TLog backups will occur at normal intervals)So how easily can you do the one-off TLog tape backup?You absolutely must not do the TLog backups to Disk as you will have broken the recovery chain if it is not recorded on to disk.And so on ....... Just say "no" !Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-04 : 07:56:07
|
Thanks Kristen, Just what I was looking forquote:
Kristen
"I am sure that best practice is to have disk backups, and I am holding out for this."this forum is littered with questions from folk who have used direct-to-tape backup routines and had problems restoring etc.
I know quote:
You absolutely must not do the TLog backups to Disk as you will have broken the recovery chain if it is not recorded on to disk.
I take it that is in the Scenario you outlined, and not as a general principle?quote:
... Just say "no" !
I intend to-- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-04 : 08:16:56
|
| "I take it that is in the Scenario you outlined, and not as a general principle?"There should be no circumstances where someone takes a TLog backup that "breaks the chain". That includes taking a Tlog backup to their C: drive (factitious example, of course) such that the DBA can't find it when a RESTORE is needed. Same thing for a non-standard folder.We provide our Developers etc. with an SProc to make a backup:usp_Backup @Databasename, @Type -- Type: 1=Full, 2=Diff, 3=TLogwhich automatically puts the file in the appropriate folder for ease of housekeeping and restore, and sets an appropriate retention period (there is an optional parameter for that - e.g. for a FULL backup that may be a large file and only needs to be stored for a day until the current operation is deemed "safe" [the scheduled FULL backup and subsequent TLog backups will do for a disaster recovery, the current one-off Full backup is just for speed of recovery if it becomes necessary)Additional point for the above list:4) All the existing disaster recovery documentation will need to be rewritten and retested (or fire-drills if that is all you have). IME that would be a VERY expensive exercise to change.Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-04 : 08:44:16
|
There should be no circumstances where someone takes a TLog backup that "breaks the chain".Ahhh! Understood, of course not, One of the first things I did when I moved into the post was to standardise the Backup proceedure . . . That and remove sysadmin, dbo, dbadmin from anyone who didn't need it  -- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-05 : 01:08:55
|
| (Not sure this makes a significant difference, but I'll mention it anyway)5) How are you going to test restore?You need to prove that all backups are readable. Thus they need restoring (preferably to a different server) and DBCC CHECKDB'ing. Better to do that before copying to tape if possible, but in parallel will do.Then test-restoring from tape only needs the tape system to validate that the Checksum is valid.If you backup direct from SQL-to-tape you will have to test restore everything from tape direct into a different database. I expect the tape unit is busy enough without having that extra burden, and it may be harder to route from the tape backup unit to your test SQL Servers box (whereas test restoring a disk file only involves solving "How do I copy this XXX.BAK file from Server_A to Server_B")Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-05 : 07:41:32
|
| Well, I won that argument of course when 'the management' get back and we have our weekly project meeting tomorrow I can see me having to justify it all again as they purchased the SQL integration licences for data protector. Still I can ask the question "Does it treat them as files or a database"-- RegardsTony The DBA |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-12-05 : 09:04:49
|
| Tony, I know you've already had your meeting, but the one thing I thought was missing (EDIT: ok, not 100% fair, Kristen does dance around this issue a bit) from this discussion was SLA (Service Level Agreement). On disaster, how much time will the business give you to do your restores and will you be able to meet that SLA both FROM DISK and FROM TAPE?As a general rule, whenever anyone mentions backups, the first thing I want to know is the SLA for restoral.Jayto here knows when |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-05 : 09:14:57
|
| Jay,The whole point of us moving to a dual site clusterd infrastructure means that if disaster strikes (Catastrophic), we should be able to fail over to the second site with few problems. I suspect the harder issue to resolve is when it is not a catastrophic event, In general what usually happens is the overnight batch run (Yes Vendor has migrated a mainframe system to SQL Server without re-engineering the application!) fails. What usually happens is my team get a call at 8am asking us to restore the previous nights backup to test so that the reports can be run (for things like court summonses etc). So far we have been lucky in that the job failures haven't impacted the posting of cash to the live system requiring us to re run the overnights in a live environment. What I guese I'm trying to say is that our users don't want to tolerate ANY downtime during the day, although they will accept up to an hour or so. As far as an SLA goes . . . . . management are still negotiating that one, and I suspect that recovery timescales will be down to 'best endevours'-- RegardsTony The DBA |
|
|
|
|
Primary Backup Disk . . . or Tape?