MSSQL JDBC驱动程序将无法连接到镜像故障转移

我正在使用C3P0和MS SQL JDBC 4驱动程序来在数据库消失时自动故障转移到新的数据库镜像.如果它首先连接到主体数据库，则故障转移将起作用，并且将无缝切换到镜像数据库.但是，如果在应用程序启动时主体DB处于关闭状态，并且镜像数据库可用于连接(已通过MSSQL Studio测试)，则应用程序将无法启动并且无法连接到备份镜像.

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.

以下是连接网址:

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

我设置了c3p0.testConnectionOnCheckout和c3p0.preferredTestQuery，但未设置c3p0.acquireRetryAttempts(使用默认值30).

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.

参考:

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

使用数据库镜像(JDBC)(MSDN使用未转义的括号在他们的网址中！) http://msdn.microsoft.com/zh-CN/library/aa342332(v = sql.90)

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)

重要的一点是，它尝试通过多种方法连接到端口1433而不是我指定的端口.

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