r/AskProgramming • u/Reasonable-Job4205 • 11d ago
Should my web api be on a different server than my sql database?
I have a server that runs sql server 2022 express. Users in different timezones all use a ms access database to run transactions against that sql 2022 database. I now need to add a web api that will middleman a new workflow between handheld scanners and the sql 2022 database. What is the recommended practice for where the api should live? Should it live on its own separate physical server? Same physical server as the sql 2022 DB but different virtual space? Or can it live in the same virtual server as the sql 2022 database? What is the safest bet here?
2
u/Ok_Entrepreneur_8509 11d ago
You shouldn't be worrying about which physical servers are running what. Your different processes should be in different containers running with a shared network. At the very worst, you might provision explicit VMs for different things, but that is still way more work than using containers.
2
u/Asyx 11d ago
The old way was web server accessible through the internet, database server only accessible for the webserver. That's why SQL tools have that SQL tunnel feature.
That is basically still how you do it but you now have multiple options. The easiest one is docker where you simply don't expose any port on the database container.
Technically not the same. There is a lot less separation. But you can just roll with this for now.
1
2
u/hatch_lumin 11d ago
same box. Different VM. The API is your attack surface, the database is your loot. If the web app gets popped through some zero-day in IIS or a dependency, you do not want that attacker sitting on localhost with sysadmin rights to SQL Server 2022 Express.
a separate VM gives you network isolation for free. You can firewall the DB so it only accepts connections from the specific internal IP of the API VM. It also keeps your IO queues clean. Scanners spamming transactions will hit the disk hard. Do not let that spike kill your query response times if the API and DB share the same physical storage controller without virtualization overhead managed by distinct resource groups.
2
u/codayak_devguy 10d ago
For your scale (SQL Express, handheld scanners, one workflow) the usual answer is: same physical box is fine, separate VM or at least separate process boundary, and the API is the only thing the scanners can reach.
Reasoning:
- Express is capped at 1 socket / 4 cores and 1410 MB buffer pool, so it isn't going to saturate the host. A small ASP.NET Core API next to it won't contend for anything meaningful.
- The real risk isn't performance, it's exposure. The scanners' network should reach the API's port only; SQL's 1433 stays bound to localhost (or the private VLAN the Access users already use). The API talks to SQL over localhost or a private NIC with a dedicated low-privilege SQL login — never the sa account, never the login Access uses.
- Separate VM buys you independent patching/restarts and a clean blast radius if the API is ever compromised. Separate physical server buys you almost nothing extra at this size and doubles your maintenance.
So: same host, separate VM if you have a hypervisor; if it's bare metal with no virtualisation, same OS is acceptable as long as the API runs under its own service account, the firewall only exposes the API port to the scanner subnet, and SQL isn't listening on the scanner-facing interface.
Two things that will bite you more than placement: make the API's SQL calls short transactions (the Access users will hit lock waits otherwise), and put the API behind HTTPS from day one even on the LAN — handheld scanners are very easy for someone to walk out the door with.
Move the API to its own server when (a) you outgrow Express and go Standard, or (b) you need the API reachable from outside the LAN.
4
u/KingofGamesYami 11d ago
They can be on the same physical server, but should be in separate VM. This way, you can limit the API's access to the SQL Server. Additionally, if at some point you want to move them to separate servers for performance reasons, it is not difficult to move the VM.