Database Reference
In-Depth Information
Let's look at a few possible failover scenarios in High Availability mode. The key point is that, at any point in time,
servers must have a quorum, and thus at least two servers must be able to connect to each other.
First, let's assume that the principal and witness servers lost the connection to the mirror server. The principal
and witness servers still have a quorum and the principal server continues to work with mirroring in SUSPENDED
state. If, at this stage, the principal server lost the connection to the witness server, and therefore does not have
quorum, the principal server would shut down.
■
Consider placing the witness instance close to the principal server to avoid connectivity issues between them
and unnecessary failovers and shutdowns. it is also beneficial to fail back to the former principal server, which is close to
a witness instance, when the server is back online.
Tip
Now let's assume that the principal server goes offline. In that case, when the mirror and witness server can see
each other and have a quorum, automatic failover occurs and the mirror server becomes the new principal server.
If the old principal server came back online and saw both servers, it would become the mirror server and synchronize
itself with the new principal server. Otherwise, it would shut itself down to avoid a
split-brain
situation where two
different servers allow clients to connect to different copies of the same database.
If the witness server goes offline, mirroring continues to work without the ability to perform automatic failover.
This is similar to the High Protection mirroring mode with exception that if the principal server lost its connection
to the mirror server without the witness server being available, the principal server would shut down to avoid a
split-brain situation.
In High Protection mode, a loss of connectivity between the principal and mirror servers would not stop the
principal server. If the principal is down, you have to perform a manual failover to make the mirror server the new
principal server. There is one caveat, though. If you performed a manual failover and at some point the principal
server came back online without connectivity to the former mirror server, it would continue to behave as the principal
server, which is a split-brain situation.
The .Net SQL Client automatically obtains and caches a mirror server name when it is connected to the principal
server. If a failover happened
after
the mirror server name is cached, the client application would be able to reconnect
to the mirror server, which becomes the new principal server. However, if the failover occurred
before
the mirror
server name is cached, the application would be unable to connect to the former principal server, which now works as
the mirror server and keeps the database in RESTORING state. The application would be unable to obtain information
about the new principal server and, therefore, connect to the database.
You can avoid such situations by specifying the mirror server name in an additional connection string property,
Failover Partner
. The SQL Client tries to connect to the server specified there only in cases when it is unable to
connect to the principal server. When the principal server is online, the SQL Client ignores the mirror server name
specified in this property and caches the mirror server name as it was retrieved from the principal server.
You should also be careful when removing database mirroring. The SQL Client will be able to connect to the
database after mirroring is removed only when it runs on the server specified in the
Server Name
property of the
connection string. You will get a “
Database is not configured for database mirroring
” error if it runs on the server
specified as a
Failover Partner
.
Database mirroring failover is usually faster than Failover Cluster failover. Contrary to a Failover Cluster, which
restarts the entire SQL Server instance, database mirroring performs crash recovery on a single database. However,
the actual duration of the failover process depends on the size of the redo queue and the number of log records that
need to be replayed.
The
PARTNER TIMEOUT
database setting controls the database mirroring failover detection time, which is 10 seconds
by default. You can change this with the
ALTER DATABASE SET PARTNER TIMEOUT
command. It is beneficial to increase
this setting if the network latency between the principal server and the mirror server is high, for example, when servers
reside in different datacenters and/or in the Cloud.
