To achieve a layout such as this, you would prevent your web layer from talking to the database itself directly, and force all data requests through a different service layer.
Obviously, this makes your whole architecture much more complicated and you only really gain any security if you are able to detect the attacker before he can sniff all passing user data anyway.
Your assumption is still spot on though - one box down really does mean game over. All of the tactics above and in the rest of this thread only slow down an attacker or make the attack more complicated. None of them will ever prevent it entirely.
* For example, if credit card details are stored on a separate service, then the web tier can be given only a "charge the card" API, instead of a full read access.
* Even if the decryption key is held in memory, encrypting the data on disk helps against accidental leaking of backups, physical theft of the servers. Or a hacker with limited skills who copies a database dump, is discovered from the unusual network load, and does not have time to fully investigate the system and extract the key from memory.
* If sensitive data is held on a separate service, but the web tier has read access to it - then the other service can impose rate limiting and unusual activity detection to block attempts to dump everything quickly.
* If the data is encrypted with the user's password, which is not stored in plaintext at all - then the attacker can at best only access accounts that log in before detection.
All of these still give an attacker full access, assuming they have infinite amount of time and skills. But for many practical scenarios, they can reduce the amount of compromised data.
If they root your web tier, and your web tier knows how to ask your internal service layer for sensitive data, then the attacker knows how to ask your internal service layer for sensitive data.
I really hate repeating "If you lose any one box in your deployment then you can assume you will lose all data, regardless of whether you encrypt things or not" because it makes me feel like Debbie Downer, but that is, in fact, the threat environment.