Database mirroring dropped connection scenarios

In a database mirroring system, when connections between database servers drop, the following notes apply:

The time required for a server to notice a dropped network connection depends on:
1. How the connection is broken. If a network cable is unplugged from the computer, some operating systems detect this problem immediately and cause the connection to be dropped. If the problem was caused by a failure in a network switch, the problem is less likely to be detected by the server immediately.
2. The liveness timeout setting specified in the mirror connection string. See LivenessTimeout connection property.
For the primary server to maintain ownership of a mirrored database, it must be connected to at least one of the other servers (mirror or arbiter). When connections to both of the other servers are lost, the database is restarted and the primary server waits until it can re-establish at least one of those connections to verify its status.
The connections between servers are re-established automatically when the problem which caused the connection to be dropped is corrected.
Until a server detects a dropped connection, it maintains the role it was previously assigned. This behavior can result in two servers attempting to act as the primary server for a brief period if the server that was previously the mirror server detects a dropped connection to the primary server before the primary server detects that its connection to the mirror server was dropped. The first update made on the original primary server blocks when the server attempts to send its changes to the other server, which limits the potential for lost transactions should this situation occur.

When the original primary detects a dropped connection from the original mirror server and checks the arbiter's state, it will see that a failover has occurred and it will restart the database.
If a server acting as mirror must restart its database, access to the database on that server is not allowed until a connection to the primary server is re-established.
Network failures may affect both client connections and mirror server connections, depending on the topology of the network.

The following scenarios help you understand what happens when connections are dropped in a mirroring system. The scenarios use the following database mirroring configuration, which consists of Server 1, Server 2, and an arbiter server running in synchronous mode:

An example database mirroring system, consisting of Server 1, Server 2, and an arbiter server.

At any time, you can use the MirrorState, PartnerState, and ArbiterState database properties to determine the status of the database servers in the mirroring system. See MirrorState database property, PartnerState database property, and ArbiterState database property.

Scenario 7: Connection between the primary server and the arbiter server is dropped

The connection between the primary server (Server 1) and the arbiter is dropped.
Server 1 and the arbiter detect the loss of connection between each other.
Both the primary and mirror servers remain available. Client connections are not disconnected. Server 1 continues to send changes to Server 2.
- If Server 1 becomes unavailable or loses its connection to the mirror server (Server 2), then Server 2 becomes the primary server, assuming it is still connected to the arbiter.
- If Server 1 loses its connection to Server 2 and to the arbiter, the database on Server 1 is no longer available. Server 1 restarts the database and waits to reconnect to the arbiter and Server 2.
- If Server 1 and Server 2 both lose their connections to the arbiter server while still connected to each other, the behavior is the same as that described in Scenario 5: Arbiter becomes unavailable.

Scenario 8: Connection between the mirror server and the arbiter server is dropped

The connection between the mirror server (Server 2) and the arbiter is dropped.
Server 2 and the arbiter detect the loss of connection between each other.
Both the primary and the mirror servers remain available. Client connections are not disconnected.
Server 1 continues to send changes to Server 2.
Both Server 1 and Server 2 wait for the arbiter to reconnect.
- If Server 2 becomes unavailable or loses its connection to Server 1, client connections to Server1 are not affected because there is no change in availability. Client connections to Server 2 are lost.
- If Server 1 and Server 2 both lose their connections to the arbiter server while still connected to each other, the behavior is the same as that described in Scenario 5: Arbiter becomes unavailable.
The arbiter comes back online and Server 1 and Server 2 connects to it.

Scenario 9: Connection between the primary server and the mirror server is dropped

The connection between the primary server (Server 1) and the mirror server (Server 2) drops.
Server 1 and Server 2 detect the lost connection, but potentially at different times. Both servers check the arbiter's state and then take action based on their responses from the arbiter:
- If Server 1 detects the dropped connection first, it updates the arbiter's state to indicate that it is still the primary server and that Server 2's database may be out-of-date. When Server 2 checks the arbiter's state, it learns that Server 1 is still the primary, and Server 2 restarts its database. Server 2 waits to re-establish a connection to Server 1 before allowing access to the database.
- If Server 2 detects the dropped connection first, it updates the arbiter's state to indicate that a failover has occurred and Server 2 becomes the primary server. When Server 1 checks the arbiter's state, it learns that a failover occurred and that Server 2 is now the primary server. Server 1 restarts its database and waits to re-establish a connection to Server 2 before allowing access to its database.

Scenario 10: Connection from the primary server to the mirror and arbiter servers is dropped

The connection between the primary server (Server 1) and the mirror server (Server 2) drops and the connection between the primary server (Server1) and the arbiter drops.
Server 1 detects that the arbiter and Server 2 are no longer available. Server 1 restarts its database and waits to reconnect to the other servers. All clients are disconnected.
The arbiter and Server 2 detect that Server 1 is no longer available.
The arbiter and Server 2 reach quorum, and Server 2 becomes the primary server.

For the arbiter server and Server 2, the behavior is the same as that described in Scenario 1: Primary server becomes unavailable.
Server 2 begins accepting client connections.
Server 1 comes back online and reconnects to Server 2 and to the arbiter.
Server 1 is the mirror server and waits for changes from Server 2.
Server 2 sends changes to Server 1.

Scenario 11: Connection from the mirror server to the primary and arbiter servers is dropped

The connection between the mirror server (Server 2) and the primary server (Server 1) and the connection between the mirror server (Server 2) and the arbiter drop.
Server 2 detects that the arbiter and Server 1 are no longer available. Server 2 restarts the database and waits to reconnect to the primary server and the arbiter server. All client connections to Server 2 are disconnected.

Client connections to the primary server are not affected. Clients can continue to connect to the primary server.
The arbiter and Server 1 detect that Server 2 is no longer available.

For the arbiter server and Server 1, the behavior is the same as that described in Scenario 3: Mirror server becomes unavailable.
Server 1 updates the arbiter's state to indicate that Server 2 is no longer up-to-date.
Server 2 comes back online and reconnects to Server 1 and the arbiter.
Server 2 resumes the role of mirror server and waits for changes from Server 1.
Server 1 sends changes to Server 2.

Scenario 12: Connections from the arbiter server to the primary and mirror servers are dropped

The connection between the arbiter and the primary server and the connection between the arbiter and the mirror server (Server 2) drop.
When the connection to the arbiter is lost, the behavior is the same as that described in Scenario 5: Arbiter becomes unavailable.
When the connections are restored, the behavior is the same as that described in Scenario 6: Arbiter restarts.

Scenario 13: Connection between all servers are dropped

The connections between all of the servers drop.
The primary server (Server 1), mirror server (Server 2), and arbiter detect the loss of connection to the other servers.
Server 1 disconnects all clients and restarts the database.
Server 2 disconnects all clients and restarts the database.
Server 1 and Server 2 wait to reconnect to the other servers to determine their roles.
When connections are re-established, Server 1 and Server 2 determine their roles based on their state and the state of the other servers.

Discuss this page in DocCommentXchange.