Knowing how to restore database files is not disaster recovery. Scheduling regular backups— while a “must do"— is insufficient for proper disaster-recovery planning in SQL Server environments. As a SQL Server administrator, you need to know how to develop disaster-recovery procedures and plans that properly protect a range of SQL Server–powered business processes—databases that range from “never go dark” to “restore when you can”. The good news? You don't need to know how to create an enterprise disaster-recovery plan. You need to focus on SQL Server protection and learn how to contribute SQL Server disaster-recovery methods to your company's more comprehensive business-continuity/disaster-recovery plan. Even if your company has no such plan, you want to be able to plan and execute a robust process for protecting SQL Server assets.
Just as performing SQL Server backup and recovery is different from restoring folders and files from a file server, SQL Server disaster recovery has unique aspects that—if not considered in a corporate disaster-recovery plan—can affect recovery and corporate viability. This two-part article touches on topics that SQL Server people should consider when contributing to an enterprise disaster-recovery plan, as well as practices they can adopt in their own SQL Server management techniques.
For our purposes, we’re going to apply our 7D Method to SQL Server disaster-recovery. Originally developed to help SQL Server administrators manage the lifecycle of their databases, the 7D Method is a systematic decide and execute process whose seven stages include Discover, Design, Develop, Deploy, Day-to-Day, Defend, and Decommission. For this exercise, we’ll use only the first five Ds. In this article, we'll cover Discovery and Design, and in Part 2, we'll show you how to Develop, Deploy, and maintain your SQL Server disaster-recovery plan in a Day-to-Day environment.
The End Is Near!
Disaster recovery can be a scary term. It implies horrible things happening: visions of tornadoes carrying your data center to Oz, or your rack of database servers sinking beneath floodwaters streaming down from the Mississippi River. In truth, such catastrophic events rarely occur. It’s much more likely that you’ll be brought down by the simplest of disruptive events—a power outage. Whether you’re facing fire, flood, tornado, blizzard, terrorist attack, pandemic, or a straightforward power outage, your job is to keep the servers operational and connected to the network.
All that being said, two big misconceptions surround disaster-recovery planning: first, that preparing for the worst kind of catastrophe properly prepares you to deal with any lesser disruptions, and second, that large enterprises are fine with the same disaster-recovery plan development that SMBs perform.
Figure 1 shows the levels of disruption and the relative frequency of occurrence—signified by the width of each band—that you might expect from minor, significant, serious, and catastrophic interruptions to your core processes. Notice the frequency of catastrophic interruptions. Catastrophes—interruptions lasting longer than 10 days—have historically made up a very tiny percentage (1 to 2 percent) of disruptions. The biggest sources of disruptions—those that cause up to a full day of downtime—are minor.
Figure 1: Sample interruption categories
When you’re building your plan, focusing on a worst-case scenario doesn't adequately prepare you for more common minor interruptions. A cascade of minor interruptions can be just as devastating as a single major event, including putting the average SMB on life support.
Focusing on SQL Server
If you work for a critical private-sector firm such as an infrastructure service provider (e.g., a power company), your company probably has a disaster-recovery plan in place. Your challenge is to dovetail your SQL Server disaster-recovery plan into that of the enterprise. Critical infrastructure companies are required to have contingency plans. Such is not the case for most small-to-midsized businesses (SMBs); SMBs rarely have responses to severe interruptions prepared in advance, nor do they have business-continuity specialists—or budgets. This article is aimed squarely at SQL Server administrators.
But you’re probably wondering how you can possibly cram yet another project into your already crowded to-do list. Sadly, for some of you, a SQL Server disaster-recovery plan might not be optional. If you’re an infrastructure service provider, financial institution, government entity, or health-care company—or if you provide critical services to these entities—you might have to devise a plan. Your company might be required by law or by contract to have a disaster-recovery plan.
The good news? Most of you are already performing SQL Server disaster-recovery activities with backups, standby and failover servers, log shipping, database mirroring, and security policies. These are tactical SQL Server disaster-recovery activities; now, you need to pull them together into a strategic plan for survival. If your organization already has a plan, you have something that you can integrate into and build on.
SQL Server disaster-recovery planning must be customized for each organization. Think about what contribution you and your team can make to crisis communication and management. What techniques can you develop and share for business-unit and technology-recovery procedures? What can you do to mitigate the impact of supplier and distributor failure? What ideas can you come up with regarding relocating and restoring critical IT infrastructure? What can you add to testing and validating incident recovery?