Why is it not advisable to have the database and web server on the same machine? [closed]

Question

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?

Potheen

I believe there is one concern that is completely omitted in this entire thread and question that would pretty much reverse this recommendation. What if you get access to the database credentials but not the server. With the db and app hosted on the same machine, you can forbid any external access to the database and as such, only having credentials of the database does not grant you any access, you will need server access for that. And that's one less security concern if you ask me. – Quita

@Quita Couldn't we keep your proposed security benefit, while also keeping the benefits of having separate machines for the DB/webserver outlined elsewhere in this post, by just telling the database to only accept connections from the webserver? That way, we would have two machines and all the benefits that come with that separation, and leaked DB credentials could only be used for evil if the attacker also got control over the webserver. – A1

Answer 1

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.

Sidekick

But with 2 machines you have double the probability of hardware failure ;) – Sideway

@Sideway - as opposed to what? – Renowned

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? – Yokel

@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. – Sidekick

@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. ;) – Sidekick

@Sideway - 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. – Sidekick

@MarkBrackett "the maximum exposure is what your app can do through the database API." That is usually almost everything. by virtue of having the ability to connect to the database and do a select * he can essentially copy the database to his own computers. furthermore, it is rare that an application does not need delete privileges so while he may not be able to drop the table, he can just delete everything from it. your database is pretty pwned in this case as well. now, if you have multiple databases on the same server, then the others are safe but thats about it. – Odometer

@Odometer - that depends on your particular security setup. See my previous comment on paranoia vs laziness as well as the caveat of "least privilege rules". But ultimately - unless you've made the relatively idiotic mistake of running your DB server as root and left open shell access - system level access is a lot more valuable than DB admin. – Sidekick

@MarkBrackett but thats what I'm saying: "least privileges" is still enough to pwn your database. Why is system access more valuable than db access? The DB is what everyone goes after: it contains private user info like passwords, CCs, etc. The only reason you want system access instead of db access is to change the site or perhaps download private source code depending on the company (but thats a different issue then). None of what Im describing requires a bad setup, only a leaked db user/password (and address) which are obtainable if the web server is compromised. – Odometer

@Odometer - I think you're confounding "average" practice with "best" practices re: least privilege. It would not be "best" practice least privilege to allow your web app to read sensitive data like passwords/SSN/CC/etc. in the clear. System access allows you to do nefarious things such as removing log files, installing root kits/phone home apps/etc., corrupt backups, jumping off to other servers, etc. Even DB admin (which your web app shouldn't be running as anyway) confines you to the DB - while that may be your crown jewels, it's not everybody's. – Sidekick

It's also worth bearing in mind that Microsoft SQL Server is priced “per core” — so you pay for each virtual CPU. A shared server would want to have more cores, to reduce resource contention, but that dramatically increases the cost of SQL licensing. It is precisely for this reason that the hosting architecture I've just been designing for a client has 8-core webservers and 4-core dataservers — general1-8 and io1-15 servers in the Rackspace Cloud, in this case, if you want to compare the specifics of the spec. – Sphenic

Why is it more likely for the web server to be compromised than the db server? – Banausic

@Banausic - Because, by design, you have to allow untrusted (aka public) machines to contact your web server. You don't need to allow that for your DB server. – Sidekick

Sorry for the stupid question but : if the db and the web app are on 2 different machines, does the queries from the web app to the db machine take more time ? Or are those machines really close in distance together that it's negligeable ? – Usia

@Usia - yes - it is definitely possible to be slower. Though it's possible to be faster, I don't think I'd ever advise splitting an app up from it's DB on performance concerns - I'd expect the "average" app to come out marginally slower (but not extremely so...maybe 20% or so off the top of my head). A pathological case (like a lot of SELECT N + 1 queries) could be 10x slower. Specifics would depend on RAM size/caching, disk speeds/latency, network speeds/latency and app chattiness/chunkiness to DB. – Sidekick

I know this is an ancient thread, but "2 machines = double the chance of hardware failure" is an incorrect statement; common confusion of statistics. If failure of 1 machine = 0.01%, then failure of 2 machines != 0.02. The maths is: 1 machine = 0.01%. 2 machines (total) = 0.02% / num of machines = 0.01%. Increasing machines, unless there is an external condition maintains the same level of failure regardless of quantity. Otherwise we would see huge server farms crashing in much higher proportions than my laptop...just clearly that up as it was irritating me.. – Blanchard

@Blanchard - since the two machines are in parallel and are each a single point of failure, the overall reliability is less; it's not technically double (it's actually 1 - (r1 * r2)) but close enough for large reliability and small nodes....In the 0.01% case, it'd be 0.0199%. But for 100 nodes, it'd be 36.6% instead of the 100% implied by the doubling statement. – Sidekick

@MarkBrackett Regarding this: "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." - Direct access to the DB using the DB's user ACL would solve that, while the web-tier level access will have more rights than the default DB user. – Pentatomic

Double boxes, double cost – Celaeno

Answer 2

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.

Installment

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. – Birkett

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). – Lineation

U-u-h, I like that. Someone is thinking of the context before suggesting a solution! – Unwarrantable

This is the right answer. Start with a single server, and move your DB only when your server can't handle the load anymore. – Harrell

Answer 3

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.

Nagy

...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. – Screwy

@MattK - but what if you have massive amounts of memory? We have an app where each client has their own database, so both the database and web servers can scale horizontally very easily. Given that we have more memory than disk in use (64GB vs. ~40GB), wouldn't it be better for performance to keep it all on the same machine? – Macrography

If your working set fits in memory, then you may not see any of the performance issues I mention. More often servers have more disk than RAM, but it sounds like in your case you have databases that will fit entirely in RAM - as long as the applications on the shared server do not consume too much of it. – Screwy

Answer 4

Tom is 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 tow 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).

Sort

If you're running anything but SPs then your webserver probably has full access to the data in your database anyways – Sherfield

Why isn't it cost efficient? Please specify. – Overshine

Robert I expanded on that and added some comments on scalability and maintenance. – Sort

Answer 5

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.

Sexagenary

What if you have a small(ish) database, but with tons of memory? Wouldn't the cost of going over the network for each database call, particularly if there are many, outweigh the benefits? – Macrography

@Tom Ritter Although performance is a concern (Per this answer, and point #3 in Mark Brackett's answer) I know that some people (me included) would likely put the money saved by NOT getting a separate DB server into MORE CPU/RAM/etc. on the one-and-only server that replaced it. So this should be taken into account. As for separated memory, IIS and SQL could be configured to account for their competition over resources. Personally, the "kicker" for me was Mark's #1 point... security. I like the thought of the limited access a compromised webserver would have to a separate DB server. – Krouse

@Tom, You can always split the memory space into two separate units. One half for server and one half for database. – Chlor

Answer 6

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.

Renowned

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. – Untangle

Of course you backup the database, that's implicit, where in my post did I suggest otherwise. – Renowned

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. – Untangle

@Earwicker - what about all the other databases residing on the DB server? All you've lost is one database. – Renowned

Besides Daniel, you are missing a HUGE point. It's not about a database backup, it's about the compromised data. Would your customers be okay that "A hacker stole all your customer and sales data, but don't worry, I've got a backup." :) – Krouse

Answer 7

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.

Raasch

Answer 8

I agree with Daniel Earwicker - the security question is pretty much flawed.

If you have a single box setup with a webserver and only the database for that webserver on it, if that webserver is compromised you lose both the webserver and only the database for that specific application.

This is exactly the same as what happens if you lose the webserver on a 2-server setup. You lose the web server, and just the database for that specific application.

The argument that 'the rest of the DB server's integrity is maintained' where you have a 2-server setup is irrelevant, because in the first scenario, every other database server relating to every other application (if there are any) remain unaffected as well - being, as they are, hosted elsewhere.

Similarly, to the question posed by Kev 'what about all the other databases residing on the DB server? All you've lost is one database.'

• if you were hosting an application and database on one server, you would only host databases on that server which related to that application. Therefore, you would not lose any additional databases in a single server setup when compared to a multiple server setup.

By contrast, in a 2 server setup, where the attacker had access to the Web Server, and by proxy, limited rights (in the best case scenario) to the database server, they could put the databases of every other application at risk by carrying out slow, memory intensive queries or maximising the available storage space on the database server. By separating the applications out into their own concerns, very much like virtualisation, you also isolate them for security purposes in a positive way.

Pocketbook

Answer 9

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.

Moonscape

Answer 10

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.

Bailly

Answer 11

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.

Madox

Answer 12

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.

Untangle

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. – Sidekick

"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. – Untangle

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. – Renowned

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? – Untangle

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. – Renowned

Answer 13

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.

Sherfield

George, I think you should refer to Mark Brackett's answer. The security your webserver has to the database would NOT be the same as it would be if the server was separate. Specifically the "local disk" wouldn't be accessible. In addition, if only a single website (of many) was hacked, they'd likely have only compromised access to THAT SITE's database (unless you use too powerful a user for that connection string). There are countless other scenarios, I just don't think a comment here is the place for them. – Krouse

Answer 14

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

Montpellier

Please explain how this amounts to a savings. – Jackie

John - he's saying that to get equivalent performance to 2 machines, you'd need to double the # of cores, which would double the SQL Server licensing costs. Why pay an extra $10-20k for SQL Server if all you get is the same performance as utilizing 2 machines. – Macrography

only true if performance/resource utilization (e.g. cpu) is dominated by the app servers and not by the db server. if the db needs 8 cpus it won't work. – Infinity

Answer 15

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.

Yuk

Answer 16

Something not mentioned here, and the reason I am facing, is 0 downtime deployments. Currently I have DB/webserver on same machine and that makes updates a pain. If you they are on a seprate machine, you can perform A/B releases. I.e.:

• The DNS currently points to WebServerA
• Apply sofware updates to WebServerB
• Change DNS to point to WebServerB
• Work on WebServerA at leisure for the next round of updates.

This works before the state is stored in the DB, on a separate server.

Meg

Answer 17

Arguing that there is a real performance gain to be had by running a database server on a web server is a flawed argument.

Since Database servers take query strings and return result sets, the data actually flowing from data server to web server is relatively small, but the horsepower required to process the query and generate the result set is relatively large. Optimizing performance around the data transfer time therefore is optimizing around the wrong thing.

Regarding security, there are advantages to having the data server on a different box than the web server. Having such a setup is not the be all and end all of security, but it is a step in the right direction.

Regarding scalability, it is easy and relatively cheap to add web servers and put them into cluster to handle increased traffic. It is not so easy and cheap to add data servers and cluster them. Also, web servers and data servers have different hardware needs, so multiple boxes help out with scalability.

If you are starting small and have only one box, then a good way would go would be to use virtual machines. Running the web server and data server in different VMs on one host gives you all the gains of separate boxes at the cost of one large box price.

Microgroove

The original question asked about application servers, not necessarily public internet facing web servers. – Leatrice

Small data flowing between machines means that small network throughput is enough. But do not forget about latency - the time needed to just connect. Lowest latency would be to get something from RAM, bigger latency from HDD, potentially much more latency over network (it depends on the network of course). – Kitchens

Answer 18

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.

Calloway

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. – Renowned

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. – Calloway