Database Mirroring Sessions
Database mirroring occurs within the context of a database mirroring session. This topic assumes that you are familiar with the principal, mirror, and witness roles, operating modes, and role switching in database mirroring. For more information, see Database Mirroring Overview.
When the mirror database is ready, and the server instances are configured, the database owner can start database mirroring. As soon as mirroring starts, each partner begins to maintain state information in its database about that database as well as the other partner and the witness, if any. This state information permits the server instances to maintain a relationship known as a database mirroring session. Throughout a database mirroring session, the server instances monitor each other. The state information is maintained until the database owner stops the session. For more information, see Mirroring States and Monitoring Database Mirroring.
At the start of a database mirroring session, the mirror server identifies the log sequence number (LSN) of the latest transaction log applied on the mirror database and asks the principal server for the transaction log for all subsequent transactions, if any. In response, the principal server sends to the mirror server any active log records accumulated since the last log restored to the mirror database or sent to the mirror server. Unsent log that has accumulated on the log disk of the principal database is known as the send queue.
The mirror server immediately writes the incoming log to disk, where it is held until it is applied to the mirror database. The log waiting on the mirror's disk is known as the redo queue. The amount of unrestored log waiting in the redo queue is an indicator of the time required to fail over to the mirror database. For more information, see Estimating the Interruption of Service During Role Switching.
The principal server continues to make the principal database available to clients and client connections. After mirroring starts, each time a client updates the principal database, on writing the transaction to the log of the principal database, the principal server also sends that log record to the mirror server. There, the mirror server immediately writes the log record to disk as the last record in the redo queue.
In the background, beginning with the oldest log record, the mirror server redoes the log on the mirror database, record by record, as quickly as possible. Redoing the log involves applying the queued log records to the mirror database in sequence, starting with the oldest record. Each log record is redone once and only once. As the mirror server redoes the log, the mirror database is continually rolled forward. When the principal server truncates or shrinks the log for the principal database, the mirror server also shrinks the log at the same point in the log stream.
Typically, redoing quickly catches up the mirror database in relation to the principal database. Whether the mirror database ever completely catches up to the principal database depends on the operating mode of the session. Under synchronous, high-safety mode, the principal server waits to confirm new transactions until they are written to the mirror server's log disk. After the accumulated log records have been sent to the mirror server, the mirror database becomes synchronized with the principal database.
During a session, if the principal server is unable to send every log record immediately, unsent log records accumulate in the send queue. Under synchronous, high-safety mode, after synchronization, new unsent log accumulates only when mirroring is paused or suspended. Under asynchronous, high-performance mode, in contrast, unsent log accumulates whenever the mirror server falls behind during mirroring, as well as when mirroring is paused or suspended. The amount of unsent log is an indicator of the possible data loss in the event that the principal server fails.
Note
If redoing fails, the mirror server pauses the session by putting the database into the SUSPENDED state. The database owner must resolve the cause of the failure before resuming the session.
Concurrent Sessions
A given server instance can participate in multiple, concurrent database mirroring sessions (once per mirrored database) with the same or different server instances. Often, a server instance serves exclusively as a partner or a witness in all of its database mirroring sessions. However, because each session is independent of the other sessions, a server instance can act as a partner in some sessions and as a witness in other sessions. For example, consider the following four sessions among three server instances (SSInstance_1, SSInstance_2, and SSInstance_3). Each server instance serves as a partner in some sessions and as a witness in others:
Server instance |
Session for database A |
Session for database B |
Session for database C |
Session for database D |
|---|---|---|---|---|
SSInstance_1 |
Witness |
Partner |
Partner |
Partner |
SSInstance_2 |
Partner |
Witness |
Partner |
Partner |
SSInstance_3 |
Partner |
Partner |
Witness |
Witness |
The following figure illustrates two server instances that are participating as partners together in two mirroring sessions. One session is for a database named Db_1, and the other session is for a database named Db_2.
.gif "Two server instances in two concurrent sessions")
Each of the databases is independent of the others. For example, a server instance might initially be the mirror server for two databases. If one of those databases fails over, the server instance becomes the principal server for the failed-over database while remaining the mirror server for the other database.
As another example, consider a server instance that is the principal server for two or more databases running in high-safety mode with automatic failover, If the server instance fails, all of the databases automatically failover to their respective mirror databases.
When setting up a server instance to operate both as a partner and a witness, be sure that the database mirroring endpoint supports both roles (for more information, see Database Mirroring Endpoint). Also, ensure that the system has sufficient resources to reduce resource contention.
Note
Because mirrored databases are independent of each other, databases cannot fail over as a group.
Threads Created for a Database Mirroring Session
The kinds of threads that a server instance creates for a database mirroring session depend partly on the mirroring roles that the server instance is performing. A given session has some or all of the following threads:
One global thread for database mirroring communications. This thread is started by Service Broker.
If the server instance is acting as a mirroring partner (whether it is the principal server or mirror server):
One thread per mirrored database for event processing.
One thread per mirrored database for asynchronous tasks (such as log send or log write) that would otherwise block the event thread.
Whenever the instance is acting as a mirror server:
One redo manager thread, which submits log for redo, performs page read-ahead, lock reacquisition, and so on.
In SQL Server Standard, one redo thread per mirror database, or in SQL Server Enterprise, one redo thread per mirror database for every four CPUs. These threads perform the actual log redo.
If the instance is acting as a witness:
- One global thread for processing the witness messages for all mirroring sessions in which the instance is acting as the witness.
Prerequisites for a Database Mirroring Session
Before a mirroring session can begin, the database owner or system administrator must create the mirror database, set up endpoints and logins, and, in some cases, create and set up certificates. For more information, see Setting Up Database Mirroring.
Creating a mirror database minimally requires taking a full backup of the principal database and one subsequent log backup and restoring them both onto the mirror server instance, using WITH NORECOVERY. Furthermore, before you can start mirroring, if any additional log backups are taken after the required log backup, you must also manually apply every additional log backup (always using WITH NORECOVERY). After applying the latest log backup, you can start mirroring. For more information, see Preparing a Mirror Database for Mirroring.
Impact of Pausing a Session on the Principal Transaction Log
At any time, the database owner can pause a session. Pausing preserves the session state while removing mirroring. When a session is paused, the principal server does not send any new log records to the mirror server. All of these records remain active and accumulate in the transaction log of the principal database. As long as a database mirroring session remains paused, the transaction log cannot be truncated. Therefore, if the database mirroring session is paused for too long, the log can fill up.
For more information, see Pausing and Resuming Database Mirroring.
Client Connections
Client-connection support for database mirroring sessions is provided by the Microsoft .NET Data Provider for SQL Server. For more information, see Connecting Clients to a Database Mirroring Session (SQL Server).