I thought I had posted an article on this issue and have been saying so at my events, but no one has emailed me to tell me I haven’t!!!!!
Here’s what i thought I posted….sorry if anyone got frustrated looking for this (again i got this from the SQL 2005 Online books):
To connect to a database mirroring session, a client can use either SQL Native Client or .NET Data Provider for Microsoft SQL Server as the data-access provider. Database mirroring is fully supported by both, once they are configured for a Microsoft SQL Server 2005 database.
Note: A database mirroring session does not protect against problems specific to client accessing the servers.
The data-access provider, whether SQL Native Client or ADO.NET Data Provider, stores both an initial partner name and a failover partner name (when it is available). These names are stored in a cache in the client’s volatile memory (for managed code, the cache is scoped to the the application domain). Minimally, a connection string supplies a server name for the initial connection attempt. For a mirroring connection, this name should identify the current principal server. This name is known as the initial partner name. The data-access provider caches the initial partner name supplied in a connection string.
The failover partner name is obtained in one of two ways: from the connection string or by downloading it from the principal server at the time of the initial connection. On a first connection request by a client for a server, the data-access provider caches the server name as the initial partner name. Once cached, the initial partner name is not updated by the data-access provider.
Optionally, the client might also supply a failover partner name that identifies the mirror server. The data-access provider keeps this client-supplied name only until it connects to the principal server. If the connection succeeds, the data-access provider downloads the name of the current mirror server, which overwrites the client-supplied name.
In general, to establish a connection for a mirrored database, the data-access provider first tries the initial partner name. If that connection fails, then the data-access provider tries to connect using the failover partner name, if available. If that fails, then the initial partner name is retried. The data-access provider continues to alternate between the servers until it times out.
Note: If the connection string does not supply a database name, the connection goes to the default database of the login account.
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 identified the current principal server, Partner A. The initial connection attempt succeeded, and the data-access provider stored the name of the mirror server (currently Partner_B) as the failover partner name in its local cache. The client connected to the principal copy of the Db_1 database.
Note: The witness is not involved at all in client connections.
Connecting to the Initial Partner
If the initial partner name in a connection string correctly identifies the current principal server, the initial connection attempt normally succeeds. On connecting to the principal server, the client downloads the name of the mirror server and caches it in memory as the failover partner name. Thereafter, the client-supplied failover partner name, if any, is ignored.
If the client gets disconnected from the database, the data-access provider attempts to reconnect using the initial partner name. If the initial partner is unavailable, the data-access provider then attempts to connect to the failover partner using the cached failover partner name.
If the failover partner is now the principal server, normally, the connection attempt succeeds. If the failover partner is unavailable, the next connection attempt retries the initial partner, and so on. Connection attempts alternate back and forth until either the client connects to one of the partners (as the current principal server) or the data-access provider times out.
|If the client gets disconnected from the database, the data-access provider does not attempt to reconnect. The client must issue a new connection request.|
Connecting to the Failover Partner
If the initial connection attempt fails because the initial partner is unavailable, the client cannot obtain the failover partner name from the initial partner. In this case, 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.
If the connection string contains a failover partner name, the data-access provider attempts to connect to the specified failover partner. For the connection to succeed, the supplied failover partner name must identify the current principal server.
The connection string supplied by the client contains information that the data-access provider uses to connect to the database. If a connection string specifies the name of a server instance without including the database name, the connection uses the current default database of the login. For example, to connect to the default database on a principal server named, Partner_A, the connection string would supply that server’s name as the initial partner name, as follows:
|This string omits authentication information. If the application uses integrated authentication, the complete connection string for ADO.NET would be: |
To direct a connection to a non-default database, a connection string must supply the name of the database. For example, to expressly connect to the AdventureWorks database on the principal server Partner_A, the client would supply the following connection string:
|When only the initial partner name is provided, application developers do not need to take any action or write any code.|
If the database is unavailable when the client attempts to connect, an exception is raised.
|For the client to connect to the database, the client’s login must have been created on the server instance. For more information, see Troubleshooting Orphaned Users.|
The principal server name is always required, and Microsoft recommends that you always specify the database name. In addition to the principal server name, you can also specify failover partner name, in which case the database name is required.
For example, to explicitly connect to the AdventureWorks database on either Partner_A or Partner_B, a managed-code client application would supply the following connection string:
“server=Partner_A; failover partner=Partner_B; database=AdventureWorks”
|Managed-code application developers supply the failover partner name in the P:System.Data.SqlConnection.ConnectionString of the T:System.Data.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.|
The database administrator can change the failover partner at any time. Therefore, a client-supplied failover partner name might not be up to date (that is, the name supplied in a connection string might be stale). For example, consider a failover partner named Partner_B that is replaced by another server instance, Partner_C. If a client then supplies
“\\Partner_B” as the failover partner name, the specified name is stale. When the client-supplied failover partner name is stale, the behavior of the data-access provider equates to the case in which a failover partner name is not supplied by the client.
For example, consider situation in which a client uses one connection string for a series of four connection attempts. In the connection string, the initial partner name is Partner_A, and the failover partner name is Partner B:
“server=Partner_A; failover partner=Partner_B; database=AdventureWorks”. The following table shows four partner configurations and indicates for each whether this connection string works for connecting the client for the first time.
|Step||Principal server||Mirror server||What happens when a client attempts to connect specifying Partner_A and Partner_B?|
Partner_A is cached as the initial partner name. The client succeeds in connecting to Partner_A. The client downloads the name of mirror server, Partner B, and caches it, ignoring the client-supplied failover partner name.
The Partner_A is still cached as the initial partner name, but the client-supplied failover partner name, Partner_B, permits the client to connect to the current principal server.
The database administrator stops mirroring (disconnecting clients), replaces Partner_A with Partner_C, and restarts mirroring.
The client attempts to connect to Partner_A and fails; then the client tries Partner_B (the current principal server) and succeeds. The data-access provider downloads the name of the current mirror server, Partner_C, and caches it as the current failover partner name.
Client attempts to connect to Partner_A initially, and then to Partner_B. Both names fail, and eventually the connection request times out and fails.