After completing the process of installing and configuring Microsoft SQL Server, any database administrator is entitled to sit back and take a well-deserved break. However, it's important to remember that the database administration process does not end when your database is humming along at a comfortable pace answering user requests. You considered a multitude of issues during the database design process -- performance, access controls, replication strategies, normalization and indexing to name a few. Have you taken the time to develop a contingency plan to cover unforeseen catastrophes that might threaten your database in the future?
Disaster recovery is all-too-often ignored until it's too late. Busy database administrators can easily push this task onto the back burner with promises to themselves that they'll "take care of backups right after I finish this big project." However, take a moment and consider the value of your data to your organization? What would be the result if an hour's worth of database changes were lost? A day's worth? What about a complete loss of the database?
More than likely, your answers to the previous questions illustrate the need for a comprehensive disaster recovery plan. They'll also form the starting point for the development of a plan tailored to the unique needs of your organization. In the remainder of this section, we'll look at the other factors to consider when developing a strategy for your organization. The next two sections will examine two prominent disaster recovery mechanisms offered by Microsoft SQL Server (and many other databases) -- database backups and transaction logs.
The first step in developing a solid disaster recovery plan is to develop an idea of what constitutes an acceptable loss for your organization. First, consider the impact of losing data stored in your database. Would you be able to recover from the loss of an hour's worth of data? If you're managing a human resources database, chances are that you could deal with this situation by instructing your personnel to reenter data entered during that period. If you're running the database supporting a financial institution, the loss of an hour's data could bring the instant wrath of clients and industry regulators along with significant monetary losses. Protection against these types of losses is provided by backups of the database and use of transaction logging. We'll discuss these in further detail.
Second, consider the loss of access to the database itself. What would be the ultimate result if your end users were not able to access information for an extended period of time. The loss of access to our human resources database would likely result in frustration, but minimal loss to the business. On the other hand, if doctors at a hospital were unable to access test results and laboratory findings in a timely manner it could result in the loss of life or limb. If you find that these issues are a concern in your organization, you may want to consider the use of failover clustering to ensure the continuous availability of your database. This concept will be discussed in a future article.
Once you've determined the level of acceptable loss for your organization and received buy-in from the users you support and your management, it's time to begin developing a strategy to minimize the impact of a catastrophic event on your database. Our first step is to develop a comprehensive backup strategy. We'll discuss this in the next section of this article. Read on!