Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-03-10 : 10:41:56
|
| I've ordered a Dell 2850 with 6x36 Ultra 320 SCSI drives. The split backplane RAID will support dual 3x36 RAID 5 arrays, the 2nd array will be dedicated to the database. I don't plan on having any other data there for read / write other than the database.The first array will host Windows 2003, IIS, webs, and SQL transaction logs.I'd like to implement a recovery plan that would reconstruct the database up to the most recent 15 minutes in the (unlikely) event of a catastrophic failure which the RAID 5 array somehow loses the disks or database.Recommendations on setting up a plan for this environment?Is SQL Standard edition enough or do I need Enterprise Edition?Are 3rd party tools for backup / recovery advisible or is SQL alone sufficent? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-10 : 11:58:00
|
| I normally have a plan like this:1. Make sure the database is in Full recovery mode.2. Setup a maintenance plan to do a full backup of the database once per day, during off peak hours. Retain the backup for at least on day, or more if you have room.3. Setup a maintenance plan to do a full backup of the system databases once per day, during off peak hours. Retain them for a week.4. Setup a maintenance plan to do a transaction log backup of the database every 15 minutes, and to retain the log backups for 3 days.5. Backup all the backup files to tape at least once per day.Make sure the backups are going to a different disk array than the one where the database is.You may also want to replicate the backup files to another server, in case the server loses it's system disk. Then you can recover the databases to another server.RAID 5 is good protection, but be aware that you still have a potential failure point that can wipe out a disk: the disk array controller. When that goes bad, you will ususlly lose everytine attached to it. That is why I mention tape backups and replication of the backup files to another server.CODO ERGO SUM |
 |
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-03-10 : 12:10:56
|
| Personally, I prefer using the SQL server to create my backups. This requires a directly connected tape device or a seperate disk volume with enough free space to hold the dumps. My primary reason for this is Microsoft. If something should happen, I am using a Microsoft supported backup method, which means I don't have to go through multiple vendors to solve a problem. If you backup to disk, you can always archive the backups to tape later using normal file based backup over a network or locally. This would also be a good time to move the files to an online disaster recovery store if you have one.Using this plan you can create a custom backup job and schedule it to run every 15 minutes. I do something similar on an hourly basis for my transactional databases. For instance, I do a full backup of the database every week to disk. During business hours, I do a hourly transaction log backup and finish the day with a final transaction log backup. At night, the backups are committed to tape.Recovery consists of the last full backup and each transaction log up to the last transaction log backup. To prevent SQL server from overwriting previous hourly backups before they have been committed to tape, you can you a custom file naming convention and a custom backup script like this:===========================================================================================declare @thisday varchar (30)declare @prefix char(13)declare @device varchar(255)declare @filename varchar(255)set @thisday = getdate()set @prefix = 'DB_HOURLY_'set @device = @prefix + Convert(varchar(4),Year(@thisday)) + Convert(varchar(2),Month(@thisday)) + Convert(varchar(2),Day(@thisday)) + Convert(varchar(2),datename(hh,@thisday)) + Convert(varchar(2),datename (mi,@thisday))set @filename = @device + '.BAK'declare @path varchar(255)set @path = 'X:\SQLBK\' + @filename/*EXEC sp_addumpdevice 'disk',@device,@path*/BACKUP LOG [DB] TO DISK = @path WITH NOINIT , NOUNLOAD , NAME = N'DB hourly', SKIP , STATS = 10, DESCRIPTION = N'hourly transaction log backup', NOFORMAT================================================================================================To save disk space at the dump location you can execute an OS command to clear anything that has already been committed to tape when you run the full backup. Just configure the job to execute the command on successful completion of the backup.Like this:del /F /Q /A:-A G:\SQLBK\DB*The command above deletes all files that start with 'DB' and have had their archive bit cleared. Obviously this doesn't work if you don't use the archive bit in your backup plan. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-10 : 13:36:32
|
| "Is SQL Standard edition enough or do I need Enterprise Edition?"If you want to log-ship to somewhere else EE has a Wizard, but you can roll-your-own [using Standard]. So, no I don't think you need EE."Are 3rd party tools for backup / recovery advisible or is SQL alone sufficent?"SQL tools will be fine - some 3rd party tape backup software to get the backups off the disk and on to tape would be a fair plan."I'd like to implement a recovery plan that would reconstruct the database up to the most recent 15 minutes in the (unlikely) event of a catastrophic failure which the RAID 5 array somehow loses the disks or database."My views are:I used the Maintenance Plans for years. Now I wouldn't go near them. But there again now I have built my own backup routines, from bitter experience, and without the bitter experience you might be safer with the Maintenance Plans.If you are going to make-your-own then Tara's Blog has all that you need as a starter-kit.I like to have a reasonable time period of recoverable material on the disk - it takes a fair while to find the tape, and restore from it. So long as you have disk space to spare I suggest keeping "sufficient" backups online. We keep Transaction backups for a couple of days, Daily backups for a couple of weeks and weekly backups for a month or two. So we lose granularity the further back we want to go - but the Tapes would save our backon if we actually needed a restore to exactly 10:52 AM First Tuesday of Last Month!Set DB to Full Recovery model.Make Transaction Backups every 15 minutesMake Full Backups Daily (or possible Full backups weekly and Differential Backups daily - that will save you disk space, but its probably pointless in your case)Ideally have something push the backups to another machine - otherwise, assuming a Tape backup once a day, your recovery is vulnerable in the case of total hard disk/machine failure. ("DELETE MyImportantTable" would be recoverable though!!)Restore the backups to make sure they can be restored. If you are pushing the backups to another machine I'd have that restore them in an automated fashion. Otherwise you have no way of knowing that the backup files are 100% A1-okay.You also need to schedule regular FireDrills to prove you can restore from Tape too.And at each turn when you think "That's WAY too much trouble" just remember the number of times you see people posting "I have an LDF file but no MDF, can I restore anything?" type messages on SQLTeam!Kristen |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-03-10 : 14:42:34
|
quote:
Originally posted by jason
This would also be a good time to move the files to an online disaster recovery store if you have one.
Any recommendations? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-03-10 : 14:46:35
|
quote:
Originally posted by Kristen
And at each turn when you think "That's WAY too much trouble" just remember the number of times you see people posting "I have an LDF file but no MDF, can I restore anything?" type messages on SQLTeam!
Well said. Timing is everything. |
|
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-03-10 : 15:41:14
|
quote:
Originally posted by SamC
quote:
Originally posted by jason
This would also be a good time to move the files to an online disaster recovery store if you have one.
Any recommendations?
I don't use one myself. Too expensive and some of my databases exceed 100GB so it's kind of impractical as well. But if you have the cash/resources there are plenty of providers who offer this type of service. You can even rent rack space and remote host your own solution. Besides the service and rack space, you will most likely need a dedicated Internet connection to perform the transfer so that it doesn't interfere with production. If you have more than one site, you might consider keeping the data at the other location on a dedicate file server. This would save a fair amount of $$. There are also lots of other solutions like log shipping, replication, etc. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-03-10 : 15:58:07
|
| Solutions like [url="http://www.usdatatrust.com/demo/default.asp"] advertize proprietary software that does byte-level replication of SQL databases in real time. I can't imagine how this could be an efficent use of bandwidth, but they claim it is.Never ask a barber if you need a haircut. |
|
|
|
jason
Posting Yak Master
164 Posts |
Posted - 2005-03-10 : 16:21:10
|
| Like I said, expensive. I couldn't tell if the archive pricing was separate or rolled into the the storage level pricing. I think if you have a small DB, this would be a great way to go. I would want the option to keep a local copy of the backup though. |
|
|
|
|
|
|
|
|
New Server - New Recovery Plan