fudder_30936
Jul 07, 2010Nimbostratus
Problems with .Net connection pooling through a MSSQL VIP
I've got a pool managing a couple SQL servers in an active/inactive rollover approach - there's only ever one in the pool at a time.
Initially I was enabling/disabling the nodes in the pool, but I found that due to connection pooling the active connections did not cut over.
Instead, I've used the MSSQL monitor to flag which server is active/inactive, so the nodes are actually taken "Down" instead of just "Disabled". Things work better, except that the very first query after a rollover errors out, as the connection is dropped. I've played with the pool's "Action On Service Down" settings, but none of the options (None/Drop/Reject/Reselect) seems to solve the problem. Reselect seems to be what I want, but it doesn't seem to work as described - the first call for each active pool after the switch still returns a connection error.
Short of turning off connection pooling, any ideas for solving this?