Analyzing Availability and Recovery Requirements

In order to develop a successful backup and restore plan, you must understand when your data needs to be accessible and the potential impact of data loss on your business. Answering the following questions can help you determine your availability requirements and sensitivity to data loss. Then you can choose the correct Microsoft® SQL Server™ 2000 recovery models for your databases and make the necessary technical and financial tradeoffs.

Here are some basic questions to help you analyze your availability and recovery requirements:

• What are your availability requirements? What portion of each day must the database be online?
  
  
• What is the financial cost of downtime to your business?
  
  
• If you experience media failure, such as a failing disk drive, what is the acceptable downtime?
  
  
• In case of a disaster, such as the loss of a server in a fire, what is the acceptable downtime?
  
  
• How important is it to never lose a change?
  
  
• How easy would it be to re-create lost data?
  
  
• Does your organization employ system or database administrators? 
  
  
• Who will be responsible for performing backup and recovery operations, and how will they be trained?

Here are some questions to help you choose the tools, techniques, and hardware appropriate for your site:

• How large is each database?
  
  
• How often does the data in each database change?
  
  
• Are some tables modified more often than others?
  
  
• What are your critical database production periods?
  
  
• When does the database experience heavy use, resulting in frequent inserts and updates?
  
  
• Is transaction log space consumption likely to be a problem due to heavy update activity?
  
  
• Is your database subject to periodic bulk data loading?
  
  
• Is your database subject to risky updates or application errors that may not be detected immediately?
  
  
• Is your database server part of a SQL Server 2000 failover cluster for high availability?
  
  
• Is your database in a multi-server environment with centralized administration?

Managing Media

When you back up and restore a database, you need to back up the data onto media (for example, tapes and disks). It is recommended that your backup plan include provisions for managing media, such as:

• A tracking and management plan for storing and recycling backup sets.
  
  
• A schedule for overwriting backup media.
  
  
• In a multi-server environment, a decision to use either centralized or distributed backups.
  
  
• A means of tracking the useful life of media.
  
  
• A procedure to minimize the effects of the loss of a backup set or backup media (for example, a tape).
  
  
• A decision to store backup sets on or offsite, and an analysis of how this will affect recovery time.