Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Description of disaster recovery options for Microsoft SQL Server
Article ID: 822400 - View products that this article applies to.
This article discusses various solutions for recovering data from a Microsoft SQL Server database, if a disaster occurs. This article also discusses the advantages and the disadvantages of each solution.
Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs.
Some examples of disasters include a natural or a man-made disaster such as a fire, or a technical disaster such as a two-disk failure in a Redundant Array of Independent Disks (RAID) 5 array.
Disaster recovery planning is the work that is devoted to preparing all the actions that must occur in response to a disaster. The planning includes the selection of a strategy to help recover valuable data. The selection of the appropriate disaster recovery strategy depends on your business requirements.
Note The solutions that are discussed in this article only provide general descriptions of the technologies that you can use. These general descriptions are for comparing the various disaster recovery methods and the disaster recovery plans. Before you decide on which disaster recovery solution is best for you, make sure that you look at each of the suggested disaster recovery solutions in more detail. After discussing each disaster recovery solution, this article contains links where you can find additional information about that solution.
Failover clusteringMicrosoft SQL Server 2000 failover clustering is designed to failover automatically if a hardware failure or a software failure occurs. You can use SQL Server 2000 failover clustering to create a failover cluster for a single instance of SQL Server 2000 or for multiple instances of SQL Server 2000. Failover clustering allows a database system to automatically switch the processing of an instance of SQL Server from a failed server to a working server. Therefore, failover clustering is helpful if an operating system failure occurs or if you perform a planned upgrade of the database system resources. Also, failover clustering increases server availability with no downtime.
Because failover clustering is designed for high server availability with almost no server downtime, the clustered nodes should be geographically close to each other. Failover clustering may not be useful if a disk array failure occurs.
Note To implement failover clustering, you must install Microsoft SQL Server 2000 Enterprise Edition.
The following operating systems support failover clustering:
For more information about MSCS and its installation, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/259267/ )Microsoft Cluster Service installation resources
Advantage and disadvantages of using failover clustering
You have high server availability. Failover clustering automatically occurs if the primary server fails.
http://msdn2.microsoft.com/en-us/library/aa174512(SQL.80).aspxFor more information about failover clustering, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/243218/ )Installation order for SQL Server 2000 Enterprise Edition on Microsoft Cluster Server
822250For more information about the Microsoft Support Policy for a SQL Server failover cluster, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/822250/ )Support WebCast: Microsoft SQL Server 2000 failover clustering disaster recovery procedures
(http://support.microsoft.com/kb/327518/ )The Microsoft support policy for a SQL Server failover cluster
Database mirroringDatabase mirroring is a primarily software solution for increasing database availability. You can only implement mirroring on a per-database basis. Mirroring only works with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.
Advantage and disadvantages of using database mirroring
Peer-to-peer transactional replicationPeer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain identical copies of the data.
Advantage and disadvantages of using peer-to-peer transactional replication
Maintenance of a warm standby serverYou can create and maintain a warm standby server by using either of the following methods:
Log shippingLog shipping is included in the resource kit for Microsoft SQL Server 7.0, and it is fully incorporated in the Microsoft SQL Server 2000 Enterprise Edition and in the Microsoft SQL Server 2000 Developer Edition. Log shipping uses a standby server that is not used during regular operations. A standby server is useful to help recover data if a disaster occurs. You can only use log shipping at the database level. You cannot use it at the instance level.
When a standby server is restoring transaction logs, the database is in exclusive mode and it is unusable. However, you can run batch reporting jobs between transaction log restorations or Database Console Commands (DBCC) checks to continuously verify the integrity of the standby server. For applications such as decision support servers that require continuous processing on a database server, log shipping is not an appropriate option.
The latency on the standby server is based on how frequently the transaction log backups are taken at the primary server and then applied at the standby server. If the primary server fails, you may lose the changes that were made by the transactions that occurred after your most recent transaction log backup.
For example, if transaction log backups are taken every 10 minutes, transactions during the most recent 10 minutes may be lost. This does not necessarily mean that the data updates that are made to the primary server during the latency period will be lost. Typically, new updates in the primary transaction log can be recovered and applied at the warm standby server with only a small delay in switching from the primary server to the standby server. The main purpose of log shipping is to maintain a warm standby server. If maintaining a warm standby server is your main objective, log shipping is likely to be more appropriate than the other solutions that this article discusses.
Advantages and disadvantages of using log shipping
For more information about how to set up a warm standby server by using log shipping, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/323135/ )Microsoft SQL Server 2000 - How to set up log shipping (white paper)
325220For more information about log shipping, visit the following Microsoft Web sites:
(http://support.microsoft.com/kb/325220/ )Support WebCast: Microsoft SQL Server 2000 log shipping
Transactional replicationYou can also use transactional replication to maintain a warm standby server. Transactional replication replicates the data on one server (the publisher) to another server (the subscriber) with less latency than log shipping. You can implement transactional replication at the database object level such as the table level. Therefore, Microsoft recommends that you use transactional replication when you have less data to protect, and you must have a fast recovery plan.
You can use a push subscription to enforce transactional replication between two servers with the primary server as the publisher and the standby server as the subscriber. Transactional replication ensures data replication. When the publisher fails, the subscriber can be used.
This solution is vulnerable to the failure of the publisher and the subscriber at the same time. In such a scenario, you cannot protect the data. In all other scenarios such as the failure of a distributor or a subscriber, it is best to resynchronize the data in the subscriber with the data in the publisher.
You should use transactional replication to maintain a warm standby server only when you do not implement schema changes or you do not implement other changes to the database such as security changes that replication does not support.
Note Replication is not designed for the maintenance of warm standby servers. With replication, you can use replicated data at the subscriber to generate reports. You can also use replication for other general uses without having to perform processing on the relatively busy publisher.
Advantages and disadvantages of using transactional replication
(http://support.microsoft.com/kb/195757/ )Frequently asked questions - SQL Server 7.0 - Replication
Backup and restore featureThe Backup and Restore feature of SQL Server provides an important safeguard to help protect critical data that you store in SQL Server databases. You can create a copy of a database (a backup copy) by using the Backup and Restore feature, and then store the copy of the database in a location that is protected from the potential failure of the server that runs the instance of SQL Server. If you experience a database system failure or database corruption, you can then use the backup copy to re-create the database or to restore the database.
When you plan disaster recovery by using the Backup and Restore feature, also determine how critical the data in the database is. Additionally, determine the restoration requirements for the database. For example, determine the following restoration requirements:
You can only restore a database to the condition of the point of time where you performed the most recent backup. Transactions that occurred after that backup may be lost. Therefore, Microsoft recommends that you use the Backup and Restore feature only for non-mission-critical database applications.
Advantages and disadvantages of using the backup and restore feature
For more information about the Backup and Restore feature, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/325257/ )Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
281122For more information about the Backup and Restore feature, visit the following Microsoft Web sites:
(http://support.microsoft.com/kb/281122/ )Description of restoring file and filegroup backups in SQL Server
Disk redundancy of data by using a redundant array of independent disks (RAID)A RAID stores redundant data on multiple disks to provide greater reliability and less downtime for servers. RAID levels 0, 1, and 5 are generally used as recovery options for SQL Server. The RAID technologies that are mentioned allow for the failure and the consequent replacement of a single disk without the server going offline. If multiple disk failures occur, data may not be recoverable. Therefore, Microsoft recommends that you combine redundant data management with a Backup and Restore procedure to help make sure that you do not lose data if a hardware failure or other disaster occurs.
RAID 0 uses striping technology for faster access whereas RAID 1 uses mirroring technology for data reliability. A common technique used in relational database management involves using RAID 0 and RAID 1 together. In this technique, two identical striped arrays of drives are constantly updated so that the information that is stored on both the arrays is the same. If one array fails, the other array automatically takes over until the original array is brought back online.
RAID 5 (also known as striping with parity) uses a single striped disk array with parity bits written together with the data. When any one disk fails, the parity bits can be used to calculate the missing data until you replace the disk. When you replace the disk, you can use the parity information and the remaining data to re-create the data from the failed disk and to copy the re-created data to the new disk. All these operations occur without database system downtime. A RAID provides many other options and features for you to help make sure that your database systems experience as little downtime as possible.
Advantage and disadvantages of using a RAID
You do not lose data if any one disk fails.
(http://support.microsoft.com/kb/100110/ )Overview of redundant arrays of inexpensive disks (RAID)
To download an updated version of SQL Server 2000 Books Online, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5For more information about other disaster recovery options, click the following article number to view the article in the Microsoft Knowledge Base:
307775For more information about failover clustering, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/307775/ )Disaster recovery articles for Microsoft SQL Server
(http://support.microsoft.com/kb/195761/ )Frequently asked questions - SQL Server 7.0 - Failover
(http://support.microsoft.com/kb/260758/ )Frequently asked questions - SQL Server 2000 - Failover clustering
(http://support.microsoft.com/kb/274446/ )Upgrade to SQL Server 2000 failover solution recommended for all non-SQL Server 2000 virtual servers
280743For more information about the Backup and Restore feature, visit the following Microsoft Web site:
(http://support.microsoft.com/kb/280743/ )Windows clustering and geographically separate sites
http://technet.microsoft.com/en-us/library/cc966495.aspxFor more information about the Backup and Restore feature, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/253817/ )How to back up the last transaction log when the master and the database files are damaged in SQL Server
314546For more information about full-text catalog folders and files, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/314546/ )How to move databases between computers that are running SQL Server
(http://support.microsoft.com/kb/240867/ )How to move, copy, and back up full-text catalog folders and files
Article ID: 822400 - Last Review: July 3, 2012 - Revision: 6.0