Home / server / Questions / 319230
Accepted
Gabriel Guimarães
Asked: 2011-10-07 12:58:09 +0800 CST

Implications of having IIS on SQL Server 2008 R2 machine

  • 772

an application needs to have IIS on the same box of Reporting Services.

Today SSRS and SQL stay on the same clustered box.

I need to evaluate the options which for me are:

  1. Installing IIS on both Cluster nodes
  2. Installing IIS and Reporting Services on another machine

I need to find out what are the bad and good points of having IIS along with SQL

And the bad and good points of having the other way.

I know price is high if having another machine (or vm) just with reporting services and IIS.

But what are the bads of having IIS on a SQL machine.

PS: All of this is on my intranet, not exposed.

sql-server iis windows-server-2008-r2 sql-server-2008-r2

4 Answers

  1. Best Answer

    IIS and SQL Server have no known incompatibilities, they are just two different services which can happily run on the same machine, provided hardware resources are adequate to support both applications at the same time, and you secure them properly.

    Actually, Reporting Services used to run on IIS as a web application: it's only with SQL Server 2008 that it became an application on its own, no longer depending on IIS; before that, Reporting Services required IIS in order to run.

    Now, about your setup: if you want to run IIS on both cluster nodes, you should be aware that IIS is not designed for clustering, buth for load balancing; it can run in an active/passive cluster, but you'll need to take extra steps in order to have it failover gracefully from one node to another. The exact steps depend on what your application is doing.

    • 5

  2. This is simple math.

    Having one service on a box will only expose one service for threats. So if you can compromise it you gain only access to that machine/service. But having two services on one machine you have doubled the risk. Compromising one (the least weak link) automatically compromises the second one.

    So it is on your own to project this for having three, four, five, ... services on one host.

    • 2

  3. A big thing you'll want to evaluate when running anything side-by-side with SQL Server is how to configure SQL Server's memory usage. By default it's set to use the maximum amount of system memory available, and will actively try to do so. It will load entire databases into memory as much as possible so if you have large databases you will want to look into setting Maximum Server Memory to something that will allow you to have enough memory for IIS as well.

    You can do this a couple of ways:

    In SQL Server Management Studio, right click on your server instance in Object browser and click on Memory in the page list. Set the value in the Maximum server memory box.

    or

    Run this:

    sp_configure 'show advanced options',1
GO

RECONFIGURE
GO

sp_configure 'max server memory (MB)',[some value]
GO

RECONFIGURE
GO

    replacing [some value] with the amount of memory in MB to which you want to restrict SQL Server.

    • 2

  4. As long as everything is properly configured, passwords are reasonably complex, and people can't upload random code to your server; I really don't see any downsides.

    The downside to having multiple IIS boxes (assuming you're running some kind of web application here) is that you will need to consider session management options; if someone boucnes from IIS1 to IIS2 and they cannot share session states you're likely to have problems (they might not seem to be logged in suddenly, things like that).

    • 1