Should SQL Server be on the same machine as your IIS installation?
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.
Answer
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).