SQL 2005 JDBC Driver and Database Mirroring
We ran into some interesting situations with the SQL 2005 JDBC Driver (v1.2) and it's use with failover partners. Take the following connection string:
jdbc:sqlserver://myserver1;databaseName=AdventureWorks;failoverPartner=myserver2;
In this connection string, our Primary server will be myserver1 with our failover server being myserver2. If the primary server becomes unresponsive, we will fail over to the myserver2. This connection string should work perfectly fine.
Lets look at another situation:
jdbc:sqlserver://myserver1;databaseName=AdventureWorks;failoverPartner=myserver2instance;
In this situation, we are connecting to a named instance for the failoverPartner. Again this should work perfectly fine from a usage standpoint of failoverPartner.
Port Number used with failoverPartner
jdbc:sqlserver://myserver1;databaseName=AdventureWorks;failoverPartner=myserver2:1699;
In this case, we are either connecting to a named instance (by way of the port), or a default instance on a non-standard port (that being 1433). This may or may not work as expected. If we were able to successfully connect to the Primary Server once, we will cache the failover connection string via the connection to the Primary server. The primary server in that case actually supplies the connection string to the failover partner and we ignore what you put in the actual application connection string. If that happened, we will probably successfully connect to the failoverPartner.
However, if we were not able to connect to the Primary Server at all (i.e. Primary Server is physically down or unreachable), then we will rely on the application connection string to connect to the failoverPartner. Our JDBC Driver doesn't parse the port number for the failoverPartner property. We will treat it as the actual server name. This is what we would see in the JDBC Log output:
Oct 28, 2008 10:21:22 AM com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: ConnectionID:1 TransactionID:0x0000000000000000 This attempt No: 1
Oct 28, 2008 10:21:22 AM com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: ConnectionID:1 TransactionID:0x0000000000000000 Connecting with server: myserver2:1699 port: 1433 Timeout slice: 400 Timeout Full: 5
Oct 28, 2008 10:21:22 AM com.microsoft.sqlserver.jdbc.TDSChannel open
FINE: TDSChannel ( ConnectionID:1 TransactionID:0x0000000000000000): Opening TCP socket...
Oct 28, 2008 10:21:23 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:com.microsoft.sqlserver.jdbc.SQLServerConnection@471e30 com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed.
Notice that server equals myserver2:1699 with a port of 1433. This is because the port number that was specified in the connection string was not parsed out.
This is the exception you will receive on the application side:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.UnknownHostExce
ption
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithFailover(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
This issue is currently not going to be changed and will still be present in the 2.0 release of the driver.
Named Instance on Primary Server when failoverPartner is specified
jdbc:sqlserver://myserver1instance;databaseName=AdventureWorks;failoverPartner=myserver2;
Here we have a named instance for the Primary server and just a default instance for the failoverPartner. Lets assume that either the Primary Server is physically down or the SQL Browser service on that server is not running. This will result in the following exception:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the named instance has failed. Error: java.net.Socke
tTimeoutException: Receive timed out.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
In most situations, because we received an exception, we will try and connect again as it should use the failoverPartner at this point. This is what we will see:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the named instance has failed. Error: java.net.Socke
tTimeoutException: Receive timed out.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
Notice the lack of the loginWithFailover method from the callstack. We didn't make it far enough to even attempt the connection to the failoverPartner. In this situation, we are trying to resolve the instance name to a port number. Because we are unable to communicate with the SQL Browser services (UDP 1434) we cannot perform the lookup and we are just erroring out at that point. To work around this issue, you could specify the port number instead of the instance name itself.
This is what we would see in the JDBC Log output:
Oct 28, 2008 10:39:19 AM com.microsoft.sqlserver.jdbc.SQLServerConnection getInstancePort
FINE: ConnectionID:1 TransactionID:0x0000000000000000 Unexpected UDP timeout at 1 seconds resolving instance port. Target -> udp:myserver2/10.0.0.2:1434.
Oct 28, 2008 10:39:28 AM com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:com.microsoft.sqlserver.jdbc.SQLServerConnection@b09e89 com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the named instance has failed. Error: java.net.SocketTimeoutException: Receive timed out. The connection to the named instance has failed. Error: java.net.SocketTimeoutException: Receive timed out.
This issue is actually going to be addressed in the 2.0 release of the JDBC Driver and should not be a problem.
Adam W. Saxton | Microsoft SQL Server Escalation Services
Comments
Anonymous
December 31, 2008
PingBack from http://www.codedstyle.com/sql-2005-jdbc-driver-and-database-mirroring/Anonymous
January 05, 2009
We've heard from some customers that they are having difficulty getting Database Mirroring to work with- Anonymous
December 28, 2015
we still have same issue on SQL 2008. May i know how can we fix this?
- Anonymous
Anonymous
October 24, 2011
We are seeing this issue on SQL 2008, any ideas?- Anonymous
December 28, 2015
hi , is your problem resloved? would you please share some with me ? thanks
- Anonymous
Anonymous
December 28, 2015
i got the same issue on MSSQL 2008, any idea? thanks