MSSQL JDBC driver does not connect to failoverPartner mirror when first connected

Question

I use the C3P0 driver and MS SQL JDBC 4 to automatically switch to another resource when restoring the database. If it first connects to the main database, then the transition to another resource works, and it automatically switches to the mirror database. However, if the main database does not work when the application starts, and the mirror database is available for connection (tested using MSSQL Studio), the application does not start and cannot 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 NOT installed (the default is 30).

Why doesn’t it connect to the mirror database initially when the main one does not work? We need this, because if the power decreases or something, and the main database does not work, and the application server needs to be processed, then a failure on failure will not help.

Link:

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

Using Database Mirroring (JDBC) (MSDN uses unshielded brackets in its 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 another type of error that he sometimes gives, with a warning about blocking.

 <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@527838 59 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@52bb85 5b (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@153043 cc (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 throws this exception as if it was trying to use a different port than what I indicated!

 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 was that he tried to connect to port 1433 instead of the port I specified in different ways.

Answer 1

I found the answer! You must 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 quite sure what an instance is, but I have seen SQL Server mention it many times. To find this secret information, I (my company) had to request support directly from Microsoft.

Oh, forgot:

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

Answer 2

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

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

From this, I would suggest adding: <Strong> server_name = PRINCIPALDB; instance_name = MyInstance properties and see if this works.

---

You note that the transition to another resource is active. Note that for some Sql mirror server configurations, fault tolerance is not automatic . You can verify that you can connect to MIRRORDB using jdbc:

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