I'm really looking forward to seeing the "NoSQL" buzzword head over the top of the hype-cycle and start heading downwards, since I think it's really doing damage in what should be a fairly straightforward discussion of matching customer use cases to appropriate technology.
Because the term is framed ("No!") in opposition to the almost the entire family of existing data persistence technology, anyone who comes to the discussion fresh assumes there's a replacement process going on, wherein NoSQL stands in opposition to SQL.
That's a shame, because the term is only one letter away from a (slightly) less polarizing buzzword: NotSQL. Even then, though, the core discussion would be lost, because the big difference isn't programmatic API versus 4GL. The big difference is use case matching, in particular the high-volume, high-availability use case which has emerged in the age of consumer web services.
People with public-facing web applications face a potentially unconstrained read/write load (in their happiest dreams) and the techniques necessary to scale a traditional RDBMS to match that load proceed from the straightforward at the low end to the increasingly byzantine at the high end.
The scaling story for traditional RDBMS technology just is not great: start by adding servers and extra technology to hook them together; get increasingly smart people to handle your increasingly complex infrastructure; finally, start hacking at your data model to allow even further partitioning and duplication.
The new breed of databases have a great scaling story: once you get set up, scaling requires plugging in new nodes and turning them on. That's it. No model changes, no extra replication and high availability technology.
There's no free lunch though. In exchange for the high-throughput/high-availability you lose the expressiveness and power of SQL. Henceforth you will write your joins and summaries yourself, at the application level. Henceforth performing an ad hoc query may require you to build and populate a whole new "table" (the terminology is highly variable at this point) in your model. And of course this technology is all pretty fresh meat, so just learning enough to get started can be a bit of a slog – kids aren't exactly coming out of school with a course in this stuff under their belt.
So, it's a whole new world, and if you are planning on serving an application where the numbers (hits, pages, requests, whatever) are heading into the 7-digits, it might be good idea to start with this technology (can you tell I can't stand to use the "NoSQL" term? It is just too awful, there really needs to be a non-pejorative term for this application category).
Of course, there's nothing new under the sun. Getting the best performance for a specialized use case requires (a) modifying your data model and (b) using technology that can leverage your specialized data model. This is exactly what OLAP databases have been doing for a generation to provide data analysis on multi-billion record historical databases (special data model, special technology).
Database guru Michael Stonebraker wrote a nice article about the brave new world of databases, called "One Size Fits All: An Idea Whose Time has Come and Gone" in 2005, and the conclusion is that we are going to see increasing fragmentation of database technology based on use case. "NoSQL" (*shudder*) is just the latest iteration in this process.
Meanwhile, I'll put my oar in for the general purpose database: it's easy to run OLAP queries on a general purpose database, you just can't do it once your table size gets over a billion; it's easy to run a public web site on a general purpose database, you just can't do it once your load gets over a million. On the other hand it's well nigh impossible to run even a small web site on an OLAP database and pretty darn hard to build even a small OLAP system on a NoSQL foundation.
Horses for courses folks, horses for courses.
For more of this kind of geekery, see the several articles linked off of "The Case for the Bit Bucket" at the Oracle Nerd blog.