The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Why a database server?

This is a philosophical question of sorts.

What exactly is the rationale for having a database run on a different host from the application?

The disadvantages are obvious: It adds two more single points of failure: the database server and the network connection between said server and the host running the application. It adds complexity to the engine. It adds the overhead of the network and of the network protocol. It's another host to do maintenance chores on and to take up space. It adds the nuisance of configuring the server connection and login in the application.

Just what do we get as compensation for all this?

For our purposes let's treat a cluster as a special case of a single host. Or we could get into whether a cluster counts as a network or whether clustering is even worthwhile.
Rowland
Saturday, December 20, 2008
 
 
Your reasons for NOT splitting into a two-server solution only make sense for single-user applications where the one user, the app and the database all run on the same box and none depend on any outside communication.  As soon as you have your first remote connection either to the application or database server, all of the fragility you describe between these two boxes also applies to every user.

In this day and age of extremely stable network connectivity there really is no problem.  Sure, you are adding incremental complexity but problems with that complexity being fragile were solved long ago.

However, it still makes sense to ask whether a one-box or a two-box solution makes economic sense.  The answer depends on the size and scope of the implementation.

For small intranet apps running over either Terminal Services (hosted thick client) or for browser-based apps, having the database on the same server makes sense - in addition to what you already mentioned this is less expensive and you also get rid of network latency.  I've had 25-user TS solutions running against SQL Server 2000 on a dual-processor Xeon machine at 1GHz and it has performed great.

As the number of connections and/or users increase you will reach a point where a single-server solution is overwhelmed and you must split them out.

The key is to gauge accurately the likely number of users and design the system accordingly.  For a system with a maximum lifetime user count of 25, just design for single-server.  If it is going to go beyond that, design the system so the database side can be easily split off, then moving to a separate server becomes a relatively simple exercise.

You can also implement a two-server solution from the start but use virtualization to put both servers into a single box.  At the point you need the horsepower of two dedicated servers moving to the other box becomes trivial.
Karl Perry Send private email
Saturday, December 20, 2008
 
 
Karl Perry": "As soon as you have your first remote connection either to the application or database server, all of the fragility you describe between these two boxes also applies to every user.

In this day and age of extremely stable network connectivity there really is no problem.  Sure, you are adding incremental complexity but problems with that complexity being fragile were solved long ago."

I need some more convincing on these points.

It seems to me a typical network becomes less reliable the larger it gets. I know IP allows for finding alternate routes but the way the Internet is built these days seems to defeat the purpose by being too hierarchical at the lower levels.

Say did you hear the latest news about the Egyptian Internet? It got knocked out again the same way as the last couple of times. They just repair the cable and go on as if nothing had happened. Their users must feel like Boston commuters being told by the radio to seek alternate routes, knowing full well there aren't any to speak of.

I really don't like taking things for granted. Any assertion along the lines of "In this day and age of extremely stable X there really is no problem" is a red flag for me. It sounds like the sort of complacency that has ruined our economic system this past year.
Rowland
Saturday, December 20, 2008
 
 
Sysadmins perceive greater security and scalability when the app server and DB server are separated, so this has become the default deployment model.

You're free to argue whether or not that's true in actual practice, and I'd bet you could come up with some good arguments.  If you control your environment, then do what's right for your situation.  If not, understand that there's a strong bias for this deployment model, and that most of the DBA's and sysadmins you'll meet are going to be pretty set in their ways.
D. Lambert Send private email
Saturday, December 20, 2008
 
 
Karl Perry: "As the number of connections and/or users increase you will reach a point where a single-server solution is overwhelmed and you must split them out."

Can't you just get a beefier server? Risk going to a cluster? A cluster is sort of network-ish but it can't be the same level of risk as an actual subnet of servers, can it?

And what happens when the two hosts can't cope? All you've done if defer the problem not solve it. The application server gets overwhelmed so you try to go to a server farm. But then the database server gets overwhelmed and it's a bottleneck. It's still a SPoF.

What about distributed apps? How many really large Internet applications are centralized for purely nontechnical reasons? For instance what's to stop Internet indexing and search from operating on a peer-to-peer basis, something like NNRP or the routing protocols?

It seems to me that distributing an app and distributing the database in parallel with it gives us vast scalability WITHOUT any single point of (total) failure beyond an end user's desktop and broadband account. Each peer has most of the data it happens to need right there on its own hard drive. Anything it hasn't got on hand it can try to fetch from a peer. If the network connection to a peer is bad it will fail softly instead of becoming completely useless. At least some clients' needs can be served with the data on hand.

At any rate I see no gain to having a database server separate from an application server even in peer to peer. Let each peer be BOTH.

And don't get me started about cloud computing.
Rowland
Saturday, December 20, 2008
 
 
D. Lambert: "Sysadmins perceive greater security and scalability when the app server and DB server are separated, so this has become the default deployment model."

I'm curious what their reasoning is for this being more secure. I think Mr. Perry has already offered the arguments for it being more scalable.
Rowland
Saturday, December 20, 2008
 
 
Just so I'm clear: about peer to peer Internet search I mean something much more radical than what Google is currently doing. I mean take it completely out of any one corporation's control. I regard corporate policy and practices as a single point of failure.

Data integrity? Digitally sign the entries. An entry originate from the host running the Web server to which it applies. That hosts signs it so nobody can spoof. The public key can be fetched from the server for verification. And if they can spoof THAT then we're in the same trouble regardless. Local caching of the public keys will help a bit there.
Rowland
Saturday, December 20, 2008
 
 
As someone else said the main issue is security. Your web server is connected directly to the Internet so it is more susceptible to attacks and being compromised. Once it is compromised anything on that machine is essentially handed to the attacker on a silver platter. This would include your database as well.

It is part of "defense in depth". You want multiple layers to your defense strategy and this includes putting your database on a different machine so that they have to hack two different machines to get to your data. It is significantly harder to hack a second machine through another one.

As you've pointed out, performance can be an issue. But only for cases where you are comparing against a single server solution. Once you need to scale out and have more than one web server on the front end then it makes more sense to just put the database on its own machine since you can't really put a single database on more than one web server anyway.
not again
Saturday, December 20, 2008
 
 
The best reason to have the database and webserver on two seperate machines is for security.

You can have the web server in the dmz zones which users from all over the world can access.

The database is located on an internal network that can only be accessed by the webserver. So outside people can not physically hit the database server.

Of course users could crack the webserver and via that route gain access to the database server but at least it is a two step process instead of having your database server facing the outside world.

Saturday, December 20, 2008
 
 
DMBSs are "tuned" to be bad neighbors.  To improve performance they like to hog things and try to manage them themselves.
So tired
Saturday, December 20, 2008
 
 
Layers is right, and so is tuning.

There's also machine design.

Webservers benefit from multiple network interfaces, lots of ram and high-speed disk; but they're essentially disposable. Building a new webserver is a trivial thing -- you don't RAID a webserver. You just have a cluster of machines instead.

The DB server will want reliable hardware, RAID disks, lots of RAM. But doesn't need so much network capacity.

You can make the machines more suited to their job instead of needing to make them a compromise.


There's another factor; the more you separate machine functions, the more opportunity you've got to fix stuff transparently. As an example; Take your DB server offline and you can still put up your static pages, and a "I'm sorry we're in maintenance mode" page. Combine the two machines and your maintenance mode gets your users a "Cannot connect to server" webpage and then IE tells them to check they spelled the URL properly.
Katie Lucas
Sunday, December 21, 2008
 
 
In busy systems you don't want one program starving the other system.  In a Web server db combination this can happen.  A beefier server is not always the solution.  You can get contention on a resource where one process significantly slows another one.  Having separate machines allows you to avoid that. (The Egyptian Internet reasoning is a canard; even if db and web server were on the same machine the Egyptian site would be down.  One can't defend their app against ships dragging anchors.)

Sure if the application does not have many users and it is well written - not often the case - then one server hosting the db and the web and the app server can work.
Jim Send private email
Sunday, December 21, 2008
 
 
One minor point, usually application and DBMS complete for CPU resource for sametime, thus spilt it into difference box usually is a cheap way to speed up the application.
Carfield Yim Send private email
Monday, December 22, 2008
 
 
not again: "As someone else said the main issue is security. Your web server is connected directly to the Internet so it is more susceptible to attacks and being compromised. Once it is compromised anything on that machine is essentially handed to the attacker on a silver platter. This would include your database as well.

It is part of "defense in depth". You want multiple layers to your defense strategy and this includes putting your database on a different machine so that they have to hack two different machines to get to your data. It is significantly harder to hack a second machine through another one."

I don't see how defense in depth works in this particular case. Consider: Any Web application in order to function will have to possess the info to connect to the database, including authentication credentials. Once the Web server is hacked all that info is available to the hacker's injected code, plus he's already on a host that has access to the server. He doesn't need to hack a second machine through the first, he's already got access by virtue of having owned a machine that itself has that access. Where's the depth? Once he's cracked the Web server he'll be in to the database server as well in less than a minute!
Rowland
Monday, December 22, 2008
 
 
The passwords for the DB connection are in the scripts. it's entirely possible to have another level of authentication. All the requests to the system go thru stored procs (which can run as a user who has permission to access tables).

However the database login user does not have this permission. So it cannot execute "DROP table".

What you do then is have a login system for permitted users. They turn up and present login name and password and get back a token. The token is then presented by the web transactions to perform operations. The stored proc "eraseMyData" only erases the data for the user whose token has been presented. Even if you can capture a login/password, you can only wipe one persons stored data.

You want to drop tables or update every customer record in some way, you need to login as the data owner. And while you're at it, you need to be on the console and definitely not from any of the web machines.

Hard outer, hard centre.


Faffing about with the tokens is a pain; a more limited implementation just won't let you run "delete" or "update" on a whole table -- you need to call stored proc verbs, which will only let you execute one deletion at a time.

That may not stop people, but it'll limit the damage.

Hell, remove delete and permissions from that user entirely. Just create new records records for changed ones, tag old and erased ones as "deleted", miss them out of queries and periodically run an actual deleter to take them out of the DB. This has the advantage that you can undo changes if users ask.

Now it's getting really hard to damage the DB...
Katie Lucas
Monday, December 22, 2008
 
 
This is a pointless, flamebaiting thread. Anyone who's built a heavy duty app knows why you split into tiers.
Andrew Badera Send private email
Monday, December 22, 2008
 
 
"Consider: Any Web application in order to function will have to possess the info to connect to the database, including authentication credentials. "

True, but as someone else pointed out those credentials may only be for limited access.

Also, if the hacker completely owns the server then yes he can likely get to the database easier. But the world is not black and white. He might not have execute privileges on the machine. It all depends on what privileges he manages to obtain. If he only has copy privileges then having a database on the web server allows him to just copy the associated files locally to access them. If the database in on another server then he can't get to it with copy privileges.

So putting it on another machine increases the privileges he must have in order to get to the data. Again, defense in depth. The world is not binary...
not again
Monday, December 22, 2008
 
 
To give an example, a hacker may be able to exploit a path canonicalization flaw in the application or server to give him read-only access to files outside of the web context path. This is a relatively common vulnerability but does not necessarily allow the attacker to take complete control of the machine. With a local database the attacker could read the database file and pull it down to his own machine to start hacking at it. But if the database is on another machine that is locked down correctly, the attacker can not simply copy the file.
not again
Monday, December 22, 2008
 
 
Katie Lucas: "What you do then is have a login system for permitted users. They turn up and present login name and password and get back a token. The token is then presented by the web transactions to perform operations. The stored proc "eraseMyData" only erases the data for the user whose token has been presented. Even if you can capture a login/password, you can only wipe one persons stored data."

Okay, but...

If the credentials and corresponding data are per end user then why have a Web app in the first place? Why not run each end user's piece entirely on his own desktop?

I had a feeling we'd get into cloud computing at some point. So let me just state my position up front: Any business model that involves giving some online business total control over your private data is a sucker play.
Rowland
Monday, December 22, 2008
 
 
not again, on read only break in: "With a local database the attacker could read the database file and pull it down to his own machine to start hacking at it. But if the database is on another machine that is locked down correctly, the attacker can not simply copy the file."

You've got me half convinced. That's better than par for the course.

I think you've made a good case against having the data files in the same account as the application. I'd have liked to do that as it's so efficient and simple. I guess I'll get over it.

Now, what about the database on the same host but under a different account, connected to the client via IPC to a daemon? This sacrifices some of the simplicity I had hoped for but it seems to keep all the security. Just substitute properly locked down account for properly locked down host.
Rowland
Monday, December 22, 2008
 
 
There are a million ways to attack a web server from cross site scripting attacks which don't actually alter anything on the web server to a complete compromise. As you go from one extreme to the other you will find pro's and con's to keeping the database on the same server was the web application.

So it really isn't worthwhile to sit here and play the "what if" game for too long because you can't really know all the ways the system can be compromised. For example, you've now proposed locking down the database on a per account basis. However, if the attacker can perform a privilege escalation then they can potentially gain access using the account that the database is running under.

So basically it comes down to what you are more comfortable with. If you feel that you have to have a virtually impenetrable web server just to feel comfortable with running the database on the same machine then it probably isn't worth it.

You need to ask yourself why you are so against having the database on another machine. If the main issue is cost, then just be sure to keep in mind that the real cost of another server is likely significantly lower than you imagine after you factor in the amount of time you may spend trying to secure and maintain a single-server solution. Also, unless you have a real reason to believe that performance is an issue, you are probably performing premature optimization that may or may not actually be beneficial.

Good luck.
not again
Monday, December 22, 2008
 
 
In our case hacking into the web server doesn't get you the db.  Sure the user name and passwords are on the web server but they are encrypted.  Just getting them out of the properties files is useless unless you know how to unencrypt them .(not a simple Ceasar cypher)
Jim Send private email
Monday, December 22, 2008
 
 
Why would you even need to unencrypt them, if they work on the server encrypted?
Grinch
Monday, December 22, 2008
 
 
The program knows how to unencrypt them to send them to the database.  The application can't send the encrypted password to the db. 

On an earlier comment.  The application server may be able to "talk" to the db server but that does NOT mean it can "see" the db files.  It has no rights to pulls down a copy of the db files.  Usually you set upt he applicaiton server so it can talk to a specific port on the db server but it cannot log into the db server.  The hacker would have to hack yet another system to gain access to the db box. (most modern RDBMS's (open source and not-pen source) don't allow the client to access the db files directly.  The client program talks to a listener on a port and the db communicates via a process with the port.  Unless you are running mid 1980's Clipper as a db, highly unlikely, the web server has no rights to the db server.)
Jim Send private email
Monday, December 22, 2008
 
 
Well, then, put the database and application on one machine. It's not like small systems don't ever do that.

Then scale up the number of users til you need a second server.

Or, perhaps, add some reduncancy for fault toelrance, so a single database or application failure doesn't bring down the entire system for every user.

Whoopsie.

If you can't get your setup to be reliable enough with one application and one database on separate machines, you're going to be in trouble soon enough anyway.

And an additional benefit is that the amount of work done by the database server and the application will rarely be evenly split and most decent databases do clustering - so you don't need a 1 database server per application server. As each side approaches capacity, you can add more resources without worrying about the other side.


But if you don't need redundancy, fault tolerance, or scalability, then do whatever's simplest - you can add features later when you do need them.

Tuesday, December 23, 2008
 
 
The OP stated: "This is a philosophical question of sorts."

He just wants to know why it would be considered a good practice. Obviously, there are lots of sites in the world where the database runs on the web server for practical reasons. For example, if someone is hosting your site then you will most likely be paying for additional servers so it makes sense to forego the additional security to save money.

So the separate database server approach is really only pertinent to companies hosting their own applications or who have something important to protect. You always need to weigh the value of what you are trying to protect against the cost of protecting it. In other words, there is no reason to pay an additional $1000 a year to protect $1 worth of data.
yeah baby
Wednesday, December 24, 2008
 
 
Dr Known Send private email
Friday, December 26, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz