AIX 5.3
10.1B
I've been trying to come up with a good way to handle severed database connections in webspeed environment. We have a site running on webspeed with local database. I've connected another database running as service on another server and handle data retrieval with connected() function and external procedures to make sure that site's procedures run even if the other server is down.
Now, the issue is in what happens when the external server / connection is up and running again. I've come up with few ideas but I think they're a bit poor:
1.) Manually renewing the db connection. This works but doing this kind of thing manually is... well, not preferred.
2.) Use connect() in procedures to renew connection. This has few issues:
* users browsing the site will get stuck while the procedures are trying to connect to the server.
* there will be multiple users running connect() simultaneously. There might be hundreds of page loads in a minute. This could be handled with some kind of connection renewal interval -variable but it wouldn't remove the situation where a user would get stuck on waiting for the connection to renew.
3.) Like in 2. but sending request to the 'connector' procedure with AJAX, so that the normal usage of the page(s) wouldn't be affected but the connector would still be launched.
4.) A better way which I'm not aware of?
Thank you for any ideas.
Personally I like #3 but I don't really feel like I understand your application well enough to say if that's a good thing or not.
You might also consider talking to an appserver or communicating via Sonic rather than connecting directly.
Having said that... losing your connection should be a very rare thing. Obviously smoothly re-connecting is important no matter how infrequent the event but it sounds almost as if this is a routine occurence. If you're losing your connections regularly maybe you also ought to be looking into why that is and fixing the root cause of that problem.
Yes, I agree that losing connection should be something that happens rarely. But in this case it actually occurs daily, as the other database is shut down for nightly backups and maintenance. On the other hand the webspeed server is running 24/7.
We actually have been using Sonic for communicating with the external db. I've tested this with appserver as well and found it working pretty well. But I personally think that both methods just add unnecessary complexion to this particular environment (and coding as well).
Thank you for your comments!
You don't need to shut down the other database for nightly backups as you can make backups while the database is on line.
I don't know what the nightly 'maintenance' is but maybe by making on line backups you can reduce the problem to once a week or so and come up with a way to handle it during off hours in the weekend or so.
I will be discussing about the possibility of online backups.
For now I'll set up a script to restart webspeed broker after the database on other server is running. This causes a tiny 'downtime' but it's not an issue in the middle of the night.
Something I noticed while testing connect / disconnect functions: These functions affect only the agent assigned by the broker. This certainly makes it a bit harder to manage connections over webspeed...
Thank you for your comments!