High Availability: Frequently Asked Questions

SQL Server 2005 High Availability is a set of best practices and technologies designed to increase database availability through backups that help recover from catastrophic disasters, redundant database components engineered to prevent downtime, and redundant server implementation designed to minimize downtime.

Database mirroring is a SQL Server 2005 technology for increasing database availability. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server. You can code client applications to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database. Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. Database mirroring, however, can fail over quickly with no loss of committed data, does not require proprietary hardware, and is easy to set up and manage.

Database mirroring is a simple strategy that offers the following benefits:

In database mirroring, an originating SQL Server 2005 instance continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. The originating database and server have the role of "principal", and the receiving database and server have the role of "mirror". The principal and mirror servers must be separate instances of SQL Server 2005. In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database's log buffer in memory, and then flushed to disk (or 'hardened') as quickly as possible. In database mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance. When the mirror server receives a block of log records, it places the log records first into the mirror database's log buffer and then writes them to disk. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal's transaction log records, it duplicates the database changes on the principal database.

There are three possible operating modes for a database mirroring session. The exact mode is based on the setting of transaction safety and whether a witness server is part of the mirroring session.

If safety is FULL and a witness is set, synchronous data transfer will occur, and a quorum is required for database service. A quorum vote requires at least two servers to decide which role, principal or mirror, each of the two partner servers should play.

Not at all. Database Mirroring does not require proprietary hardware and it can be configured on any hardware that runs SQL Server 2005.

Yes, the witness can be on a cluster. However, having the witness on a cluster is not required. Your experience with clustering should determine whether to use clustering. Note that the witness is not the most important member of the database mirroring session. The witness just answers the question: “Who do you see?” When the partner servers cannot see each other, the partner servers contact the witness to see whether the witness can contact the other partner and verify that a failure has occurred.

Database Mirroring is easy to set up and monitor. You can use T-SQL command or the graphical tools (like wizards and dialogs) in SQL Server Management Studio to set up and monitor Database Mirroring. For more information, see How to: Configure a Database Mirroring Session is SQL Server 2005 Books Online.

No. In SQL Server 2005, each principal database can have only one mirrored database.

The following table shows what database mirroring features are supported by the various editions of SQL Server 2005.

A few database mirroring features require SQL Server 2005 Enterprise or Developer Editions: