Fail-Over

Failing over mirror partners is achieved in a process known as role switching. It is not unlike the role switching and seizing that you perform with an Active Directory role. To fail over from the principal server to the secondary, the secondary server takes over the principal role and brings its copy of the database online as the new principal database. If the former principal server is able to continue in the mirror relationship, it can become the new mirror database. You can fail the servers back to their original roles as needed. Role switching is actually the easy part of the mirroring paradigm. The hard part is redirecting the clients to the correct server instance, a subject we will tackle later in this section.

To provide automatic fail-over for a mirror solution, the session must have been configured under the high-safety mode, with a third, witness server monitoring the session. Before the fail-over can occur, the databases on both sides must be synchronized. The witness server must also be connected to the target server that will assume the principal role. The witness server verifies that the partners are up and functioning.

Manually failing over the server also requires high-safety mode. The partners must also be connected to each other, and their databases must already be synchronized. You can also force the fail-over under high-performance mode, but there is the risk of losing data as a result. You would force fail-over when the principal server has failed and is no longer connected to the mirror.

Restoring Connection to the Fail-Over Server

Failing over servers, no matter the technology-replication, mirrored servers, log shipping, or otherwise-requires work at the client or middle tier in terms of how connections to the principal or production database are achieved. Connection strings typically supply the name of the server instance where login can be achieved. A second name, known as the fail-over partner name, can be provided if the first connection attempt fails. The connection string must also supply a database name. This is necessary to enable fail-over attempts by the data access provider. Some other considerations to keep in mind follow.

Network Attribute

The connection string should contain the Network attribute to specify the network protocol. This ensures that the specified network protocol persists between connections to different partners. The best protocol for connecting to a mirrored database is TCP/IP. To ensure that the client requests TCP/IP for every connection to the partners, a connection string supplies the following attribute:

 Network=dbmssocn;

See Chapter 5 for configuring the client protocol stack.

Server Attribute

The connection string must contain a Server attribute that supplies the initial partner name, which should identify the current principal server instance. The simplest way to identify the server instance is by specifying its name, <server_name>[\<SQL_Server_instance_name>]. For example,

 Server=Partner_A Server=Partner_A\Instance_2;

However, when the system name is used, the client must perform a DNS lookup to obtain the IP address of the server and a SQL Server Browser query to obtain the port number of the server on which the partner resides. Those lookups and queries can be bypassed by specifying the IP address and port number of the partner in the Server attribute, rather than specifying the server name. This is recommended to minimize the possibility of external delays while connecting to that partner. A SQL Server Browser query is necessary if the connection string specifies the named instance name and not the port.

To specify the IP address and port, the Server attribute takes the following form, Server=<ip_address>,<port>, for example,

 Server=123.34.45.56, 4724;

The IP address can be IP Version 4 (IPv4) or IP Version 6 (IPv6).

Database Attribute

In addition, the connection string must specify the Database attribute to supply the name of the mirrored database. If the database is unavailable when the client attempts to connect, an exception is raised.

For example, to expressly connect to the AdventureWorks database on the principal server Partner_A, a client uses the following connection string:

 "Server=Partner_A; Database=AdventureWorks"

Bundling the protocol prefix with the Server attribute (Server=tcp:<servername>) is incompatible with the Network attribute, and specifying the protocol in both places will likely result in an error. Therefore, we recommend that a connection string specify the protocol using the Network attribute and specify only the server name in the Server attribute (“Network=dbmssocn; Server=<servername>”).

Failover Partner Attribute

In addition to the initial partner name, the client can also specify a fail-over partner name, which should identify the current mirror server instance. The fail-over partner is specified by the Failover Partner attribute. The simplest way to identify the server instance is by its system name, <server_name>[\<SQL_Server_instance_name>].

Alternatively, the IP address and port number can be supplied in the Failover Partner attribute. If the initial connection attempt fails during the first connection to the database, the attempt to connect to the fail-over partner will be freed from relying on DNS and SQL Server Browser. Once a connection is established, the fail-over partner name will be overwritten with the failed-over partner name, so if a fail-over occurs, the redirected connections will require DNS and SQL Server Browser. When only the initial partner name is provided, application developers do not need to take any action or write any code except about how to reconnect.

SQL Native Client verifies that it connects to a principal server instance but not whether this instance is the partner of server instance specified in the initial partner name of the connection string.