SQL Server 2000 Administration in 15 Minutes a Week: Creating a Disaster Recovery Plan (Part 2)

Friday Jun 28th 2002 by Michael Aubert
Share:

Michael Aubert's ninth article in the SQL Server 2000 Administration in 15 Minutes a Week series concludes a discussion on preparing a disaster recovery plan.


Welcome to the ninth article in my SQL Server Administration in 15 Minutes a Week series. Last week we started to create a disaster recovery plan. This week we are going to finish working on our disaster recovery plan. The topics for this week include:

- Creating a Disaster Recovery Plan - Continuation


Creating a Disaster Recovery Plan - Continuation

Last week we decided on how often to backup our database; now we must decide where to backup our database. The first option available is to backup directly to tape from SQL Server. On the plus side, a backup to tape allows for off-site storage of backups. On the down side, tape backups are slow and therefore can impact the server for a longer amount of time than a backup to a file. The other common place to backup to is a file. File backups are much faster than a tape backup, for both backup and restore operations; however, backups to files don't allow for quick off-site storage (unless you happen to have a high speed remote link).

A third option is to use a combination of making backups to a file and then using another backup utility, such as NT Backup, to copy the file backups to tape. By making backups to files on another server nearby and then copying the files to tape, you can minimize the time a backup will impact your SQL Server while still allowing for off-site storage of tapes. Also, if you need to make multiple copies of tape backups, using another computer for copying backups to multiple tapes can help even more.

Another thing to think about when you are choosing where to make a backup is the time it takes to restore a backup. For example, a backup that is stored on another computer could be restored much faster over a high speed network than it could be from a tape drive. To take advantage of this faster recovery you may consider saving file backups for the week on another computer (in addition to your tape backups). In the event your SQL Server crashes you have a current backup on hand and available at a faster speed than tape.

File and tape backups do provide for lots of flexibility in designing your disaster recovery plan, but there are still many options available from third party venders. For example, you can find utilities that make the process of making backups of multiple SQL Servers very simple. You may also consider a Storage Area Network for large mission-critical systems. Although I would love to cover every option available, other backup utilities and hardware options are outside the scope of this series.

Before we can move on, there is a second half to deciding where to backup...choosing a tape, or file for that matter, rotation. If you used a new tape for each day's backup you would probably eat up a good part of your budget on nothing but tapes. In order to save money a few popular tape rotation schemes are in use. The rotation we are going to look at is known as the Grandfather-Father-Son rotation. Let's look at our example from last week to see how this rotation works (note: I have rearranged the order of the days from the last article):
 

MON TUE WED THU FRI SAT SUN
12:00 AM             FULL
1:00 AM              
2:00 AM DIF DIF DIF DIF  DIF DIF  
3:00 AM              
             

In a Grandfather-Father-Son rotation you start out by using a new tape for each day of the week for the first week. For each week following the first week you reuse the same tapes except for the last tape of the week. By using a new tape at the end of each week you can keep an archive of data. In the event you need to restore data that was deleted or lost, the archive from past weeks is available. Once a month has gone by you keep the tape for the last week of that month and then reuse the end-of-week tapes. Here is what a Grandfather-Father-Son rotation would look like for our example over a two month period:

11 Tapes are used: M, T, W, TH, F, S, W1, W2, W3, Month1, Month2...
 

MON TUE WED THU FRI SAT SUN

Week 1

M T W TH F S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S Month1

 

MON TUE WED THU FRI SAT SUN

Week 5

M T W TH F S W1
Week 6 M T W TH F S W2
Week 7 M T W TH F S W3
Week 8 M T W TH F S Month2

In our example we must take the monthly backup on Sunday because that is the only day we make a full backup. However, if you make a full backup of your database every day of the week you can use the monthly tape on the last day of the month no matter what day of the week it ends on. To illustrate, this is what a Grandfather-Father-Son rotation would look like if we took a full backup every night for the next two months:

Note that a new tape is substituted for the last day of each month. Also note that once the last day of the month has passed, the end-of-week tapes can then be reused.

May 2002, Wednesday 1st - Friday 31st:

MON TUE WED THU FRI SAT SUN

Week 1

    W TH F S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S W4
Week 5 M T W TH Month1    

June 2002, Saturday 1st - Sunday 30th:

MON TUE WED THU FRI SAT SUN

Week 1

          S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S W4
Week 5 M T W TH F S Month2

Confused yet? :)


Page 2: Disaster Recovery Plan (Continued)


 » See All Articles by Columnist Michael Aubert


Having decided on how often to backup and where to backup, you now must choose a location to store your tapes. Deciding on a location is going to depend greatly on your situation, but there are some general rules you should keep in mind. First of all, the location, either on or off-site, should be secure! If someone has access to your tapes...you might as well give them access to your server. Second, you need to find a balance between keeping the most recent tapes nearby (in case you need to restore a database) vs. the need to store tapes off-site (in case of a disaster).

One approach that we talked about earlier is storing a file backup on a second computer from your SQL Server. By doing this, not only can you recover from a crash faster, but it also allows you to store tapes off site without having to worry about going to get them to restore a database. You can also accomplish this same effect, minus the faster restore time, by making two sets of tape backups; one off-site for storage and one on-site for quick access. You may even consider making a third copy of your monthly backup for storage in a third off-site location. Also keep in mind that you don't have to keep all your tapes off-site. Depending on your needs, you may find that keeping the weekly backups, or even the monthly backups, off-site is adequate.

Before I move on, there is one last issue with backups that I would like to cover -- choosing someone to swap tapes. Once you have your plan in place you should designate a single person responsible for checking that backups took place and that tapes are swapped out as needed. It is important to have one person do this because when multiple people share the responsibility you end up with: "I thought you swapped the tapes last night?!?" When a single person is responsible for backups it becomes a routine for them. Now, if that one person is unable to swap tapes (ex: they get sick) it is their responsibility to find another person to swap tapes for them. Although another person may do the job of swapping tapes now and then, you still have the accountability of the single person who normally does the tape swapping, not a group of people. If the job is too big for one person, consider giving the responsibility for half the servers to one person and half to another person (or however you would like to split them up). Additionally, if you can't have the same person swapping tapes every day (ex: you take backups on the weekend), make it clear who is responsible for what days and keep the days the same from week to week.

So, having decided how often to backup, where to backup, and where to store our backups...what's left in a disaster recovery plan? Well quite a lot, but most of it is highly dependent on your environment. The first step is to document, document, and document. Get a folder and dedicate it to your disaster recovery plan. Here are some things that you should include in your plan:

- Server Hardware Specifications
- Network Layout
- Server Software Configurations
- Database File Layout (i.e. log files and data files)
- Label your tapes and include a backup and rotation description

The next step is to start thinking about, and write down, what should happen if a failure occurs. Keep in mind when you start to write out the plan, you should assume that you are not on-site and are unable to come to the rescue. You should also assume that the person restoring the server does have technical knowledge about SQL Server, but knows nothing about your particular setup. Think about things like:

- Who should be contacted if something goes wrong?
- Where are the backups stored?
- Where are the software and driver disks stored?
- Are there any tech support numbers available?
- If new hardware is required, what should be done?
- Is there any other information that may be useful?

Once you have completed documenting what should happen if a disaster occurs, there is one final step that you must complete...testing your plan. Having a plan is not enough, you have to test to see if your plan has all the necessary information, if your backups work correctly, and if everyone knows what to do. In order to do this you should setup a fake disaster. Now, don't go lighting your servers on fire (we all know how tempting that can be sometimes!), but use some extra hardware to test your plan. Don't worry about getting exactly the same setup (hardware wise), you will need just enough to run the services and any client applications. When testing, you should follow your disaster recovery plan and see if all the information is available in the plan. If you left anything out, or something was wrong, now is the time to make corrections and additions. By using the test hardware you not only get a feel for what information needs to be in your plan, but you are also able to test your backups by restoring the server from tape.

Next week we will look at how to restore a database from backup so we can continue to test our disaster recovery plan. We should also be finished with backup/restoring databases next week and then we can move on to some more interesting stuff! :) As always, if you have any technical questions please post them on the SQL message board. Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.

Mike
maubert@databasejournal.com
www.2000trainers.com


» See All Articles by Columnist Michael Aubert


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved