Hi:
I want to add database failover to my web application
I use Tomcat 6 and SQLServer database + JDBC Driver 3.0
I'm getting connections from datasource with
DataSource ds = ic.lookup("java:comp/env/jdbc/mydb");
Connection con = ds.getConnection();
I know that I've got to define a DN like
jdbc:sqlserver://DEVSQLB\SQLB;databaseName=db_space;portNumber=5555;failoverPartner=BACKUPSQLA\SQLA;
But my doubts are about what happen with pooled connections.
The scenario is my pool is populated by connections to primary server.
Then, the primary server fails and a failover happens ( mirror ->
primary )
I'm not talking about the case where the primary server is down and
DBCP tries to create new connections
About SQLServer documentation
"When the principal database server fails, the client application
receives errors in response to API calls, which indicate that the
connection to the database has been lost. When this occurs, any
uncommitted changes to the database are lost and the current
transaction is rolled back. If this occurs, the application should
close the connection (or release the data source object) and try to
reopen it. On connection, the new connection is transparently
re-directed to the mirror database, which now acts as the principal
server, without the client having to modify the connection string or
data source object."
If I close my connection ( con ) , I returns it to connection pool,
and I'll have a "bad" connection in the pool.
If I get another bew connection with Connection con =
ds.getConnection(), i'll retrieve from the pool connection and it
will be a "bad" connection.
In conclusion, all old connections in my pool will be "bad"
connections after the principal database server fails
I'd need to release all old connections and create new connections ( I
don't know how these connections know what is the primary server now ,
I''llbelieve it )
How do you resolve the failover mechanism when connections are
retrieved from a connection pool ?
Do I need to clear the pool when a failover happens ?
Would a ping query in datasource solve this issue ?
Thanks and regards
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
