Backend: remote vs local sql database

Question

What is your position on having the (relational) database on the same machine as the application server if you want to have a scaleable backend which can handle around 200 simultaneous requests?

I see most are discouraging this approach with the following main arguments:

• Scaleability is more tricky
• Security (as both AppServer and DB on same environment)
• Resource competition of DB and AppServer
• Single point of failure

However, I still see alot of advantages by going with the approach to have both on the same server, being:

• Costs: in particular, egress costs: having a seperate server for my db will result in almost twice the outbound transfer costs (once from db to the appserver, and once from appserver back to the client). At least that's the case for almost all providers, who charge for your server's outbound traffic. An issue I would not face if the db was simply on the same server. Of course you can decrease it by efficiently making use of your appserver's cache, but the main issue still remains.
• Speed: eventhough you'd still access the local db via TCP, localhost will simply be faster
• Scaleability: where is the issue really, I could just run the DB in an own VM and scale by upgrading hardware and assigning the hardware to the VM which needs it. Or scale horizontally with a new VM/K8s/whatever instance, even if both my AppServers and my DB are on the same machine
• Same resources: again, by having both in their own VM, I could just assign the hardware resources whereever they are needed - no competition at all?

From what I see it, the question to put the db on a remote server might simplifies things and improves your security, but I also see alot of advantages with the other approach such as costs on outbound transfer. So is it safe to say that it's very situational and based on your actual project needs?

(I intentially do not mention all-in-one SOA architectures from Google, AWS, MS Azure, as their costs simply don't make them an option).

Answer 1

Here are my two cents on this topic as per my understanding:

When the application is new and until its volume and size is relatively small, it can be okay to have the DB on the same box as app server. However as the time goes by several issues crop up in this setup. The points that you have mentioned are valid, however, in the long run, the benefits of having db on a separate server may outweigh the single server setup.

• The system can be scaled vertically by increasing capacity of the server. But as we know after a certain point, the amount of capacity gained per hardware cost declines which means a little capacity gain is achieved compared to the increase in hardware cost for a single machine. So after a point, in order to achieve cost effective scalability, going horizontal is the only solution. Now when going horizontal, assuming that more machines are added and they have both app server and db server included in the same box (with different VMs or containers), when it comes to scaling, both app and db servers can easily have different scaling needs. A box with higher capacity is needed to handle both app and db load. Let's say another set of boxes are added to increase the capacity but then it results in provisioning of these higher capacity boxes which will be a waste because let's say only app server needs to be scaled and the db is fine or vice versa. Instead if app and db servers were on different boxes, the scaling can be managed individually.

• As mentioned in the question, the security is another issue. The app servers are generally facing the outside world and are more vulnerable to attacks and if db is on the same box, it will un-necessarily face the same risks. Instead if db is on a separate box, it can be behind the firewall and not exposed to internet at all.

• If either of App or DB server results in system crash, both components will become unavailable (Single point of failure).

• Whenever there is a need to alter the system configuration, there is a bit of risk involved and if both app and db are on the same box, this risk is faced by both components, even if the system change is required by one of the components.

• The I/O, CPU and other hardware capacity requirements of app and db components can be very different. And even if containerization is applied, it may not be possible to split the resources of the box in an optimal way. Instead it is far more simple to do capacity planning if both components are deployed separately.

So is it safe to say that it's very situational and based on your actual project needs?

Any deployment must always meet the project specific needs. That being said, in most cases where the application gets bigger so as to not fit on a single machine, generally separating out the app and db tiers on different machines has much more practical advantages than having a single setup as per my understanding.

Hope it helps!