Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL and IIS on the same Box?

By David Poole,

Introduction

If computers today are twice the power that they were this time last year then why do you need both a database server and a webserver?  Why can't you just install them on the same machine?

This question came, not from an naive IT person but from someone really important, the customer who was supposed to pay for it.

As the company I work for develops web solutions for, well, anyone who will pay us to do so, I often have to provide an answer for this question.

Take your IT hat off and you can see their point.   My laptop is a PIII 500Mhz machine and I have SQL 2000, IIS, MS Office, Outlook 2000 to name but a few. I even have Apache on there somewhere!

I do a lot of development on the laptop and it all works fine hence the difficulty in explaining why a Quad PIV 2.4GHz processor, 4Gb RAM 300Gb RAID5 machine should only be used for a database server.

My approach is to answer the question in the following way.  Please bare in mind that this is targeted at the end user.

Stability

SQL Server

Any enterprise level database server can be considered to be the safe deposit box for the businesses most precious asset, data. The main requirements for a database server are that it be stable and secure.   Anything that poses a threat to stability or security has to be neutralized.

Service packs and hot fixes for SQL Server tend to be few and far between. When these patches are released these patches tend to be extensively tested before applying in a production environment.

IIS

The nature of a web server is that it is exposed to the world at large. Unfortunately there are a tiny minority of people who take pleasure, not just in hacking into other people's servers, but in stealing or damaging the information contained on those servers.

There is a constant battle between the developers on one side trying to prevent people from hacking into their systems and the hackers on the other trying to circumvent all efforts to stop them.

OK I know that hackers are developers in much the same way that terrorists of which we approve are called freedom fighters!

This manifests itself in the release of security patches at regular intervals

Due to the nature of the internet these security patches have to be applied to the web server ASAP in order to close the potential security breach.  This puts pressure on the systems admin who cannot afford the luxury of extensively testing the patch before the potential security breach is exploited.

The patches themselves may introduce new bugs and instabilities into the system.

This need to apply panic fixes has obvious implications for stability.  I wouldn't go as far as to say that IIS is therefore inherently unstable but you get the gist.   In terms of stability SQL Server and IIS are at opposite ends of the scale.

Security

By definition a web server is usually exposed to the public.  It is not that IIS is particularly bad, it is more that an infinite number of monkeys with an infinite number of type-writers will not only come up with the complete works of Shakespeare but will probably find a way to crack your web-server in their banana break.

A database server tends to be kept secure behind a fire wall with very limited access to its functionality and what functionality is exposed requires password access.

If you put SQL Server on the same box as IIS then what happens if a hacker discovers a way to elevate their privileges and gain control over SQL Server?

SQL Server runs as with administrative privileges of the machine and has a number of powerful features to read and write to the registry, call operating system commands, talk to other servers etc.   What damage might be caused by someone gaining access to this sort of functionality?

Server Specialization

Database servers tend to be designed for resilience.

  • Multiple power supplies
  • RAID Arrays
  • ...etc
If you are on a tight budget then you tilt your spending towards resilience rather than raw performance.

Of course web servers need to be reliable too, but the emphasis has to be on blasting out web pages at high speed. Again you tilt your budget accordingly.

Of course you could build a big beast of a server that could do both things but as the law of diminishing returns comes into play I'm not sure how much of a cost saving there would be, if there was any.

Most web-sites seem to have at least some database driven content so I don't think that single point of failure can be added to the list of reasons but if there are two separate boxes then you have got to be terminally unlucky to have to reinstall both after a system failure with the time/cost involved.!

Performance

As I said in my introduction it seems strange to talk in terms of performance given the power of modern servers, however a server is only as fast as its slowest used component. If you have two applications fighting for use of that component then both will suffer as a result.

Realistically, if you have a web site then you have no idea of how much demand there is going to be on your web-server.  You could be sitting on the next Amazon or Napster!

We all know that SQL Server loves memory.   So does IIS, particularly if the web-site makes extensive use of session variables.   So you have a potential conflict here as well.

Are you using the SMTP service within IIS?  Do a stress test on your web site with the service switched on, then compare the results with the service switched off.  Now imagine what the impact would be if you were running a database server on there as well.

Upgrades

If you separate out your web-site and database functionality it is easier to spot bottle-necks in your setup.   If you are on a tight budget this also allows you to target your upgrade budget where it will have most benefit.

On larger sites I have seen the database server, the web server and the search engine all running on separate boxes with the web server and the search engine being load-balanced and clustered.   In one case there was a single database server and yet the bottle neck was in the load-balanced IIS and the search engine!

I find the easiest analogy for a customer to grasp is to liken the installation unto a hi-fi set up.   Yes, you could buy an all in one, but you may want to upgrade the amp or ditch the graphic equalizer.

For some reason accountants seem to empathize with the hi-fi analogy so it is a good one to use.

To sum up...

  • The modus operandi of a web server directly opposes the modus operandi of a database server.
  • 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.
  • Separation allows easier identification of the need to tune and the ability to tune individual hardware components.
Total article views: 13897 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

Security Outside the Database

Database security needs to improve, and while SQL Server continues to get better, there is more work...

ARTICLE

Why Secure the Database?

You're behind a firewall, you have passwords. Why spend any great amount of time on extra database s...

BLOG

Securing Database Contents with Transparent Data Encryption (TDE)

Introduction Microsoft SQL Server has many security features available within the database, but unt...

FORUM

moving database to another machine

moving database to another machine

FORUM

SQL Server Security Model

SQL Server Security Model

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones