Google the "Hype Cycle".
NOSQL is such a broad term that it is next to meaningless.
Not all NOSQL solutions deliver "No single point of failure".
BASE has made people realise how much they miss ACID. Eventually consistent doesn't really work.
These systems emerged to satisfy a niche for companies operating at the bleeding edge. The inventors of the solutions were pretty clear on the use cases for what they had invented. They understood that NOSQL meant Not Only SQL and for the most part it seems that the systems were produced by craftsmen who needed a craftsmans tools.
What appears to have happened is that journeymen think they need craftsmans tools and are building wobbly systems with them.
Of all the NOSQL solutions I see graph databases as making the most sustained inroads into the space of the traditional RDBMS. I had a lot of fun with Neo4J. I genuinely do mean fun, no irony or sarcasm involved. Microsoft have a number of articles on their graph database code named Trinity
REDIS is a great key-value pair in-memory solution for web session management and anything transient.
I can see from a developer perspective why document stores are currently popular. From an operational perspective I'm sceptical. For MongoDB I'd be looking at TokuMX
as a replacement engine. As Mongo doesn't have a shared nothing architecture I'd probably look at Couchbase instead.
RIAK is interesting, especially as it follows the Amazon Dynamo white paper. It's really worth reading the Amazon paper. It'll take you a few attempts to digest it but is worth it in the end. In a lot of cases front-end web development simply wants to retrieve an object by a key value and if that is all you want to do then virtually all the NOSQL solutions fulfill that brilliantly.
Cassandra is one I keep looking at but am not sure I've fully grasped its potential.
Hadoop has a large ecosystem and there is a huge amount of investment in it. I liked the idea of HIVE which effectively lets you use the MySQL dialect of SQL to query structured data in Hadoop.
I believe that Stinger has the same design goals as Impala in terms of adding real-time SQL querying of the Hadoop core.
The big challenge faced by data professionals is to identify the stuff that is merely a fad and the stuff that really will continue to engage after the fruit-fly attention span of early adopters has got bored with it.