SQL Server 2005 - Disaster Recovery

SQL Server 2005 - Disaster Recovery

In this tutorial you will learn about Disaster Recovery in SQL Server 2005, Recovery Models, Managing Backup Media, Running a Base-Functionality Script and Ensuring Disaster Readiness.

Disaster Recovery in SQL Server 2005

Most enterprises have mission critical data which is the very backbone of the business. The loss of such data would be disastrous to the business and may even result in closure. In such circumstances it is not surprising that organizations are very concerned about Disaster Recovery procedures. SQL Server 2005 provisions for just such a scenario by helping the DBA create a Disaster recovery plan and put in place recovery steps that can be tested for efficiency long before the disaster actually strikes.

Disaster Recovery plans hover around concepts such as time to recover. The Time to recover concept predicts the time required for an organization to put its database back into an active state, after the disaster strikes. This could range from 48 hours to a week. The structuring of the plan would also require some thought. It could include plans to acquire hardware, communication plans, and list of contacts, instructions to disaster recovery personnel and information to plan administrators and so on.

SQL Server 2005 supports clustering, backup and restore and log shipping as means of disaster recovery. It adds a number of enhancements to the features available in the earlier versions.

Recovery Models

At the core of any Disaster Recovery Plan is the recovery model that has been envisaged. Microsoft SQL Server 2005 provides the DBA with three kinds of recovery models—full, simple and bulk-logged. The recovery model determines the basic behavior of the back up and restores operations that will be put in place for the database. This in turn depends on the availability and recovery requirements of the organization.

Managing Backup Media

Backup plans must include methodologies for managing backup media. DBAs must design a tracking and management plan for recycling backup sets, a schedule for overwriting backup media, decide on whether to use centralized or distributed backups, set up the means of tracking the useful life of media, define a procedure to minimize the effects of loss of backup sets and take decisions to store backup sets offsite and the impact on recovery time.

Running a Base-Functionality Script

A base functionality script is used as part of the disaster recovery plan to ensure that everything is working as intended. It is application specific and can take different formats. It may be a .sql script file or a .bat file containing osql or bcp commands or both. It provides a tool for the system administrator or database administrator to verify whether the database has been restored to its viable state without end user intervention.

Ensuring Disaster Readiness

Ensuring Disaster Readiness is part of the DBAs Disaster Recovery plan. The backup and recovery procedures have to be thoroughly tested, defined and documented for smooth execution. Regular database and transaction log backups have to be performed to minimize the amount of lost data. It is advisable to maintain system logs in a secure fashion and keep a list of service packs installed on Microsoft Windows or SQL Server. Network libraries have to be recorded and security modes and sa passwords have to be stored. A simulation of the recovery steps should be done on a separate server and the base functionality script has to be maintained to ensure minimum capability assessment.

1. Procedure for recovering from disaster
2. Acquire the necessary replacement hardware
3. Install the Microsoft Windows platform and apply the service pack.
4. Verify the domain functionality
5. Install Microsoft SQL Server and apply the appropriate Service pack.
6. Restore the master database backup and the msdb database backup.
7. Restart the server.
8. Reconfigure the server for appropriate network libraries and security modes.
9. Run the SQL Server Configuration Manager and ensure that the server is running as desired.
10. Check the Windows application log and ensure that the computer name has not changed. If it has changed use the sp_dropserver and sp_addserver commands to correlate it with the SQL Server computer name.
11. Restore and recover the databases as per recovery plan.
12. Verify the availability of the system by running the base functionality script.
13. Permit users logon and operate on the database.