Running SQL Server on the Web Server

Question

Is it good, bad, or indifferent to run SQL Server on your webserver?

I'm using Server 2008 and SQL Server 2005, but I don't think that matters to this question.

Answer 1

For small sites, it doesn't make a bit of a difference. As the load grows, though, this scales really badly, and quicker than you think:

• Database servers are built on the premise they "own" the server. They trade memory for speed and they easily use all available RAM for internal caching.
• Once resources start to be scarce, profiling becomes very difficult -- it is clear that IIS and SQL are both suffering, less clear where the bottleneck is. IIS needs CPU, SQL Server needs RAM or CPU etc etc
• No matter how many layers you put in your code, it all runs on the same CPU, therefore a single layered application will run better in this context -- less overhead -- but it will not scale.
• Security is really bad, usually you isolate SQL behind a firewall!

If you can afford it, it's probably better to shell out a few bucks and get a second server, maybe using PostgreSQL. One IIS server and one PostgreSQL cost about as much as on IIS + SQL Server because of licensing costs...

Answer 2

Larger shops would probably not consider this a best practice... However, if you aren't dealing with hundreds of requests per second, you're fine putting them both on one box.

In fact, for small apps, you will see better performance on the back-end because data does not have to go across the wire. It's all about scale.

Keep in mind that database servers eat memory. Here's one important lesson from the school of hard knocks: if you decide to run SQL Server 2005 on the same machine as your web server (and that is the setup you mentioned in your question), make sure you go into Sql Server Management Studio and do this:

1. Right click on the server instance and click properties
2. Select 'memory' from the list on the left
3. Change 'maximum server memory' to something your server can sustain.

If you don't do that, SQL Server will eventually take up all of your server's RAM and hang onto it indefinitely. This will cause your server to more or less sputter and die. If you are not aware of this, it can be very frustrating to troubleshoot.

I've done this quite a few times. It's not something you would do if you had the infrastructure of a large corporation and it does not scale, but it's fine for a lot of things.