Ensuring the availability of your database system and databases is one of the most important issues today. There are several techniques that you can use to ensure their availability, which can be divided in two groups: those that are components of the Database Engine and those that are not implemented in the database server. The following two techniques are not part of the Database Engine:
- Using a standby server
- Using RAID technology
The following techniques belong to the database system:
- Failover clustering
- Database mirroring
- Log shipping
- High availability and disaster recovery (HADR)
- Replication
The following sections describe these techniques, other than replication, which is discussed in a separate chapter “Data Replication”.
Using a Standby Server
A standby server is just what its name implies—another server that is standing by in case something happens to the production server (also called the primary server). The standby server contains files, databases (system and user-defined), and user accounts identical to those on the production server.
A standby server is implemented by initially restoring a full database backup of the database and applying transaction log backups to keep the database on the standby server synchronized with the production server. To set up a standby server, set the read only database option to true. This option prevents users from performing any write operations in the database.
The general steps to use a copy of a production database are as follows:
- Restore the production database using the RESTORE DATABASE statement with the STANDBY clause.
- Apply each transaction log to the standby server using the RESTORE LOG statement with the STANDBY clause.
- When applying the final transaction log backup, use the RESTORE LOG statement with the RECOVERY clause. (This final statement recovers the database without creating a file with before images, making the database available for write operations, too.)
After the database and transaction logs are restored, users can work with an exact copy of the production database. Only the noncommitted transactions at the time of failure will be permanently lost.
Note – If the production server fails, user processes are not automatically brought to the standby server. Additionally, all user processes need to restart any tasks with the uncommitted transactions due to the failure of the production server.
