Making the Initial Connection to a Database Mirroring Session
New: 14 April 2006
For the initial connection to a mirrored database, a client must supply a connection string that minimally supplies the name of a server instance. This required server name should identify the current principal server instance and is known as the initial partner name.
Optionally, the connection string can also supply the name of another server instance, which should identify the current mirror server instance, for use if the initial partner is unavailable during the first connection attempt. The second name is known as the failover partner name.
The connection string must also supply a database name. This is necessary to enable failover attempts by the data access provider.
On receiving a connection string, the data access provider stores the initial partner name and the failover partner name, if supplied, in a cache in the client's volatile memory (for managed code, the cache is scoped to the application domain). Once cached, the initial partner name is never updated by the data access provider. When the client supplies the failover partner name, the data access provider also stores this failover partner name temporarily in case the provider cannot connect using the initial partner name.
A database mirroring session does not protect against server-access problems that are specific to clients, such as when a client computer is having a problems communicating with the network. A connection attempt to a mirrored database can also fail for a variety of reasons that are unrelated to the data-access provider; for example, a connection attempt can fail because the principal server instance is inactive, as occurs when the database is failing over, or because of a network error.
When attempting to connect, the data access provider begins by using the initial partner name. If the specified server instance is available and is the current principal server instance, the connection attempt typically succeeds.
Note
If the mirroring session is paused, the client typically connects to the principal server and the downloads the partner name. However, the database is unavailable to the client until mirroring resumes.
If that attempt does not work, the data access provider tries the failover partner name, if available. If either partner name correctly identifies the current principal server, the data access provider normally succeeds in opening the initial connection. On completing this connection, the data access provider downloads the server instance name of the current mirror server. This name is stored in the cache as the failover partner name, overwriting the client-supplied failover partner name, if any. Thereafter, the .NET Framework Data Provider for SQL Server does not update the failover partner name. In contrast, SQL Native Client updates the cache whenever a subsequent connection or connection reset returns a different partner name.
The following figure illustrates a client connection to the initial partner, Partner_A, for a mirrored database named Db_1. This figure shows a case in which the initial partner name supplied by the client correctly identifies the current principal server, Partner_A. The initial connection attempt succeeds, and the data access provider stores the name of the mirror server (currently Partner_B) as the failover partner name in the local cache. Finally, the client connects to the principal copy of the Db_1 database.
.gif "Client connection if initial partner is principal")
The initial connection attempt may fail, for example, because of a network error or an inactive server instance. Because the initial partner is unavailable, for the data access provider to attempt to connect to the failover partner, the client must have supplied the failover partner name in the connection string.
In that case, if the failover partner name is unavailable, the original connection attempt continues until the network connection timeout or an error is returned (just as for a non-mirrored database).
When the failover partner name is supplied in the connection string, the behavior of the data access provider depends on the network protocol and operating system of the client, as follows:
- For TCP/IP, if the client is running Microsoft Windows XP or later, the connection attempts are regulated by a connection retry algorithm that is specific to database mirroring. The connection retry algorithm determines the maximum time (the retry time) allotted for opening a connection in a given connection attempt. For more information, see Connection Retry Algorithm (for TCP/IP Connections).
- For other network protocols and for clients who are not running Microsoft Windows XP or later
If an error occurs or if the initial partner is unavailable, the initial connection attempt waits until the network connection timeout period expires or the login timeout period expires on the data access provider. Typically, this wait is on the order of 20 to 30 seconds. Thereafter, if the data access provider has not timed out, it attempts to connect to the failover partner. If the connection timeout period expires before the connection succeeds or the failover partner is unavailable, the connection attempt fails. If failover partner is available within the login timeout period and is now the principal server, the connection attempt normally succeeds.
Connection Strings for a Mirrored Database
The connection string supplied by the client contains information that the data access provider uses to connect to the database. This section discusses the keywords that are specifically relevant for connecting to a mirrored database using a SQL Native Client ODBC Driver Connection. For information about all of the connection-string keywords, see Using Connection String Keywords with SQL Native Client.
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;
Important
We recommend keeping TCP/IP at the top of a client's protocol list. However, if the connection string specifies the Network attribute, this overrides the list order.
Alternatively, to ensure that the client requests named pipes for every connection to the partners, a connection string supplies the following attribute:
Network=dbnmpntw;
Important
Because named pipes does not use the TCP/IP retry algorithm, in many cases, a named pipes connection attempt may time out before connecting to a mirrored database.
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;
or
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.
Note
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;
Note
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 "
Note
This string omits authentication information. For information on the keywords for integrated authentication, see Using Connection String Keywords with SQL Native Client.
Important
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 failover partner name, which should identify the current mirror server instance. The failover 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 failover partner will be freed from relying on DNS and SQL Server Browser. Once a connection is established, the failover partner name will be overwritten with the failover partner name, so if a failover occurs, the redirected connections will require DNS and SQL Server Browser.
Note
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.
Note
Managed code application developers supply the failover partner name in the ConnectionString of the SqlConnection object. For information on using this connection string, see "Database Mirroring Support in the .NET Framework Data Provider for SQL Server" in the ADO.NET documentation, which is part of the Microsoft .NET Framework SDK.
Example Connection String
For example, to explicitly connect using TCP/IP to the AdventureWorks database on either Partner_A or Partner_B, a client application could supply the following connection string:
"Server=Partner_A; Failover Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"
Alternatively, the client could use the IP address and port number to identify the initial partner, Partner_A; for example, if the IP address is 250.65.43.21 and the port number is 4734, the connection string would be:
"Server=250.65.43.21,4734; Failover Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"
See Also
Concepts
Network Protocols, and TDS Endpoints
Overview of Database Mirroring
Possible Failures During Database Mirroring
Other Resources
Connecting to the SQL Server Database Engine
Using Connection String Keywords with SQL Native Client
Using SQL Server Browser