right off the bat, you've lost the querying power that SQL offers.
Can you point me to any reasonably-priced database systems that allow me to execute SQL queries against a cluster of shared-nothing machines running commodity hardware? I'm not interested in paying $20-100K/TB/year for Vertica and friends.
SQL can be great if you have vast amounts of money or if your data can fit onto a single machine. When neither of those things are true though, nosql DBs become important.
Well are we talking with shard capabilities built-in, or having to roll your own? If you can roll your own then I suggest looking at Galera (we use this for our production MySQL stuff) http://www.codership.com
If it has to have built-in sharding then I can't think of anything off-hand.
MySQL cluster is another option beyond MySQL Galera (and Cluster actually has a NoSQL layer on top of it.. FYI)... but I have to say if we want to talk about scaling issues for MongoDB, you might not be happy with some of the limitations of NDB.