Your best bet to improve scalability is to focus on removing calls to the database, rather than to remove data from the database. (although that may happen with things like session data)
If your web application is like most I've seen, a single page load triggers multiple HTTP requests. Each of these requests is likely to trigger a session data retrieval, user details retrieval and then also hit the database for the data for the information to respond to the request.
There are some obvious things that can be done here.
- Session data is a obvious thing to move into a NoSQL database. Its a straight Key/Value operation, and something like Riak is good place to store this data.
- Database data such as user details that are read often, but changed rarely are also a good match for storing in Riak. How you handle the race condition when updates occur will vary depending on your ability to tolerate using stale data.
- Likewise, your website probably also has lots of other data that it processes into HTML sections that do not vary from user to user. Implementing a caching mechanism to store the outcome of this processing can also substantially reduce the hits on your database.
In the end though, you will find that some tables will contain some critical information that cannot be cached. When you reach this point, your choices are to either redesign your core data structure (which is probably not possible, you you would already have done it) or to throw money (and hardware) to enable the database to keep running. In one environment i've worked with, this meant a MS SQL server with 128 cores with the fastest disk and max memory money could buy.