July 1, 2008 at 10:50 am
We are purchasing a new SQL Server for production, to replace an older one that has limited storage space.
At my previous employer, we had a DR plan that required database restores within 60 minutes of any problems. Anything over a week allowed for more time. We made the decision to have enough DAS (direct attached storage) in the server to store five days worth of database backups and enough DAS in another server where the logs were stored (again, five days worth). We had a tape backup to keep the database backups and log backups long-term, off-site.
Currently here, a tape backup does run nightly, just after the full backup for the previous day is run. Then, the database backup is deleted from the drive due to the aforementioned limited space issue.
In specing out our new server, including my best estimates for growth over the next five years (a real guess since I have only been here for four months), I determined that to maintain a similar plan to my previous employer (five days immediately accessible), we would need 3+TB of available space. I have suggested a SAN solution which introduces the issue of cost.
My question, to start more of a discussion, is to find out what everyone else does to establish an industry "best practice" so I can either justify my request or make an educated adjustment to what I am asking for. Of course, part of this question would be to address the DR issue. Is the "best practice" based largely on our DR plan (which seems to be nonexistent at this point)?
July 3, 2008 at 6:42 am
Jim,
I understand what you are talking about. It's never fun to break new ground when it comes to DR. Of course, since you are the new DBA this affords you the ability of a fresh perspective. As a DBA, I am totally on board with the plan you've outlined. Sure there is a cost factor, but is the amount invested worth the payoff in a recovery situation? Make sure that you do your best to find out how much money would be lost if the application(s) would be down for a day versus an hour. Also, as I am sure you are aware, documenting the DR plan is tantamount to success!
Okay, and to let you know what I do. We take one full backup per day and Transaction Logs every 15 minutes during high utilization periods and then 30 minutes during low periods after that. We have enough local storage to hold 2 full days locally on the Server. Those files are redundantly copied to a file Server after the backup has completed, and additionally to a 60-day tape rotation.
My worst case recovery time, minus hardware failure, is 2.5 hours with a max of 30 minutes data loss. Barring natural (flood) or man made (fire) disaster, with a total loss of 75% or more hardware, we are looking at an 8 hour max with an 8 hour data loss. However, I hope to reduce that with the implementation of a Hot Site, but that costs money too. 😉
Regards, Irish
July 7, 2008 at 1:30 pm
As Jeffrey said, it does come down to how important the data is, and how important it be that the data be available. Importance has a direct relationship with the company's bottom line. In most companies the powers that be all think their system is the most important and needs to be up immediately in the event of a disaster, be it physical, or data corruption.
Once those powers are actually questioned about what will happen if their system is down for an hour, a day, or a week, it becomes clearer that most of the internal systems aren't really that critical. The obvious exceptions are out-facing systems the customers deal with. THOSE are the most important systems. Or AP system, while important, is not going to affect the company's bottom line if you can't pay your bills for a week. The HR sysem can be down for a week too, if you can make payroll on time (either it just ran before the disaster or is outsourced).
What I am saying is that the actual recovery requirements must be carefully scrutinized before a recommendation is made. This may keep you from having to "downgrade" the plan a bit once management hears the price tag for that level of recovery. They may still ask you to downgrade if the price is still too steep for them to stomach.
Good documentation is a must, but testing the plan you create will be as important as documenting the plan.
Here, we keep a rolling 2-5 days on NAS (depending on the system) and 30 days on tape, with one or more days per year held over on tape.
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply