Introduction

Database mirroring is a new SQL Server 2005 technology available for review 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 Overview

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 hardens them to disk as quickly as possible. 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.

The principal and mirror servers are each considered a partner in the database mirroring session. A database mirroring session consists of a relationship between the partner servers when they mirror a database from one partner to another. A given partner server may have the principal role for one database and a mirror role for a different database.

In addition to the two partner servers (principal and mirror) a database mirroring session may have an optional third server, called the witness. The witness server's role is to enable automatic failover. When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.

Some important items to note about database mirroring:

·         The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.

·         The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.

·         The mirror database must have the same name as the principal database.

·         Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' later in this paper.)

Note: For more information about the terms related to database mirroring, see "Overview of Database Mirroring" in SQL Server 2005 Books Online.

Operating Modes

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.

Table 1.   Database Mirroring Operating Modes

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.

In order to explore the three operating modes in more detail, let's first take a closer look at transaction safety and the role of a quorum.

Transaction Safety

If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).

Synchronous transfer guarantees that all transactions in the mirror database's transaction log will be synchronized with the principal database's transaction log, and so the transactions are considered safely transferred. You set safety to FULL using

ALTER DATABASE [<dbname>] SET SAFETY FULL;

When safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgment from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service. (See the topic 'Forced Service' in SQL Server Books Online.)

The Quorum and the Witness Server

When the witness server is set, a database mirroring session requires a quorum to keep a database in service. A quorum is the minimal relationship among all the connected servers required by a synchronous database mirroring session. Because at least two servers are required for a quorum, when the witness is set the principal server must form a quorum with at least one other server to keep the database in service, regardless of the safety setting. Normally, if a witness is set, then the safety level is set to FULL as well.

The witness server assists the principal or mirror in forming a quorum. If a witness server is present, a loss of either the principal database or the mirror database leaves two servers to form a quorum. If the principal cannot see the mirror server, but it can form a quorum with the witness, it can keep its database in service. Similarly, if the mirror and witness servers cannot see the principal server, and the mirror server can form a quorum with the witness, the mirror can take on the role of a new principal server.

The witness is not considered a single point of failure in a database mirroring session, because if the witness server fails, the principal and mirror continue to form a quorum. (For further information, see the topic "Quorum in Database Mirroring Sessions" in SQL Server Books Online.)

High Availability Operating Mode

The High Availability operating mode supports maximum database availability with automatic failover to the mirror database if the principal database fails. It requires that you set safety to FULL and define a witness server as part of the database mirroring session.

The High Availability mode is best used where you have fast and very reliable communication paths between the servers and you require automatic failover for a single database. When safety is FULL, the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. Because a single database failure will cause an automatic failover, if you have multi-database applications you want to consider other operating modes. (See "Multi-Database Issues" in the Implementation section later in this paper.)

In the High Availability mode, database mirroring is self-monitoring. If the principal database suddenly becomes unavailable, or the principal's server is down, then the witness and the mirror will form a quorum of two and the mirror SQL Server will perform an automatic failover. At that point, the mirror server instance will change its role to become the new principal and recover the database. The mirror server can become available quickly because the mirror has been replaying the principal's transaction logs and its transaction log has been synchronized with the principal's.

Also, SQL Server 2005 can make a database available to users earlier in the recovery process. SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. In SQL Server 2005, a newly recovered database can become available for use as soon as the redo phase is finished. Therefore if a database mirroring failover occurs, the recovered new principal database can become available for use as soon as it finishes the redo phase. Because the mirror database has been replaying transaction log records all along, all the mirror serves has to do is finish the redo process, which normally can be accomplished in seconds.

High Protection Operating Mode

The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database does not need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness to fill the tie-breaker role. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.

Since there is no witness server defined, automatic failover cannot occur and a principal server which suddenly loses its quorum with the mirror does not take its database out of service.

High Performance Operating Mode

In the High Performance operating mode, transactional safety is OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror that all transaction log records have been recorded on the mirror. The mirror does its best to keep up with the principal, but it is not guaranteed at any point in time that all the most recent transactions from the principal will have been hardened in the mirror's transaction log.

Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. If the witness is set, a quorum is required. If the witness is not set, then a quorum is not required. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation:

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

Database Snapshots and the Mirror Database

Because the mirror database is in a recovering state, it is not accessible and not readable. With SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time. Database snapshots provide a read-only view of a database, exposing data that is consistent at the point of the snapshot creation.

You access the database snapshot just as though it were another database. When you query a database snapshot, you read original versions of any database data that has been changed after the snapshot's creation from the database snapshot's file, and you read unchanged data from the original database. The end effect is that you see database data current at the point in time that you created the snapshot. (See the topic "Using Database Snapshots with Database Mirroring" in SQL Server Books Online for more information.)

Because database snapshots do require some overhead on the mirror server, you need to be careful about how they might impact database mirroring performance. You can only mirror to one database, so if you need to scale out to many read-only reporting servers, transactional replication is a better choice. (For more information, see "Database Mirroring and Replication" in the Implementation section later.)

Client-side Redirect

In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.

There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:

"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"

The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.

Assume a client is successfully connected to the principal, and a database mirroring failover (automatic, manual, or forced) occurs. The next time the application attempts to use the connection, the ADO.NET or SQL Native Client driver will detect that the connection to the old principal has failed, and will automatically retry connecting to the new principal as specified in the failover partner name. If successful, and there is a new mirror server specified for the database mirroring session by the new principal, the driver will retrieve the new partner failover server name and place it in its client cache. If the client cannot connect to the alternate server, the driver will try each server alternately until the login timeout period is reached.

The great advantage of using the database mirroring support built into ADO.NET and the SQL Native Client driver is that you do not need to recode the application, or place special code in the application, to handle a database mirroring failover.

If you do not use the ADO.NET or SQL Native Client automatic redirection, you can use other techniques that will enable your application to fail over. For example, you could use Network Load Balancing to manually redirect connections from one server to another, while the client just connects to a virtual server name. You might also write your own redirection code and retry logic.

However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.

Database Mirroring and SQL Server 2005 Editions

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

Table 2.   Database Mirroring and SQL Server 2005 Editions

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

·         High Performance mode with safety OFF (asynchronous data transfer);

·         database snapshots;

·         Use of multiple threads for replaying the transaction log on the mirror database (parallel REDO).

SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring.

Conclusion

Database mirroring is a new SQL Server 2005 technology that can deliver high availability and high performance solutions for database redundancy. In database mirroring, transaction log records are sent directly from a principal to a mirror database whenever the principal's transaction log buffer is written to disk (hardened). This technique can keep the mirror database nearly up to date with the principal, and with no loss of committed data. In the High Availability operating mode, if the principal fails, the mirror server will automatically become a new principal and recover its database. Using the new ADO.NET or SQL Native Access Client drivers, applications can also perform an automatic failover from the client servers as well. Database mirroring becomes an important new option in the array of high availability technologies supported by SQL Server 2005.