views:

1290

answers:

15

Listening to Scott Hanselman's interview with the Stack Overflow team (part 1 and 2), he was adamant that the SQL server and application server should be on separate machines. Is this just to make sure that if one server is compromised, both systems aren't accessible? Do the security concerns outweigh the complexity of two servers (extra cost, dedicated network connection between the two, more maintenance, etc.), especially for a small application, where neither piece is using too much CPU or memory? Even with two servers, with one server compromised, an attacker could still do serious damage, either by deleting the database, or messing with the application code.

Why would this be such a big deal if performance isn't an issue?

+10  A: 

I would think the big factor would be performance. Both the web server/app code and SQL Server would cache commonly requested data in memory and you're killing your cache performance by running them in the same memory space.

Tom Ritter
+1  A: 

I think its because the two machines usually would need to be optimized in different ways. Other than that I have no idea, we run all our applications with the server-database on the same machine - granted we're not public facing - but we've had no problems.

I can't imagine that too many people care about one machine being compromised over both since the web application will usually have nearly unrestricted access to at the very least the data if not the schema inside the database.

Interested in what others might say.

George Mauer
+1  A: 

It depends on the application and the purpose. When high availability and performance is not critical, it's not bad to not to separate the DB and web server. Especially considering the performance gains - if the appliation makes a large amount of database queries, a considerable amount of network load can be removed by keeping it all on the same system, keeping the response times low.

simon
+4  A: 

Tom correct on this. Some other reasons are that it isn't cost effective and that there are additional security risks.

Webservers have different hardware requirements than database servers. Database servers fare better with a lot of memory and a really fast disk array while web servers only require enough memory to cache files and frequent DB requests (depending on your setup). Regarding cost effectiveness, the two servers won't necessarily be less expensive, however performance/cost ratio should be higher since you don't have to different applications competing for resources. For this reason, you're probably going to have to spend a lot more for one server which caters to both and offers equivalent performance to 2 specialized ones.

The security concern is that if the single machine is compromised, both webserver and database are vulnerable. With two servers, you have some breathing room as the 2nd server will still be secure (for a while at least).

Also, there are some scalability benefits since you may only have to maintain a few database servers that are used by a bunch of different web applications. This way you have less work to do applying upgrades or patches and doing performance tuning. I believe that there are server management tools for making these tasks easier though (in the single machine case).

Dana the Sane
If you're running anything but SPs then your webserver probably has full access to the data in your database anyways
George Mauer
Why isn't it cost efficient? Please specify.
Robert Jeppesen
Robert I expanded on that and added some comments on scalability and maintenance.
Dana the Sane
+7  A: 

On the other hand, referring to a different blogging Scott (Watermasyck, of Telligent) - they found that most users could speed up the websites (using Telligent's Community Server), by putting the database on the same machine as the web site. However, in their customer's case, usually the db & web server are the only applications on that machine, and the website isn't straining the machine that much. Then, the efficiency of not having to send data across the network more that made up for the increased strain.

James Curran
...until concurrent usage increases, and the db server needs more memory to make effective use of buffers and caches. Once the web/app and db servers need more memory than they can share on a single box, paging and disk I/O increases, and performance tanks.
MattK
+1  A: 

I can speak from first hand experience that it is often a good idea to place the web server and database on different machines. If you have an application that is resource intensive, it can easily cause the CPU cycles on the machine to peak, essentially bringing the machine to a halt. However, if your application has limited use of the database, it would probably be no big deal to have them share a server.

Mr. Will
+1  A: 

Security is a major concern. Ideally your database server should be sitting behind a firewall with only the ports required to perform data access opened. Your web application should be connecting to the database server with a SQL account that has just enough rights for the application to function and no more. For example you should remove rights that permit dropping of objects and most certainly you shouldn't be connecting using accounts such as 'sa'.

In the event that you lose the web server to a hijack (i.e. a full blown privilege escalation to administrator rights), the worst case scenario is that your application's database may be compromised but not the whole database server (as would be the case if the database server and web server were the same machine). If you've encrypted your database connection strings and the hacker isn't savvy enough to decrypt them then all you've lost is the web server.

Kev
But you back up the database, right? Otherwise you'd be at just as much risk of losing it due to a hardware failure or rarely excited bug. An attack that kills the webserver will cause downtime all by itself. Enough privileges to add records to tables is enough to render a site useless.
Daniel Earwicker
Of course you backup the database, that's implicit, where in my post did I suggest otherwise.
Kev
Yes, but the conclusion therefore is that an attack intended to bring down the site can do so by destroying the web server config or the database, and the solution is the same for both: restore from backup. Specially protecting the database is (a) unnecessary and (b) impossible anyway.
Daniel Earwicker
@Earwicker - what about all the other databases residing on the DB server? All you've lost is one database.
Kev
A: 

An additional concern is that databases like to take up all the available memory and hold it in reserve for when it wants to use it. You can force it to limit the memory but this can considerably slow data access.

HLGEM
+25  A: 
  1. Security. Your web server lives in a DMZ, accessible to the public internet and taking untrusted input from anonymous users. If your web server gets compromised, and you've followed least privilege rules in connecting to your DB, the maximum exposure is what your app can do through the database API. If you have a business tier in between, you have one more step between your attacker and your data. If, on the other hand, your database is on the same server, the attacker now has root access to your data and server.
  2. Scalability. Keeping your web server stateless allows you to scale your web servers horizontally pretty much effortlessly. It is very difficult to horizontally scale a database server.
  3. Performance. 2 boxes = 2 times the CPU, 2 times the RAM, and 2 times the spindles for disk access.

All that being said, I can certainly see reasonable cases that none of those points really matter.

Mark Brackett
But with 2 machines you have double the probability of hardware failure ;)
TWith2Sugars
@TWith2Sugars - as opposed to what?
Kev
Ref. point 1. If the Web Tier is owned, then what more do you want or need than the App /database API interface? Surely it's game over at that point anyway? Things then get very interesting in terms of services required to support DMZ infrastructure e.g any AD or Microsoft services in play?
Noelie Dunne
@Noelie - Your web tier would not have access to say, backup the database to a file and ftp that file to ftp.hackers.com using xp_cmdshell. Or drop the database. Or modify config values. etc.
Mark Brackett
@Noelie - As to what services are used in the DMZ, I think there's still some debate on that. Some folks will use the same AD domain, some will use a separate domain or no domain at all. Depends on the level of paranoia vs. laziness. ;)
Mark Brackett
@TWith2Sugars - notice I didn't put "cheap" or "reliability" as an advantage. ;) The web tier is stateless, so you can have > 1 box. The DB server failure rate should be at least as good as a single box solution - in most cases, better, because we spend more for redundancy. Like I said, not cheap.
Mark Brackett
+4  A: 

One factor that hasn't been mentioned yet is load balancing. If you start off thinking of the web server and the database as separate machines, you optimize for fewer network round trips and also it gets easier to add a second web server or a second database engine as needs increase.

Paul Tomblin
A: 

Wow, No one brings up the fact that if you actually buy SQL server at 5k bucks, you might want to use it for more than your web application. If your using express, maybe you don't care. I see SQL servers run Databases for 20 to 30 applicaitions, so putting it on the webserver would not be smart.

Secondly, depends on whom the server is for. I do work for financial companies and the govt. So we use a crazy pain in the arse approach of using only sprocs and limiting ports from webserver to SQL. So if the web app gets hacked. The only thing the hacker can do is call sprocs as the user account on the webserver is locked down to only see/call sprocs on the DB. So now the hacker has to figure out how to get into the DB. If its on the web server well its kind of easy to get to.

Jojo
+3  A: 

It doesn't really matter (you can quite happily run your site with web/database on the same machine), it's just the easiest step in scaling..

It's exactly what StackOverflow did - starting with single machine running IIS/SQL Server, then when it started getting heavily loaded, a second server was bought and the SQL server was moved onto that.

If performance is not an issue, do not waste money buying/maintaining two servers.

dbr
I agree, it can be done while the load is low...as the load increases, its easy to seperate them into 2 or more machines.
EJB
I came in and was going to comment on the same thing. Switching you DB server is as easy as changing your connection string (in most cases).
CitizenBane
A: 

Operating system is another consideration. While your database may require larger memory spaces and therefore UNIX, your web server - or more specifically your app server since you mention only two tiers - may be a .Net-based, and therefore require Windows.

Chris Noe
I didn't down vote this, but "While your database may require larger memory spaces and therefore UNIX" - If you're running 64 bit windows and 64 bit SQL there's plenty of memory to play with.
Kev
Sorry, memory was meant as an example reason to need for deploying to split OSs. Other reasons include: performance, security, corporate standards/licensing, vendor support, etc.
Chris Noe
A: 

I listened to that podcast, and it was amusing, but the security argument made no sense to me. If you've compromised server A, and that server can access data on server B, then you instantly have access to the data on server B.

Daniel Earwicker
Not true. You have access to whatever data or privileges that Box A had to Box B. In a secure setup, that would mean you have the highest level of DB access that the app on Box A has. You don't, however, have sa privs on the DB, or root on the OS of Box B.
Mark Brackett
"You have access to whatever data or privileges that Box A had to Box B" - that's what I meant by "you instantly have access to the data on server B". If the RDBMS was on box A and there was no box B, what difference would it make? NB. we're assuming you've already hacked box A, either way.
Daniel Earwicker
In a two box config, if you're applying the principle of least privilege, if box A (web) is compromised, then the worst that should have happened is you've lost the DB on box B and no more.
Kev
And on a one box config, if that one box is compromised, you've lost that one box, which includes the DB on it. What's the difference?
Daniel Earwicker
The difference is that on a two box config, if the web server is compromised, all you've lost is the web server and at worst the DB. The rest of the DB server's integrity is maintained.
Kev
A: 

Database licences are not cheep and are often charged per CPU, therefore by separating out your web-servers you can reduce the cost of your database licences.

E.g if you have 1 server doing both web and database that contains 8 CPUs you will have to pay for an 8 cpu licence. However if you have two servers each with 4 CPUs and runs the database on one server you will only have to pay for a 4 cpu licences

Ian Ringrose
Please explain how this amounts to a savings.
John Saunders