Does using a NoSQL database give a boost to scalability even if you aren't sharding data? Well lets define scalability. If you are referring to scalability as database/backend systems are concerned, in that you have vertical and horizontal scaling where horizontal scaling IS sharding data then this becomes a trivial question because then the answer would be absolutely no, because the only option you have left is vertical scaling (ie getting better hardware). If however you are talking about scalability in a broader sense referring to flexibility of the application, data value, etc... Then that is a completely different question with a number of answers. And like you mentioned it will often come down to what are you doing with the data and how it should be stored. Let me preface everything here with the statement that in most cases you should still be using a RDBMS and NoSQL should fill niche's. The following is a description of a specific instance where a NoSQL database would be more beneficial given specific requirements, and where we can ignore horizontal scaling.
Take for instance the idea that you are creating a cloud file storage system similar to google drive, dropbox, or box but instead of using an actual file system you decide that it would be more beneficial to you to virtualize the file system. Now you have a problem because your data model is suddenly the tree structure that is going to be horribly inefficient in a RDBMS (despite the fact that that is how everything is indexed). Because now you have a 3 column table with Name, User, and Parent. User is a foreign key to a users table and Parent is a self referencing nullable foreign key (nullable because the root directory could not have a parent). So what is the primary key? In this instance it is a compounded key across all columns... Which suddenly makes Parent our worst enemy.
Now instead think about how you would put that in some form of document store? Instead of fighting the data you are able to work with it and store it as the tree structure which will in turn decrease your development time as well as decrease maintenance costs. If you are decreasing costs doesn't that allow for a different kind of scalability? Plus in this instance you are creating the system correctly from the ground up which should give more flexibility to the application itself. Currently I am running this on a single server using MongoDB, which as you explained gives me an Available, Consistent model that is not much different than looking at the difference of MySQL or Postgres.
With MongoDB at least you can define how many servers you need to communicate with for a query to succeed so, yes you can convert it to a Consistent, Available model if you tell all queries to communicate with all server instances.
So I think that you have the right of it in that there is a big benefit in how the data is stored. There are things that don't fit well in a relational model that fit well in other models (as another brief example, Amazon uses some form of Graph Database for their recommendation engine for products).
Did I correctly understand your question?
Edit:
Will more data slow things down? Yes. How much will it slow things down? I honestly don't have enough experience to give an adequate answer.
Key/Value: Essentially a lookup table with large amounts of data associated with the lookup key. This is going to be really really fast because you can only look things up by the key.
Column/Family: Essentially a much more structured Key/Value store. You can only query based on the Column and so this should be really fast too.
Document: Aggregation style schema. Here you will want to aggregate similar data together. Denormalization is ok and expected for this kind of database. Depending on whether you are doing a lot of writes or reads you can organize your data so that it gets distributed across multiple shards to distribute the writes or the reads out (note that you can create a hybrid approach that is good for both but generally you need to choose optimization for one or the other)
Graph: The strength of this one is that it can create and tear down relationships really quickly. If you have some data where you have relationships that need to change between data (think some form of recommendation engine) then you should use this.
How you store data in any of these databases will influence performance (similar to the fact that if you store data incorrectly in some RDBMS it will influence performance). So to hopefully make this more clear: You need to know which database system you should use as well as how to store data in that database system.
I can't tell you why it's a bad idea. I can tell you a bunch of reasons why a relational database is a good idea though.
Remember that not everyone consults a dictionary for a definition. More times than not, a dictionary is used to find the correct spelling. This means you're not just finding a needle in a haystack, you are searching the haystack for needles that are similar to the one described by the user (if I may use an idiom).
You won't just be doing primary key look-ups. You'll be doing keyword searches
Words can be related, either in meaning or spelling (read, read, red and reed)
Whenever you see the word "related" think "Relational Database"
If you need speed, you need caching on top of your relational database, not a broken relational data model
A properly normalized database speeds up primary key look-ups and searches since there is simply fewer bits to sift through.
The people who say normalized databases are slower are referring to the 0.1% of cases where this is true. In the other 99.9% of cases they haven't actually worked with a truly normalized database to see the performance first hand, so ignore them. I have worked with a normalized database. Love it. Don't want to go back. And I'm not a database guy. I'm a C#/JavaScript/HTML/Ruby guy.
Words have an origin. In fact, many words in the same language can have the same origin, which is another word in a different language. For instance, résumé (the thing we upload to recruiters websites so we can get incessant phone calls and e-mails for the next 7 years) is a French word.
A dictionary also defines what kind of word it is (noun, verb, adjective ect). This isn't just a piece of text: "noun" it has meaning as well. Plus with a relational database you can say things like "give me all the nouns for the English language" and since a normalized database will be utilizing foreign keys, and foreign keys have (or should have) indexes, the lookup will be a snap.
Think of how words are pronounced. In English especially, lots of words have the same pronunciation (see my example above with read and reed, or read and red).
The pronunciation of a word is, itself, another word. A relational database would allow you to use foreign keys to any pronunciations. That information won't be duplicated in a relational database. It gets duplicated like crazy in a no-SQL database.
And now let's talk about plural and singular versions of words. :) Think "boat" and "boats". Or the very fact that a word is "singular" or "plural".
Oh! And now let's talk about past tense, present tense, future tense and present participle (to be honest, I don't know what the crap "present participle" is. I think it has something to do with words ending in "ing" in English or something).
Look up "run" and you should see the other tenses: ran, runs, running
In fact, "tense" is another relationship itself.
English doesn't do this so much, but gender is another thing that defines a word. Languages like Spanish have suffixes the define whether the subject of the noun is male or female. If you need to fill in the blanks for a sentence, gender is extremely important in many languages.
Since you can't always rely on language conventions to determine gender (in Spanish, words ending in "o" are masculine/male, but that's not true for all words), you need an identifying value: Male or Female. This is another relationship that a normalized database handles gracefully even at millions of records.
With all the twisted rules and relationships between words, and even different languages, it's hard for me to imagine this data store as a "document store" like a no-SQL solution provides. There are so many and such a large variety of relationships between words and their components that a relational database is the only sensible solution.
Best Answer
Replication would typically be used for site to site data transfer.
Fault tolerance is all about redundancy. The more redundant your system is more tolerant it is to faults. A lot of can be solved through infrastructure, rather than code, especially for a database.
Hopefully the server has redundant hard drives that can be hot swapped on the fly if there is a failure. So the server itself must have redundant feature(s) such as hardrives power supplies, etc.
But what if server crashed, then we are into failover clustering, this is when another server takes over seamlessly should one server fail. In this case, multiple nodes would be available. Usually one is active and the other passive waiting for failure.
Then is a more serious failure, data center crash, for example an earth quake or some other act of god. If this case another data center would take over. In this scenario the most likely the data is replicating from one site to another.
All of these cost more money to implement and maintain. It depends on how serious the data is and what the client demands are. In some cases, just a nightly back up will do.
Hopefully your online banking system is using more than that.
I don't know what your requirements are, but those are the most common scenarios and levels of escalation.