In a previous post I proposed using a database connection proxy in order to improve security. There are a number of other reasons why this is a good approach.
First line of defense routing transactions - if the application is connected directly to the DB then the application needs to add software that allows it to switch from primary to alternate DBs. This would essentially add unnessary complexity to the application. (simple is better). By creating a proxy the ability to switch traffic in a coordinated fashion will have many benefits.
Capacity and monitoring - Most databases do a lot to queue transactions from the client. That capacity is typically wellknown to the DB vendor, however, they never take into account the amount of other production events. For example there might be some extra disk I/O taking place on the DB server that the vendor would not be able to account for. Furthermore monitoring is limited to the vendors tools and nothing that you’d be able to improve upon. Using the proxy as a funnel it makes monitoring and metrics easier.
TTL (time to live) - One complication is the TTL. When transactions are queued and the DB server is busy certain transactions may appear to timeout. Therefore measuring the TTL will be important. Most DB servers provide a deadlock timeout, however, that is different than the customary TTL.
Supplementing or replacing stored procedures - in addition to stored procedures many transactions need more compute power but since the dataset is too large or the number of roundtrips is too high then putting the “transaction” in the proxy would provide many benefits of running the full application on the DB server.
Queues can help SLA When restarting DB - from time to time the DB server needs to be restarted. This is terrible. In some cases the DB Server can be restarted in very few seconds but since the proxy has an incoming queue those transactions can be suspended while the DB connectivity is restored.
Impeadance options - when transitioning from DB vendor to another there are always a number of complications as the impeadence changes during the switch. There is also a disconnect between the code and the data. Proper implementation in the proxy layer gives you more choices.