Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Should SQL Server be on the same machine as your IIS installation? [closed]

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.

like image 491
Jason Avatar asked Nov 08 '08 15:11

Jason


People also ask

Why should the database and Web server be installed on separate machines apart from the rest of the network?

Advantages: Isolated database cannot be stopped by overloaded processes on the web/application server. The database is further isolated from a security perspective. Problem diagnosis and performance monitoring is easier as the web and database loads are separated.

Does IIS need SQL Server?

IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation.

How do I give IIS permission to SQL Server?

Right click logins and select "New Login" In the Login name field, type IIS APPPOOL\YourAppPoolName - do not click search. Fill whatever other values you like (i.e., authentication type, default database, etc.) Click OK.

Is SQL Server running on my computer?

Click Start, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager. If you do not have these entries on the Start menu, SQL Server is not correctly installed.


3 Answers

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

like image 160
splattne Avatar answered Oct 25 '22 18:10

splattne


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

like image 34
Steve Morgan Avatar answered Oct 25 '22 18:10

Steve Morgan


Putting them on the same machine:

  • Reduces latency between them - so if you have lots of easy queries, this can improve performance
  • Make your development and performance testing easier because you can do it with a single box (or VM)

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.

like image 39
MarkR Avatar answered Oct 25 '22 17:10

MarkR