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

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.

Solution 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.

Solution 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.