Clarification on the Failover Partner in the connectionstring in Database Mirror setup.

[アーティクル]
12/08/2010

There is a common misunderstanding on how the Failover Partner keyword in the connectionstring works in a database mirror setup.
This is my attempt to clarify a bit on this.

From the documentation we can see the following: “Failover Partner - The name of the failover partner server where database mirroring is configured.”
This then suggests that what is given in “Failover Partner” in the connectionstring is the server that will be connected to when a failover occurs.

This is not the whole truth.
What happens is that when you successfully connect to the primary, then the SQL Server will send the failover partner to the client which will then cache
this failover partner on the client side. And this is what will be used when a failover occurs.

However, when the primary is down when the initial connection is made, then the client will try the failover partner in the connection string.
If this then is successful, the SQL Server will send the ‘new’ failover partner to the client which will then cache this failover partner on the client side.
This means that what was the original primary server is now stored on the client as the failover partner.

This is somewhat confusing and should possibly be better documented. But I will try to illustrate this by example.

Let’s assume that we have a setup that looks as follows, in Mirroring properties for a database that is setup for mirroring (right click the database, select properties and go to the Mirror section) we have the following:

PRINCIPAL: Server_A
MIRROR: Server_B

And our mirrored database is called MirroredDatabase.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scenario #1:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Server_A is running as Principal (Principal, Synchronized)
Server_B is running as Mirror (Mirror, Synchronized / Restoring …)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_B;Initial Catalog=MirroredDabase";

When we now call Open() on the connection.
Server_A is up and running so we successfully connect to it and then the client will download Server_B from the SQL Server to the cache on the client side.
Whatever is in the Failover Partner in the connectionstring is ignored.
So here Failover Partner is Server_B (returned from SQL Server)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scenario #2:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Server_A is running as Principal (Principal, Synchronized)
Server_B is running as Mirror (Mirror, Synchronized / Restoring …)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_X;Initial Catalog=MirroredDabase";
or
Connectionstring is: string cs = @"Data Source=Server_A;Initial Catalog=MirroredDabase";
So here Failover Partner in the connection string is a nonexistent server or nothing at all

Server_A is running as Mirror (Mirror, Synchronized / Restoring …)
Server_B is running as Principal (Principal, Synchronized)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_B;Initial Catalog=MirroredDabase";

When we now call Open() on the connection, there will be an attempt to connect to Server_A since this is the Data Source in the connectionstring. This will fail since Server_A is not accessible since it is in the restoring state.
So now the SqlConnection WILL use the Failover Partner in the connectionstring. So there will be an attempt to connect to Server_B. This is successful since this is now up and running.
This means that when the connection to Server_B is opened, the client will download Server_A from the SQL Server to the cache on the client side.
So here Failover Partner is Server_A (returned from SQL Server). So this now means that we have Server_B in the connectionstring, but the ‘true’ failover partner is Server_A (in the cache).

Note that there will be a silent failed open on Server_A before there is an attempt to open the connection to Server_B.

When we now call Open() on the connection, there will be an attempt to connect to Server_A since this is the Data Source in the connectionstring. This will fail since Server_A is not accessible since it is in the restoring state.
So now the SqlConnection WILL use the Failover Partner in the connectionstring if there is one. However, in this case, there is none or it is an invalid server.
So this means that there will be a silent attempt to connect to Server_A, this will fail. So it will try to connect to Server_X and this will also fail since there is no such server.
This will lead to:

System.Data.SqlClient.SqlException (0x80131904):
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

or something similar depending on driver etc.

Now if there is new failover, so that Server_A is running as Principal and Server_B is running as Mirror (as per the original setup) then the connection will open fine using the same connection strings.
This is because we are now back to scenario #1 where Server_A is accessible and Server_B will be downloaded to the cache from SQL Server.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scenario #5:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Server_A is running as Principal (Principal, Synchronized)
Server_B is running as Mirror (Mirror, Synchronized / Restoring …)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_B;Initial Catalog=MirroredDabase";

You have some connections in the connection pool (which you probably have since this in on by default).

We now call Open() on the connection. This will successfully connect to Server_A (and store Server_B in the cache, received from SQL Server).
We now call Close() on the connection. This means that the connection will go into the pool. Now the next time you call Open() a connection will be taken from the pool.
So, now we have a failover (so that Server_A is running as Mirror and Server_B as Principal).
We now call Open() on the connection from the pool. And we will get:

System.Data.SqlClient.SqlException (0x80131904):
A transport-level error has occurred when sending the request to the server.

or something similar depending on driver etc.
This is because the connection in the pools has no knowledge of the failover that has happened, it still assumes that Server_A is up and running and will attempt to connect to it.
And fail.

You have some connections in the connection pool (which you probably have since this in on by default).

We now call Open() on the connection. This will (silently) fail to connect to Server_A and try on Server_B from the Failover Partner, and succeed.
We now call Close() on the connection. This means that the connection will go into the pool. Now the next time you call Open() a connection will be taken from the pool.
So, now we have a failover (so that Server_A is running as Principal and Server_B as Mirror).
We now call Open() on the connection from the pool. And this is successful. No exception even though this is much like scenario #5.

This is because the connection in the pool has Data Source set to Server_A, so it will first try to connect to Server_A and succed.
And the Failover Partner in connectionstring is ignored, instead it is downloaded from the SQL Server.

Server_A is running as Principal (Principal, Synchronized)
Server_B is running as Mirror (Mirror, Synchronized / Restoring …)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_B;Initial Catalog=MirroredDabase";

We call Open() on the connection. Server_A is there so the connection opens fine and downloads Server_B into the cache.
All is well. We now have a failover so the scenario is this:

We call Open() on the connection, Server_A is not there, so there is a silent fail and then we connect to Server_B.
The client then caches Server_A as the Failover Partner. So all is running fine still (even if the Server_A is never there).

Now, the dbas decides that Server_A is no longer to be used. So they reconfigure the mirror setup and drops Server_A.
Instead they introduce Server_C which is to be the new Mirror and Server_B is now the new Principal.
However, there is no information on this for the application developers. So they run the application with the connectionstring as it always was, so now we have a situation like this:

Server_A is gone.
Server_B is running as Principal (Principal, Synchronized)
Server_C is running as Mirror (Mirror, Synchronized / Restoring …)
Connectionstring is: string cs = @"Data Source=Server_A;Failover Partner=Server_B;Initial Catalog=MirroredDabase";

What happens now when call Open() is that we silently fail to connect to Server_A (since this is gone), so we connect to the Failover Partner in the connectionstring, which is Server_B.
This is successful, but what happens now is that Server_C is downloaded into the cache as the Failover Partner.
Which then means that you may have the impression that all is setup correctly, but when a failover has occurred, you will have a failed connect to Server_A and then to Server_B
and get an exception since the server to connect to is Server_C. But the client do not know this from either the connectionstring or the cache.

So in this scenario you may have a well working application with this connectionstring for a long time, even if Server_A is gone (because it will silently fail to connect to it and use the Failover Partner in
the connectionstring even though now the failover partner in the connectionstring is in reality the Principal). But once a failover occurs, you will see connection errors.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

There are probably more scenarios. I just wanted to clarify what is going on and that the Failover Partner in the connectionstring is not what it may seem to be.
Basically the true failover partner is downloaded from the server side and is cached on the client.

Some references:

"SqlConnection.ConnectionString Property"
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
"Making the Initial Connection to a Database Mirroring Session"
https://msdn.microsoft.com/en-us/library/ms366348.aspx
"Using Database Mirroring"
https://msdn.microsoft.com/en-us/library/ms131373.aspx
"The Impact of a Stale Failover Partner Name"
https://msdn.microsoft.com/en-us/library/ms366350.aspx
"Database Mirroring in SQL Server (ADO.NET)"
https://msdn.microsoft.com/en-us/library/5h52hef8.aspx

Comments

Anonymous
March 17, 2011
Great post! This really clears up a few things.
Anonymous
May 26, 2011
Thanks -- this is really helpful!
Anonymous
July 10, 2011
What is the point/use of extracting mirror server name from principal and storing it in local cache? Is the information stored in local cache used anytime?
Anonymous
January 14, 2013
helpful explanation of how failover is handled
Anonymous
January 21, 2013
Is there a way to get sqljdbc to automatically reconnect after a failure? We have a cluster with a single hostname for accessing the DB, and there is a small "outage" when the cluster fails over. The applications will not reconnect without restarting.
Anonymous
August 21, 2013
The following is our scenario Server_A is running as Principal (Principal, Synchronized) Server_B is running as Mirror (Mirror, Synchronized / Restoring) Connectionstring is: string cs = @"Data Source=Server_B;Failover Partner=Server_A;Initial Catalog=MirroredDabase"; Suddenly Mirroring is removed between the two servers. what will happen if we connect to Server_B(Mirror, Synchronized / Restoring). Will that works. Kindly advise
Anonymous
April 16, 2014
Great article!
Anonymous
September 16, 2015
Great explanation. So after reading this, the lesson I take away is that you should NOT user the Failover Server attribute at all in your connection strings. Rather it should use the one from cached from SQL Server since that was accurate at the time of connection. Does that make sense or am I missing something?
Anonymous
September 17, 2015
After some testing, it does appear the Failover Partner= attribute is required even if not being read. When it is not present, a SQL connection error is returned to the client. If I restart the client again, it does start using MIRROR server but manual restart of the client is required. So the client apparently did receive the name of MIRROR server even though it was not specified in the connection string. When the Failover Partner is included in the connection string, the client app pauses for moment (no SQL error triggered) and then continues once the MIRROR server is recovered and ready. This is very nice. I think in this mode, the client will wait until x seconds for the MIRROR to become available and continue before it returns and error to the client. So apparently having the Failover Partner attribute is needed to make that smooth transition from PRIMARY TO MIRROR without any additional coding on the client.

次の方法で共有

Clarification on the Failover Partner in the connectionstring in Database Mirror setup.

Comments

その他のリソース