Vivek Thakur

Help! It's IT Blogwatch, in which everybody reacts violently to last week's Novell/Microsoft announcement. Not to mention the list that brings tears to your humble blogwatcher's eyes...

How long is a piece of string? Which way is that way? Which is better, chocolate or vanilla?

The answer, as always is that "it depends." If you need to manipulate a huge data set, or do heavy duty data crunching, you'll need a separate DB server to prevent the users from showing up at your door with pitchforks and torches.

If you have a small data set and fast queries, there's no problem leaving them both on the same box.

The network latency argument is a red herring.

Complaining about the latency for shoveling a couple of K worth of data (usually a single packet) over 6 feet of Gigabit Ethernet or fiber compared to what it takes to get the complete web page to the end user over the public internet is like complaining that you were late to arrive in New York because the gas pump in East-Podunk took too long to authorize your speed-pass on your cross-country trip.

Terry,

Nice feedback! I agree that if you have a DB intensive app, than having DB on a separate server will help.

It is due to the limitations of hardware on the web server that we need to put DB on a separate tier (that's why I had added the word "theoretically"!) for apps where heavy data is moved back and forth, but by no means should this be a blind practice (which my friend was following).

Cheers,

Vivek

Indeed, it depends. One advantage to having the two on the same machine that I see right away, especialy me being a Dynamic SQL/ORM advocate, is that you can pass -extremely large- queries without any issues with performance.

ORMs like LLBLGEN Pro can generates crazy huge queries (but extremely efficient!) in order to reduce processing time. The hit however, is the network overhead. Today I went to an interview, and they were talking about how certain queries are happening nearly continualy, to the point that reducing the -lenght of the NAME- of a stored procedure made a small, but noticeable difference (let say 1-2%, but the point is it was benchmark-able). So obviously then having both on the same server would help, especialy if the query is several kilobytes.

On the other hand, if thats such a big deal, you're probably to the point where you need a distributed environment (a web farm with a SQL Server cluster), and then you don't have a CHOICE but to have remote servers... So you're between a rock and a hard place >.<

I personally prefer separating the server for any ERP system, since I -know- it will have to scale, so might as well do it -now-. However, if its simply a small web site, like a blog or forum engine, I leave them on the same physical machine.

(PS: This is Shados from the ASP.NET forums btw)

What about hosting companies that provide shared hosting?

Should they have their SQL Server databases on a separate physical database server to the web server?

Thanks,
Darren