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

It really comes down to how much work your webserver and your sql server are doing.

Without more information I doubt you are going to get any helpful answers.

Answer 2

It will depend on the expected load of the server. For small sites, it is no problem at all (if correctly configured). For large sites, you might want to consider distributing the load over different servers: web server, file server, database server, etc.

Answer 3

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 4

I'd say it was best to run them on the same server until it becomes a problem. That way you'll save yourself some money and time upfront. Once the site becomes a success and requires a some architectural changes it should have already paid for itself.

Remember to back up :)

Answer 5

If your web server is publicly accessible, this is a VERY bad idea from a security perspective.

Although it makes a lot of things more difficult from a routing, firewall, ports, authentication, etc. perspective, separation is good. When you have your database server running on the web server, if your web server is compromised, then your sql server is, too.

When you have them on separate boxes, you've raised the bar a little.

There's still a lot more work to be done to secure your web server AND your database server, but why make it easier than it needs to be?

Answer 6

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.