Hi, I am having trouble with database failover. I have set up 3 connections: “a” (primary db) which is ok, “b” (secondary db) which is ok (and connecting to a different machine) and c which connects to the same db as “a”, but fails over to connection “b” on failure of the primary db. Now the problem I am having is “c” is not failing over to “b” when I kill the primary.
I know it has nothing to do with the db, because I can connect to either database individually with at least two connections to each server from ignition, and the connection to the “b” server is still valid when the “c” database fails. It is as though it doesn’t realise it has failed to initiate a failover, even though it goes into fault. I have also tried restarting the gateway.
Any ideas what could be causing this? Given the simplicity of what I am trying to do, (i.e selecting the failover source from a combo box is not something that is easy to get wrong), I am out of ideas on what else to look at.
Update: After rebooting the primary gateway hardware, and killing the server again, the clients seem to be utilising the failover connection. This doesn’t really fill me with confidence. The interesting thing is that the connection still says faulted and the store and forward still keeps changing from available to unavailable and vice versa in the gateway web interface.
I am still having issues with this - but moreover, I have clarified a bit more the issue I am having most commonly:
I have 24 connections possible to my db (I expanded from the default). I have a really complex/long select query that I do, and it starts to increase the number of connections for some reason, possibly due to timeouts that don’t close from some reason. At a certain point, maybe when it gets to about about 7 connections it falls over, the problem is it is the connection that should failover to another database (“c”, in my first post), and the other two connections are fine. It just sits there, failed with 7 connections, does not attempt to recover.
How is it that when I use fail over for availability, I seem to be getting less availability than using a standalone database. It doesn’t add up.
Are there any ways to make the database connection try to recover or reset at least without going into the gateway web interface?
And more importantly, why/how is this happening?
Also, query time is extremely volatile, a few seconds to half an hour