Chapter 8: Disaster Planning and Recovery

Almost everyone who has been in the data processing field for long has received a page or a call during dinner because of a disaster at work. Hopefully, you don't have to jump up from the dinner table wondering if you backed up the server. In this chapter, we'll explore different types of backup strategies that you can employ as well as the more important question of how to recover the backups.

Developing a Plan

The planning process of a backup and recovery strategy is one of the crucial parts of your career. Poor decisions here can end your career as fast as your server can lose its data. The first step is really a business question. What is the acceptable amount of outage time for your server? The common term for uptime is the nines. This ranking system sets the goal for the acceptable amount of outage in a year. Table 8-1 shows what this translates into annually.

Table 8-1: Availability

Classification

Percentage Uptime

Annual Downtime

One nine

90%

36.5 days

Two nines

99%

3.7 days

Three nines

99.9%

8.8 hours

Four nines

99.99%

53 minutes

Five nines

99.999%

5 minutes

Anything past three nines is almost impossible without a clustering solution, which we will cover in Chapters 10-12. Keep in mind that the larger the database, the longer it is going to take to restore. It's not unheard of to take an entire day to restore a large database and then another day to perform the constancy checks. Even with high-end clustering solutions, three nines may be aggressive. Consider that NASDAQ has reached a highly respectable 99.97 percent level of availability with 200 transactions a second.

Third-Party Backup Programs

Many third-party vendors produce excellent software to back up and restore databases and servers. As some of these software packages begin to back up SQL Server, they receive a sharing violation when they try to back up the databases. The only way around this is to stop the service before backing up the system, or to purchase an add-on agent to back up open files or SQL Server databases online. These agents can raise the price of software an additional $1,000 per server. Although I've had a good success rate restoring from the SQL Server agents, I have found the restore success rate is lower than just backing up the database through the SQL Server tools. Keep in mind that SQL Server is keeping these files open for a reason. If you want to obtain the safest complete system and OS backup, it's best to stop SQL Server. Otherwise, just back up the dump files.

Note 

Most backup packages have the capability to execute command-line programs (like net start) before and after the backup process.

I prefer to use these software packages for what they're geared for: backing up files, not databases. I like to back up my databases from a SQL Server agent job and then have the third-party software pick up my backup files. If your company insists on purchasing a third-party agent to back up the SQL Server databases, also back up the databases with SQL Server tools just in case.

Purchasing third-party tools for backing up databases may make sense with some of the tools on the market that compress and encrypt the backups. For example, SQLLiteSpeed (http://www.sqllitespeed.com/) and SQLZip (http://www.sqlzip.com/) both have extended stored procedures that compress a backup while it's being backed up. This reduces time, space, and overall CPU utilization. I saved roughly 80 percent of the space by using one of these products. While this worked great for me, make sure you test the solution before implementing it in your environment to make sure you're configuring it properly.

Caution 

Programs that claim to back up open files often back up the files, but leave SQL Server in a suspect state on restore. This is because they don't back up transactions 'in flight' properly. Be very wary of these programs!




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net