Should the Database be on the same box as the web server or separate

Question

Im using SQL Server 2005 and IIS6. I have each on separate boxes (Quad cpu, 4G). Client wants to consolidate on to one box. This is an Enterprise application with 400 users. Performance is good now, but I question any savings to consolidate with performance changes, management of services. So for 1) future features/growth , 2) performance, 3) Modularity, other?, move to 1 box or keep on 2.

Answer 1

I'm pretty much 100% Linux-based, but I think some common principles apply.

If you expect to scale, having the servers separate even if just using virtualization software provides you with the ability to easily add nodes as needed and load balance between them - it also provides for better security, as you only open the ports you need.

Having a database server on the box I found is good for supplementary services like mail servers, or some other configuration tool that uses a backend database... these footprints are relatively small, and scalability is less of a concern.

Answer 2

There isn't really enough info to tell you which direction to take, it's really going to depend on your environment. To make the right call you will want to gather metrics on both servers in their current states and analyze what you've got. You need real data to prove out the point, either way. "Enterprise application" doesn't really say much with regards to metrics other than that "it might be big". You might be able to combine but if you have to buy new hardware with more horsepower then you might be shooting yourself in the foot.

A couple of thoughts (mainly from the database perspective):

• One security theory is that if an attacker gains access to your web server then they've gained access to the database server. This may or may not be necessarily true, depending on how the attacker gains access, how you've set up the security on your web server and how your connection strings are stored. It's certainly worth paying attention to, though.

• SQL by default will consume as much memory on the server as it can. When hosting web and SQL Server on the same box finding the happy place where they both perform acceptably will take a little planning and some tuning. This will constantly shift as your database grows and your application use goes up. Not that it won't constantly shift anyway, just that sometimes it's harder to find the balance between two services

• You won't have the overhead of network connections from the web server to the SQL server.

Answer 3

What are the supposed benefits of consolidation?

• Maintenance? Hardly, it will be slightly harder at best.
• Cost? Perhaps... for now.

If you want the best of both worlds, you could setup two VMs on the one host. As an when either of them grows too big, you can just move it to a new host (or perhaps to a new dedicated machine).

In general, I'd advise against running them on the same box in a production environment; apart from the security implications, configuration and maintenance won't be as easy as both roles will always be jostling for resources.

Answer 4

Some security folks don't like having a web server and database server on the same machine. Ours let it slide if we install IIS on a separate partition.

Answer 5

I would say, try it on a non-production copy of your system and see.

Any significant application which is actively being supported should be able to be performance-tested. Get production-grade hardware boxes (the same spec as you would deploy into production now - not obsolete) and test it with a single box, and two boxes.

My guess would be that putting the database on the same machine performs better provided the database+application fits in ram - and worse otherwise. This effectively means that putting them on the same machine improves performance but reduces scalability. This performance vs scalability trade-off is typical.

But do try it and see. Be sure to use production-grade hardware and some software to simulate at least your production level of usage.

There are also implications for robustness (of using a single machine). But you could have others as warm(ish) backups without impacting production too much (depending on what type of replication/clustering etc you use).