I'm listening to podcast #19 and Joel and Jeff are arguing about running SQL Server on the same machine as your IIS installation. I have to say that it sounds like Jeff folded to Joel, but then again I am only guessing which one is which. ;)
What are the pros and cons of each? Which is better?
I typically run them separately (Joel's preference) but I can see Jeff's point where the two are closer to each other.
For security purposes it is good to separate web and database machines, preferably having a firewall between the two. A web server is exposed to the world at large. Unfortunately there are people who take pleasure in stealing or damaging the information contained on those servers.
Then there is the performance aspect. It's common knowledge that SQL Server loves memory. So does IIS, particularly if the web-site makes extensive use of caching and session information. So you have a potential conflict here as well. Having a dedicated machine for SQL Server is clearly better than having a single machine doing all the load.
Then, separation allows easier identification of the need to tune and the ability to tune individual hardware components.
To sum up, a machine powerful enough to cope with the demands of both IIS and SQL Server in a live environment won't necessarily be cheaper than two machines specced for the specific requirements of each server. (Jeff Atwood mentioned in one of the podcasts, that upgrading the one machine would have cost the same as getting a second machine).
Putting them on the same machine:
If the application does not need redundancy and doesn't need to scale out, putting them on the same box is definite win - it's far easier to maintain.
I don't think the security argument carries any weight - I don't see any security benefit of separating them. The web server would need to have enough access to the database to view and modify all or most of the data anyway, so if it were fully compromised, the SQL box would effectively be compromised too.
@MarkR
Security is indeed enhanced by moving SQL Server to another box and it's to do with the Attack Surface exposed.
The web server is exposed to malicious access from the Internet. One hopes it would never happen, but there have been (and could in future, be) vulnerabilities that can be exploited via malformed requests that traverse firewalls.
Exploiting one of these vulnerabilities could lead to arbitrary code being able to execute.
In the event that the web server is compromised in this way, anything else that runs on that machine is now vulnerable and exploit software could potentially run in a privileged context. The attack surface of the compromised machine is much wider.
If SQL Server is installed on the same machine, any database is vulnerable.
Now, if SQL Server is installed on a separate machine, it can itself only be accessed via its public interface. The attach surface of the database is limited to that interface. So, to compromise the database, you now have to compromise the web service first, THEN the SQL Server. This is MUCH more difficult than having them on the same machine.
Extending the principle further, it's also an argument for the use of stored procs. If the web server is only able to access the database server using stored procs, the interface, and hence the attack surface, is massively constrained. If the web server is able to execute arbitrary SQL against the database server, the attack surface is again much bigger then it needs to be and the risk to the data is greatly increased.
In systems where data is valuable, these risks, while relatively small, are very real and determining the business exposure of such risks is an essential aspect of solution design.