MSSQL JDBC driver won't connect to mirror failoverPartner on first connect

Question

I'm using C3P0 and the MS SQL JDBC 4 driver to automatically failover to a new database mirror when the database goes away. If it first connects to the principal DB, then the failover works and it switches seamlessly to the mirror DB. However, if the principal DB is down when the application starts, and the mirror DB is available to connect (tested with MSSQL Studio), then the application fails to start and fails to connect to the backup mirror.

Here is the connection URL:

jdbc:sqlserver://PRINCIPALDB;databaseName=app_space;port=99999;failoverPartner=MIRRORDB

I have c3p0.testConnectionOnCheckout and c3p0.preferredTestQuery set, and c3p0.acquireRetryAttempts is NOT set (using default of 30).

Why won't it connect to mirror DB initially when principal is down? We need this because if power went down or something and principal DB is down, and app server needs recycling, then failover won't help.

Reference:

http://www.mchange.com/projects/c3p0/#configuring_recovery

Using Database Mirroring (JDBC) (MSDN uses unescaped parenthesis in their URLs!) http://msdn.microsoft.com/en-US/library/aa342332(v=sql.90)

---

Here are some logs from the app.

<14>[APP]: INFO 20 Jul 2012 12:21:21,982 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "C3P0 using driver: com.microsoft.sqlserver.jdbc.SQLServerDriver at URL: jdbc:sqlserver://PRINCIPAL;databaseName=APP_space;port=9999;failoverPartner=MIRRORDB"    
<14>[APP]: INFO 20 Jul 2012 12:21:21,982 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "Connection properties: {user=USERNAME, password=PASSWORD}"    
<14>[APP]: INFO 20 Jul 2012 12:21:22,435 [main] net.sf.hibernate.transaction.TransactionFactoryFactory "Transaction strategy: net.sf.hibernate.transaction.JDBCTransactionFactory"
<14>[APP]: INFO 20 Jul 2012 12:21:22,450 [main] net.sf.hibernate.transaction.TransactionManagerLookupFactory "No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)"    
<12>[APP]: WARN 20 Jul 2012 12:29:17,279 [main] net.sf.hibernate.cfg.SettingsFactory "Could not obtain connection metadata"    
<12>java.sql.SQLException: Connections could not be acquired from the underlying database! 
<12>    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) 
<12>    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) 
<12>    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) 
<12>    at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:33) 
<12>    at net.sf.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)

And here is a different type of error that it sometimes gives, with a deadlock warning.

<14>[APP]: INFO 20 Jul 2012 18:05:43,049 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "C3P0 using driver: com.microsoft.sqlserver.jdbc.SQLServerDriver at URL: jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;failoverPartner=MIRRORDB:9999"
<14>[APP]: INFO 20 Jul 2012 18:05:43,049 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "Connection properties: {user=USERNAME, password=PASSWORD}"
<14>[APP]: INFO 20 Jul 2012 18:05:43,190 [main] com.mchange.v2.log.MLog "MLog clients using log4j logging."
<14>[APP]: INFO 20 Jul 2012 18:05:43,518 [main] com.mchange.v2.c3p0.C3P0Registry "Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]"
<14>[APP]: INFO 20 Jul 2012 18:05:43,612 [main] net.sf.hibernate.transaction.TransactionFactoryFactory "Transaction strategy: net.sf.hibernate.transaction.JDBCTransactionFactory"
<14>[APP]: INFO 20 Jul 2012 18:05:43,612 [main] net.sf.hibernate.transaction.TransactionManagerLookupFactory "No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)"
<14>[APP]: INFO 20 Jul 2012 18:05:43,658 [main] com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource "Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@616301db [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@d6ed198b [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1bqq23w8o1a6dec41cwe1cd|20e1bfee, idleConnectionTestPeriod -> 100, initialPoolSize -> 10, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxI...
<14>...dleTime -> 3600, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 150, maxStatements -> 1000, maxStatementsPerConnection -> 0, minPoolSize -> 10, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@2c0fb781 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1bqq23w8o1a6dec41cwe1cd|20360e46, jdbcUrl -> jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;failoverPartner=MIRRORDB:9999, properties -> {user=******, password=******} ], preferredTestQuery -> select * from CLUSTERSAFETY, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 1bqq23w8o1a6dec41cwe1cd|6f3e49a8, numHelperThreads -> 3 ]"
<12>[APP]: WARN 20 Jul 2012 18:06:03,644 [Timer-0] com.mchange.v2.async.ThreadPoolAsynchronousRunner "com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@37f844f7 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!"
<12>[APP]: WARN 20 Jul 2012 18:06:03,644 [Timer-0] com.mchange.v2.async.ThreadPoolAsynchronousRunner "com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@37f844f7 -- APPARENT DEADLOCK!!! Complete Status: 
    Managed Threads: 3
    Active Threads: 3
    Active Tasks: 
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@52783859 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@52bb855b (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@153043cc (com.mchange.v2.asyn...
<12>...c.ThreadPoolAsynchronousRunner$PoolThread-#2)
    Pending Tasks: 

I ran a test program from the documentation with this connection:

jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;portNumber=9999;failoverPartner=MIRRORDB:9999

and it throw this exception, like it was trying a different port than I specified!

Connection to principal server failed, trying the mirror server.
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host MIRRORDB:9999, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

The important point being that it tried to connect to port 1433 instead of the port that I specified, many different ways.

Answer 1

I found the answer! You have to specify the instance name as part of the host name! Example:

jdbc:sqlserver://DEVSQLB\SQLB;databaseName=db_space;portNumber=99999;failoverPartner=BACKUPSQLA\SQLA

where \SQLA is the instance name! I'm not exactly sure what an instance is, but I've seen it referred to many times in SQL Server. To find this secret information out, I (my company) had to request support directly from Microsoft.

Oh, forgot:

• You can fix this issue by using the format "server\instance_name" and make sure that the browser service is running and in automatic mode.

Answer 2

The Sql Server JDBC driver documentation here has the following (confusing) disclaimer:

Note The driver does not support specifying the server instance port number for the failover partner instance as part of the failoverPartner property in the connection string. However, specifying the serverName, instanceName and portNumber properties of the principal server instance and failoverPartner property of the failover partner instance in the same connection string is supported.

From this I would suggest adding: serverName=PRINCIPALDB;instanceName=MyInstance properties and see if that works.

---

You mention that the failover is active. Note that for some Sql Server mirror configurations a failover is not automatic. You may want to verify that you can connect to your MIRRORDB using jdbc:

jdbc:sqlserver://MIRRORDB;databaseName=app_space;port=9999;