次の方法で共有

Running a database mirror setup with the SQLBrowser service off may produce unexpected results.

  • [アーティクル]

I had an interesting case the other day which I thought I'd share some information on here.
Basically the setup was a SQL Server 2008 Database Mirror setup and the connectionstring looked something like this:

@"Data Source=SERVER_A\INSTANCE_A,12345;Failover Partner=SERVER_B\INSTANCE_B,54321;Initial Catalog=MirroredDatabase;";

Perhaps you'll notice that the connectionstring contains both server and instance and port number.
In this case the reason was that customer wanted the SQLBrowser to be disabled.
If you do not know, the SQLBrowser acts as a 'lookup' for incoming requests for SQL Server.
A connection will hit the SQL Server machine, the instance name is provided to the SQLBrowser who will return port number to the client who uses that information to connect.

In case the SQLBrowser service is turned off, a connection using @"Data Source=SERVER_A\INSTANCE_A" will fail since the client will not get the port number back.
So the connection fails. A way around this is to provide the port number in the connectionstring as per above.

So in this case the SQLBrowser service was turned off and the connectionstring had the port number added.
Now what happened was that when SERVER_A was primary and SERVER_B was mirror connections opened fine.
You may expect this since this is the most likely scenario, i.e. primary is up and running.
Given a situation when SERVER_A failed over so that SERVER_B was primary and SERVER_A was mirror, connections failed to open.
Given a situation when SERVER_B failed over so that SERVER_A was primary and SERVER_B was mirror, connections opened fine.

The reason for this is the common misunderstanding on how the failover partner in the connectionstring works.
What happens is this, when primary server in the connectionstring (what is in Data Source) is available and connected to in a mirror setup, the SQL Server will
return the failover partner to the client. This takes precedence over whatever is in the failover partner in the connectionstring. This effectively means that as
long as the primary is up and running (SERVER_A in this case) you can have whatever in the failover partner in the connectionstring since this is ignored.

However, if the primary is down (SERVER_A in this case) the client will try on the failover partner in the connectionstring. Which then on a successful connect
will download the failover partner to the client and cache it there.
However, again, all connections will first try on the Data Source server.

Now, the problem in this is case is that the failover partner that is returned from the SQL Server do not contain a port number.
So it will return the server and the instance. And this means that the SQLBrowser needs to be running since we need to look up the port number to be able to connect.
So with no SQLBrowser service running we have no port. No port, no connection. You can see what is returned to the client by running the following:

SELECT DB_NAME(database_id) AS 'DatabaseName'
, mirroring_role_desc
, mirroring_safety_level_desc
, mirroring_state_desc
, mirroring_partner_instance
FROM
sys.database_mirroring WHERE mirroring_guid IS NOT NULL;  

 

 

What is in the mirroring_partner_instance is what will be sent back to the client and be cached there.

So, let's walk through this scenario.
We have connectionstring like this: @"Data Source=SERVER_A\INSTANCE_A,12345;Failover Partner=SERVER_B\INSTANCE_B,54321;Initial Catalog=MirroredDatabase;";

The SQLBrowser is turned off on both machines.

SERVER_A is up and running so SERVER_B is mirror. We open a connection; this will successfully connected to SERVER_A\INSTANCE_A since we have provided the port number.
The failover in the connectionstring is ignored since SERVER_B\INSTANCE_B is downloaded from the SQL Server and takes precedence over what is in the connectionstring.

Now a failover occurs so SERVER_A is mirror and SERVER_B is primary. This means that the next attempt to open a connection will fail since the client will try to connect
using SERVER_B\INSTANCE_B (from the cache) so since there is no SQLBrowser service running, the connection will fail since we can't get the connection information we need (the port).

Now a new connection is made (SERVER_A is still mirror and SERVER_B is still primary). This time the connection will open. This is because the connection will try on the data source (SERVER_A\INSTANCE_A,12345 ) first.
This will fail so it will try on the failover partner in the connectionstring (SERVER_B\INSTANCE_B,54321). This is successful so now the client downloads the mirror to the cache, this will be SERVER_A\INSTANCE_A
but again with no port.

Now a failover occurs so SERVER_A is again primary and SERVER_B is again mirror. Now maybe you would expect the next connection to fail since there is no port.
But this is not the case since the connection will first try on SERVER_A\INSTANCE_A,12345 and since this server is now available it will successfully connect and download the failover partner to the client.
Which then will be SERVER_B\INSTANCE_B with no port. So if a new failover would occur we again would have a connection issue since we have no port when SQLBrowser is not running.

The above may be theoretical and you may never see this. After all, a failover should not be a regular thing to happen. But this is worth being aware of if you for some reason are not running SQLBrowser.

So the short version of the above is this. If you are running a mirror setup with named instances, the SQLBrowser service should be up and running. If not, a connection after a failover will try
to use server\instance with no port and this will fail since this service is not running.

One possible way around this is to create an alias on all clients that are connecting to this mirror setup.
In other words, you need to create an alias which has a name of SERVER\INSTANCE and a value of SERVER\INSTANCE,PORT. This means that when a failover has occurred and SERVER\INSTANCE is taken
from the cache and attempted to be used by the connection, it will check for an alias with this name, it will find it and substitute it with the server\instance, port. And connection will succeed.

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
"SQL Server Browser Service"
https://msdn.microsoft.com/en-us/library/ms181087.aspx

 

SELECT

Comments

  • Anonymous
    March 23, 2015
    Without knowing much (or nothing at all...:P) about the SQL Server internals and such, some questions come to mind:
  1. Why does "the failover partner that is returned from the SQL Server do not contain a port number."?
  2. Since SQL Server knows which instanceDB plays a mirroring role for a particular instanceDB (doesn't it?), why does someone need to specify it in the connection string with "Failover Partner"? Does this happen in newer versions of SQL Server?

  • Anonymous
    March 24, 2015
    Is the 2nd needed because the client needs to try the instance B when connection to A fails? Is this how it works? This doesn't seem a very optimized way to do things (I would expect someone like the witness who knows -when not down- which instance is up, to redirect the SQL traffic), but I guess that may be the only way...

  • Anonymous
    March 24, 2015
    Hm... But again, the penalty is only when switching back & forth from 1 instance to the other, so I guess it makes sense... :)