3

I am coming from an application development perspective (my day job) and not an infrastructure, or operations, perspective, so if this is a completely inane question, please let me know!

We have proxies in front of our application servers and this has typically served us well. This (hardware/software) proxy provides an abstracted endpoint (in addition to other services, such as load balancing/distribution) so that our clients always know how to connect.

Does anyone have any experience putting a proxy between application servers and the the database?

Seems like someone has got to be doing this, especially in hosting provider, or cloud space, but when I've talked to our (enterprise) infrastructure teams they just say 'no' though I'm still struggling to understand why.

I don't understand if due to the extra layer of abstraction in front of the database this causes functional or performance issues, or just plain makes it harder to trouble shoot when things go wrong.

Can anyone share thoughts, experiences, or resources in this area? I would greatly appreciate it!

Thanks!

Z

Zach Bonham
  • 210
  • 1
  • 3
  • 10

3 Answers3

4

Main problems:

  • Database connections are way more persistent between application servers and backends - so load balancing is hard through them.

  • Proxies must speak the protocol. There are tons of HTTP proxies there. Try getting one for example the SQL Server protocol. No need, no market - no product.

  • Finally, though what SQL Servers are, caching is not possible. Not without a protocol to invalidate results etc., and that is not part of any SQL implementation. No "if modified since" tag. Ergo, you would either gain nothing or deal with outdated data - both not acceptable on that layer.

Sharuzzaman Ahmat Raslan
  • 420
  • 1
  • 5
  • 15
TomTom
  • 51,649
  • 7
  • 54
  • 136
  • Thanks for feedback! I might need to update my question w/more detail. I'm looking for a passthrough proxy to the server/cluster name (no caching, balancing, distribution, etc). Just looking to create a virtualized name that never changes. Server/cluster name can be rolled into/updated in VIP as needed. I didn't think about the protocol being an issue. Isn't Sql Server (e.g.) just tcp/ip? I"m guessing there is more to it than that! :) – Zach Bonham Sep 23 '10 at 18:51
  • Yes, it is just TCP/IP - but if you cache on that level, you basically gain absolutely nothing except some manageability, which may not be worth it. – TomTom Sep 23 '10 at 19:28
  • 2
    Do not use a proxy. Just use a CNAME to the server with the database name. Then you have a stable server name (similar to db name) and connect to that one. No proxy needed. – TomTom Sep 23 '10 at 19:29
  • Using Microsoft Cluster Services, that makes a VIP and DNS Name for the cluster, the client never knows what machine in the cluster is being used. – mfinni Sep 23 '10 at 19:55
  • Sadly that does not really solve anything in the larger scale... like moving databases between server (clusters)... AND it adds a LOT in terms of hardware requirements (shared storage) and nothing for uptime. – TomTom Sep 23 '10 at 20:31
  • The CNAME record sounds so simple? Is that not something that can be done easily? Would that be considered a best practice? If not, If you were managing a large number of DB clusters, how else would you do it? – Zach Bonham Sep 24 '10 at 01:49
  • @TomTom I'd like to mark your CNAME response as the answer, can you update your answer to reflect that in your response? – Zach Bonham Sep 24 '10 at 01:50
4

I realize this is an old question, but at this time there are at least a couple of decent database proxy products to choose from. That is, if you're using MariaDB or MySQL:

  • MaxScale, claims to be the world's most advanced database proxy
  • ProxySQL, which has many of the features found in MaxScale

I only have experience using MaxScale, which I feel I can recommend at this point. MaxScale works really well e.g. with Galera clusters. It's taken a little while to mature, perhaps because it's a relatively new concept developed from scratch, and it's solving some non-trivial problems.

It should be noted that MaxScale has a special non-open source licence - BSL. (ProxySQL on the other hand is licenced under GPL.)

I could also mention HAProxy which is not a database-aware proxy, but has nevertheless been widely used successfully as a database proxy both for MariaDB/MySQL and PostgreSQL.

dbdemon
  • 181
  • 6
3

I also like TomTom's DNS idea. Short TTLs and this is easier than you're making it to be.

But you can certainly run a proxy in front of your databases. SQLProxy and HAProxy can do this, for example. But again, if your concern is not load balancing or separating reads and writes, why add this extra level of latency (to and from the proxy) when you could just do it with a CNAME -- and I think that the argument is the one your Ops/Systems guys will consider.

wet
  • 46
  • 1
  • "why add this extra level of latency (to and from the proxy) when you could just do it with a CNAME" -- certificate validation? Say the backend is an azure host and you want to proxy through a domain that you own. The connection would fail if you use the cname "mydomain.com" because the server will present for "someazuredomain.com" – Sinaesthetic Apr 16 '21 at 19:28