Disaster Recovery Overview

Disasters come in all shapes and sizes. From natural disasters, such as tornadoes and floods, to hardware failure to accidentally dropping a table from a database. A disaster can strike at any time and how prepared you are can make all the difference. Given that, here are some things to ponder when thinking about disaster recovery.

To fully be prepared, you need to have a disaster recovery plan. This written checklist of everything that needs to happen in the event of an emergency will be a key to getting your organization up and running as quickly and efficiently as possible. Once something bad happens, people shouldn't have to think, they should be able follow a plan that has proven results.

Proven results means the plan has been thoroughly tested. You need to constantly test your disaster recovery plan. This provides several benefits. You have confidence in knowing your plan works. You are able to find any deficiencies in your plan, such as out-of-date material, and make the appropriate corrections. Finally, you gain experience in putting your plan into action, which, in the event of a real emergency, allows you to know what to expect.

When planning for an emergency, you should also see how much of the plan can be automated. As you learn later in this chapter, a good bit of SQL Server's backup and recovery can be automated using maintenance plans. Automation can save you time and prevent errors that occur from having to do something manually.

Given all this, let's briefly touch on what to think about when creating your disaster recovery plan. These considerations include an overview of the options that exist for backing up your data in Windows as well as when using third-party tools.

The Disaster Recovery Plan

A disaster recovery plan is a comprehensive, written plan of actions that need to be taken before, during, and after a disaster has occurred. It should be constantly tested, to ensure that all elements of the plan are still valid.

For a comprehensive recovery plan from a disaster affecting your business, you need to consider all the different areas of your organization and plan accordingly. Depending on the size of your business, this could be a massive undertaking. A good source to start with for this, and any disaster recovery needs in general, is Disaster Recovery Journal (drj.com). This publication offers a lot of free material related to disaster recovery, including sample plans, checklists, and other useful information.

Typically, a disaster recovery plan contains the following information:

• Information on location of backup hardware and software

• A communication list of people to contact in the event of an emergency, including people who will perform the recovery and those who will confirm the recovery works

• Some sort of organizational chart, so everyone involved knows who is responsible for what

• A list of the different recovery scenarios, along with a detailed checklist of each step required to perform each scenario

• A priority list of the recovery scenarios, listing which ones are most important, so that those involved in the recovery know which systems and scenarios have priority

This is by no means a comprehensive list, but it should give you an idea of some of the things to include in your plan.

Built-In Windows Backup Tools

Team Foundation Server runs on Windows Server 2003 and you can make use of the Windows Server 2003 Backup and Restore Wizard to configure your server backup options. To start the Backup and Restore Wizard, open a command prompt and type NTBackup. For in-depth information on how to use the Windows Server 2003 backup tool, refer to the following Microsoft TechNet Article: microsoft .com/technet/prodtechnol/exchange/guides/DROpsGuide/.mspx.

SQL Server 2005 has built-in tools for backing up databases and related items. We explore those tools later in this chapter.

Third-Party Backup Tools

A variety of third-party tools, including Symantec's Backup Exec, Legato's NetWorker, and Computer Associates' ARCserve, are available for backing up information. One of the major advantages of these tools is that they provide the ability to back up your database data as well as any other files related to your deployment. Of course, you can do the same thing with a combination of NTBackup and SQL Server 2005 tools. However, many of the third-party tools provide extra bells and whistles to make disaster recovery a much simpler process.

Disaster Recovery Applied to Team Foundation Server

Team Foundation Server disaster recovery can be relatively easy. All the data related to Team Foundation Server, including version control files, work items, and reports, is stored on the data tier of Team Foundation Server. Therefore, backing up Team Foundation Server is almost as simple as performing a database backup of the data tier. You also need to make a backup of the Report Server encryption key, which is not handled by a simple database backup. There may also be log files from other sources such as Internet Information Server that you want to save. These would also need to be backed up, if you wished to have them for historical reasons.

Given that all the data related to Team Foundation Server is stored in SQL Server 2005, it makes sense to spend a little time giving a basic overview of backup and restore strategies as related to SQL Server. This is by no means a comprehensive overview, but it is intended to give you a foundation for understanding the rest of this chapter. The online MSDN Library (http://msdn2.microsoft.com) is full of detailed resources concerning SQL Server 2005 backup and recovery, and would be a great place to learn more about the topic.

SQL Server 2005 Backup and Restore Overview

Let's start this section off by talking about recovery models in SQL Server 2005. A recovery model is just a database property. It controls how transactions are logged in the database, whether the transaction log file needs to be backed up, and which restore operations are available for that database. You can specify a different recovery model for each database in SQL Server 2005, and can change the recovery model at any point. There are three types of recovery models: simple, full, and bulk logged.

Important

A new database inherits its recovery model from the model system database.

In simple recovery mode, the transaction log is not backed up. After a database backup has executed, any transactions that have been committed from the transaction log are truncated, freeing up space in the log. Also, only minimum database information is retained to ensure database consistency after a database is restored. Simple recovery mode is the easiest to use, but it comes with some risk as well. You can recover data only up until the last backup. If you have a database failure twelve hours after your last backup, then you lose the past twelve hours of information. Given this, you should keep your backup intervals short enough to limit your losses, but long enough to keep the backup from affecting production. You can use a combination of full and differential backups to achieve this. A full database backup backs up all the information in the database. A differential database backup backs up only changes made to the database since the last full or differential backup. Best practices dictate this recovery model is not suitable for production systems where it is unacceptable to lose recent changes. As such, it is not recommended to use this model on your Team Foundation Server data tier.

Full recovery mode provides much better protection against data loss. In this mode, the transaction log must be backed up. By logging all transactions against the database to the transaction log and making backups of those logs, a database can be recovered almost to its point of failure. For example, you could schedule a full backup every night at midnight, and then a transaction log backup every hour. This would allow you to restore the database to within an hour of its failure, or better yet, if the transaction log can be saved from the failed server, you can restore the data back to the point of failure. Given the critical nature of the files stored within Team Foundation Server, this is the recommended model to use on your Team Foundation Server data tier. It is also the default for most databases in SQL Server 2005.

Bulk-logged recovery mode is a subset of full recovery mode. It is mostly used when doing huge imports or index creation to help with performance. You should never need to use this mode with Team Foundation Server.

To view the current recovery model of a database, open SQL Server Management Studio by going to Start⇨All Programs⇨Microsoft SQL Server 2005, and selecting SQL Server Management Studio. After establishing the database connection, expand the Databases folder on the left, and drill down to the database in question. Right-click the database and select Properties from the context menu to open the Database Properties window. In the Select a Page pane, click the Options button. You can see the current recovery model of the database displayed in the Recovery model drop-down list box. To change the model, simply choose a different option from the Recovery model drop-down list box.

After you know which recovery model you are using, you can set up a maintenance plan to perform backups of your databases. A maintenance plan is a series of scheduled tasks that run at specific times against the database server. Here is a list of some of the possible tasks that can be handled by a maintenance plan:

• Checking the integrity of the database

• Shrinking the database

• Rebuilding and reorganizing database indexes

• Cleaning up the database history

• Backing up the database, using either a full, differential, or transaction log backup

Later in this chapter you learn step by step how to set up a maintenance plan for backing up Team Foundation Server.