April 12, 2005 at 1:23 am
OK Guys need some suggestions,
What would be the best backup strategy for a application database,
1.getting updated every minute,24x7.
2.best way to handle t-log to avoid space & recovery problems.
3.No backup window possible, must be backed up when online.
4.maximum data loss affordable = 1 hour.
5.backup on daily tapes (monday-saturday)
Thanks.
Tanmay.
April 12, 2005 at 2:46 am
OK, here's my stab at an answer (there are lots of different opinions ).
A backup window isn't a problem because SQL lets you do online backups.
I would suggest that you do 1 full backup per week (to disk) on whichever night you think is best (just make sure you check that it's worked). You can then copy the file you backed up to to tape the next day (to make sure you have a local and safe copy).
On all of the other nights a differential backup to a seperate disk device (i'd use 6 seperate backup devices to prevent file corruption problems (not that you should get any but it's safer)). Again you can copy these off to tape the next day.
Because you can only afford a maximum of 1 hours worth of dataloss you're also going to need to do transaction log backups, I'd be inclined to either write something myself or in your case use a maintenance plan for this bit (but not the full or differential backups). Depending on how busy your server is (and how much data is pumped into it) I'd say look at doing a backup every 20 minutes or so (or 30, or whatever you want just so long as it doesn't exceed an hour).
If you use a maintenance plan for this then you can decide how many days worth of transaction logs you want to keep, I'd always keep at least the last weeks worth just in case there were problems with the differential backups but if you're really paranoid then only clear out tlog backups older than 2 weeks
Monday - Full Backup
Tues - Sun - Differential Backup
Daily - Transaction Log Backup at 20 minute intervals (or whatever)
April 12, 2005 at 5:21 am
I dont know what size database you are running. If its a medium sized database, I could think of Full Backups been running once in a day.
Differential backup running at 6 hour interval having 4 backups in a day and deleting the last backup.
T-Log backups running at 15-20 minute interval deleting the older backups than 7 hours.
In this case, you could have 1 Full backup, 1 Differential backups and 21 T-log backups at any point of time.
I have a similar scenario in my enviornment where I run 2 databases of 3GB each.
--Kishore
April 13, 2005 at 4:49 am
Also a lot depends on your guaranteed recovery time. The shorter the time you get to recover after a disaster, the more often you will need to do a differential- or full backup. Recovering a lot of tl-logs is a pain if you've got no time.
How fast/big will your db grow?
What kind of hardware is the server living on?
As an example, i've got an server with 4 Pentium 2GH cpu's, files on SAN. On that one a full backup to disk with a size of 25 GB is running 10 minutes, with no noticable impact on 160 users. I could do one every hour, if i wanted to...
I would recommend tl-backups every 15 minutes too...
In any case you should get scripts ready to restore all db- and log-backups automagically.
karl
Best regards
karl
April 13, 2005 at 6:32 am
What about using replication as a sort of real time backup?
April 13, 2005 at 10:36 am
Everything depends on size and space: how big is the database and how much disk space is available AFTER allowing for growth. It's a juggling act, not entirely unlike indexing in that a lot becomes personal preference and experience.
When I'm in a DBA role (right now I'm a developer with no DBA responsibilities), my norm would be to do a full backup after business hours which would then be captured by tape. Transaction logs would be truncated after the backup and before business starts the next day. So that gives you your starting recovery point. I follow that up with transaction dumps normally every 15 minutes, depending on how much transactional activity the database receives. If it's used for lots of querying but not much insert/update/delete going on, I would consider slacking the time off a bit.
To shorten recovery time, I also do incremental database backups at noon, frequently that's the mid-point of the business day, and continue the transaction backups.
SO, let's say your server crashes at 11am. Reload previous night's backups, reapply transaction logs. If it crashes at 4pm, ignore the morning logs, add the incremental dump, restore the afternoon logs.
In terms of steps, let's say your 15 minute log backups start at 7am and you crash at 11:20am. You've got 17 transaction logs to be reapplied. If it dies at 12:20, you have one incremental data dump plus one transaction log, in both cases you might lose the last 5 minutes of work.
If your restore time cuts into your recovery window too much, also look at log shipping. And there's nothing preventing you from using a scheduled task to copy your backups to another server, which is not the same thing as log shipping.
One tip that was beneficial for me. When you have a server with lots of databases, frequently little ones, stagger their maintenance jobs. Let's say your standard is to dump the transaction log every 15 minutes, and you have five databases. Normally tlog dumps are pretty quick (not always, obviously). Start DB1's tlog backup at 2 minutes past the hour to repeat every 15 minutes. DB2 starts at 4 minutes past the hour, etc. This way, in all likelihood, the previous database will be done before the next one starts. I've seen some I/O performance issues if all the databases dump at the same time.
Two final thoughts. First, I've never worked in a SAN environment, so obviously Your Mileage May Vary. I've never managed DBs bigger than 2 or 3 gig, so again, YMMV. These are just the best practices for how I like to do things, I've worked with every version of SQL back from the 4.21a/LanMan days, and with the exception of a huge database under 6.5 that blew up, I've never lost data (the big database was too big for the amount of storage in the box, so we couldn't dump it to disk, and the backup agents that went inside the DB to backup at that time weren't very good).
Second, you need to keep your backups in line with your disaster recovery plans. There's a world of difference between a server crash/hiccup that requires a restart (you probably won't skip a beat as SQL will roll all transactions back and forth as required), a disk crash/replacement (might require restoring backups from tape and/or other servers/disks) and a server going up in flames with all drives lost.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 13, 2005 at 10:38 am
I would recommend using the either the maintenance plan or sqlmaint program, so that you easily have named backups with date/time embedded. Do a full backup once a day, as close to the time the tape backup is run, but make sure it is completed before the tape looks for the files. Keep 2 days backups on disk (if not, murphy's law will ensure that the only disk backup you have will not be good when needed). Schedule TLog backups every hour, keep 2 days of these (to match db backups).
You can add a few differential backups if you want to reduce the number of TLog restores. But I have found that even when doing 20 TLog restores, it is generally easy (just have to change the time in the filename), and they are usuallly pretty quick. If you are not used to using diff backups, having to remember that this one database has it when under the pressure of doing a restore may be a risk. I would say that keeping backup plans consistent for your databases is more important than trying to reduce a few TLog restores.
Hope this helps
Mark
April 13, 2005 at 12:24 pm
Three things matter most overall. Database size and the type of disks you are using and whether o not your backupos go directly to tape or disk. First off, backups to disk are superior in speed than to tape. On the size question, if the database is 30 Gb or less then SQL maintenance plans are the way to go. Full backup daily and transaction log backups every 10-15 minutes (I've a similar 30 Gb database that can be recovered with 2/3 a day's transaction logs in a little less than an hour). Finally, disk type. By this I mean local disks or SAN disks. SAN didks are the way to go. So if your on a SAN then you can have the awesome restore time you need.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 6, 2008 at 11:54 am
Can someone give some advice on this scenario, please?
SQL 2K SP4 on SAN (I don't know much about the set-up of this).
My plan is to use log-shipping to create a stand-by server. However, SAN snapshots are taking place, which seem to disrupt the transaction log sequence. Currently, a full backup occurs nightly and, on most DBs, transaction logs occur every 15 minutes. Additionally, BackupExec runs full database backups every morning (to tape). I don't think that the disks, and thus, the full backups and transaction logs, are being copied to tape.
I want to streamline this whole backup process by eliminating some of these seemingly redundant steps. Are the SAN snapshots reliable you think? Can anyone give an example of a DR plan, involving a SAN? Any other suggestions? Thanks.
Chris
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply