Disaster recovery refers to the ability of a company to continue operations after an event interrupts normal business functions. For example, many businesses in New York City faced considerable disruption during the power failure in the summer of 2003, and of course in the aftermath of September 11, 2001.
Business continuity is critical for a company's viability, especially for RDBMs as according to Gartner Inc., (Online Server Backup: Niche, Glitch or Killer App? by Adam W. Couture, January, 2002) a market research firm in Stamford, Conn:
50% of companies would close within two years if their data is not restored within 24 hours after a disaster occurs.
As many as 95% would close within two years if their data is not restored within 72 hours after a disaster. Gartner's research further reveals that a whopping 77% of backups are unusable when needed.
According to other reports,(Enterprise Storage Group, "The Evolution of Enterprise Data Protection", January 2004) 25% of 500 companies surveyed will face a significant data disruption event.
Not having any disaster recovery plans in place is essentially playing Russian roulette with your company's future – let alone your own job. It is essential that whatever disaster recovery plan you implement, you test it and ensure that all people incorporating the disaster recovery operation are well practiced at the plan, in order to minimize downtime. Not having tested your disaster recovery plan means you really only have a "disaster recovery hope" (to quote SQL Server MVP Geoff Hiten).
Disaster recovery goals vary from industry to industry:
Some companies can tolerate less data loss than other companies; most financial institutions can tolerate no data loss.
Some industries have very stringent requirements on availability but more relaxed requirements on data loss; some media outlets must provide continuous service in the event of a disaster as the public turns to them for news about the disaster. But they can tolerate data loss, as everyone is interested in new news (of the disaster), not old news, and the same news about the disaster is continually entering their system.
Some companies only require essential business functions restored and other companies require plans for their entire enterprise; I consulted for one retailer whose DR plans do not include their ecommerce site.
In general the goals are to:
Minimize disruptions.
Minimize the extent of disruption.
Minimize the economic impact of the disruption.
Have a disaster recovery site with all dependencies in place to provide failover.
Have personal trained personnel and practiced to provide business continuity from the disaster recovery site.
Provide for smooth and rapid restoration failback to the normal place of operation.
SQL Server 2005 has expanded the portfolio of options for disaster recovery in SQL Server 2005. The following components are available in SQL Server for Disaster Recovery:
Mirrored backup media sets
Backup and restore
Log shipping
Database mirroring
Replication
New in SQL Server 2005 is the ability to do mirrored tape backups. You can simultaneously write your backup to up to four tape devices. This means you could backup your database locally to your multiplex tape unit, while at the same time write your backup to your DR site. The slowest link will throttle your overall throughput of your backup solution, so the WAN link to your DR site may slow down the speed of your local backup, if you are using mirrored tape backups. If you use mirrored backups, all of your backup devices must use identical hardware. For more information on this feature consult Microsoft's article Using Mirrored Backup Media Sets.
Mirrored backup media sets can provide complete point in time recovery and can be used with very large databases (VLDBs). SQL Server 2005 will also allow for log backups to occur during a database backup. In SQL 2000, these log backups were paused until the full-backup
was done. If you were required to do a point in time recovery of your database that crashed during a backup, your exposure to data loss could be significant. The reason being you would have to restore the last backup (yesterday's perhaps), and then the transaction log backups that occurred since the last backup completed, and until the next backup started. Consider a database backup starts at midnight and takes eight hours to complete and the transaction log dumps every hour. Should a database failure occur at 7:30 a.m., you would have to restore yesterday's backup as well as all the transaction log backups. The latest time you could restore your database to would be yesterday at 11:00 pm. In SQL Server 2005, the latest you could restore your SQL Server 2005 database to would be 7:00 am this morning.
SQL Server 2005 advantages
Multiple copies of your backup for redundancy.
Less exposure to data loss with the ability to do log backups during a full database backup.
When to use mirrored backup media sets
When you have VLDBs with point in time recovery requirements, have a high bandwidth link to your DR site and identical tape devices in all locations.
SQL Server 2005 offers many new features for DR plans that use database backups and restore. If your disaster recovery goals are not time critical (you don't need point in time recovery), a disaster recovery plan using a backup and restore of a full, differential, or partial database backup can be the optimal choice.
For smaller databases, a full or differential backup may satisfy all of your DR goals, however, if your databases get larger you may need to incorporate a partial backup strategy. This would involve backing up your read-only filegroups of files on a different frequency than your read-write file groups. In SQL 2000, such a strategy would require a complete set of all read-write file group and log backups since you performed your last read-only file group backup. This requirement is relaxed in SQL 2005 and you will only need the last backup of the read-only file group, the last backup of the read-write file group(s), and the log backup chain since the last backup of the read-write file group.
Consult the Microsoft article Performing Piecemeal Restores for more information on this feature.
SQL Server 2005 also has the ability to continue to backup or restore a database transaction log, or file group after a failure of the database media or the backup media. This means that if your database files get damaged you may still be able to backup the tail of the log, or if your database backup is damaged you will be able to continue the restore after the error has been detected.
Consult the article How to: Specify Whether BACKUP Continue or Stops on Encountering an Error for more information on this feature.
SQL Server 2005 advantages
More granular backups and restores.
Ability to continue the backup or restore of a database and transaction log, should a failure occurs in the database files or backup media.
If your DR plan requires point in time recovery log shipping may be your best option. Log shipping is a continuous backup and restore. However log shipping does have significant exposure to data loss. At best, this exposure is the shortest length of time required to complete your transaction log dump and copy it over to the standby server.
Use log shipping when you have point in time recovery requirements and you have a small number of databases you need to log ship. Log shipping is not scalable to large databases or a large number of databases because the bandwidth requirements are large and log shipping can become complex when you are log shipping large numbers of databases. SQL Server 2000 has a log shipping wizard in the Enterprise Edition, but this wizard is now included in all editions of SQL Server 2005 (except the express and CE editions).
SQL Server 2005 advantages
The wizard now ships in all editions of SQL Server (except the express and CE editions).
The ability to continue to dump the transaction logs during a database backup will reduce your exposure to data loss.
When to use log shipping
Is generally used for small numbers of databases or small databases.
Use when you do not have real time synchronization requirements, and can tolerate some exposure to data loss.
Replication will replicate all or selected transactions which occur on your source server to your destination server (DR site). It can also modify the data as it is replicated to the DR site.
Replication will require SQL Server licenses on the SQL Servers in your DR site. Log shipping and a backup and restore solution will not, as long as the SQL Servers solely have a standby role. Replication will not detect the addition of new database objects or logins.
Replication can be configured in a DR topology for easy failover and failback, however, in SQL 2000 you will need to do significant work to set it up, and it is not resilient to schema modifications.
SQL Server 2005 will replicate most DDL (Data Definition Language) transparently, and the peer-to-peer replication model allows for a configuration of a DR node in a write-anywhere topology. However, it is not really resilient to conflicts or schema changes--Microsoft recommends that all schema changes and DML occur on one node in the topology. Replication generally offers very low latencies, however, under some database loads the latency can exceed that of log shipping. Transactional replication is included in all SQL Server 2005 Editions (except express and CE editions); peer-to-peer is only supported on SQL Server 2005 Enterprise Edition.
SQL Server 2005 advantages
Peer-to-peer replication model simplifies the creation of bi-directional nodes with simple failover and failback.
The ability to replicate some DDL changes to your replication topology simplifies the maintenance of your replication solution.
When to use replication
Replication can offer lower latencies and can provide lower exposure to data loss than log shipping or a backup and restore DR solution.
Replication can replicate a subset of your data and can modify it to have different values or a different schema on the DR site.
SQL Server 2005 offers database mirroring which ships in the Standard and Enterprise Editions. Database mirroring is continuous log shipping and can have very low latencies. It has two modes high safety (essentially a split write) and high performance which is an asynchronous commit operation on the DR site. High safety does have larger latencies than high performance, but your exposure to data loss is extremely low, if at all. To make database mirroring in high safety mode work optimally, Microsoft recommends you have a very good network in place, have long running transactions and have many connections to the database. Database mirroring can also be configured for automatic failover. When used in a DR capacity, it will require licenses in place on both the production and DR site.
Database mirroring does consume host-based resources and is not scalable to large numbers of databases on a single server. It can only be used in full-recovery model.
SQL Server 2005 advantages
Database mirroring was not a feature of SQL Server 2000, and is essentially real time log shipping, and as such offers lower exposure to data loss than log shipping and lower latencies than all of the technologies discussed above.
Database mirroring can offer automatic failover which is generally not part of a DR solution; however it does not have a distance limitation like clustering.
When to use database mirroring
Use database mirroring when your DR goals include very low latencies, lower than log shipping or replication.
Summary
In this article we've looked at some technologies included in SQL Server 2005 that can be integrated into a Disaster Recovery plan. Each technology has its own strengths and limitations, and one technology may be a better fit for your DR goals.
| ABOUT THE AUTHOR: |
|
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
Copyright 2007 TechTarget
|
|
SQL Server disaster recovery plan goals
