Obviously there are many ways to build web apps and many patterns to follow but the thing with scaling is that you have to design your app to scale before you try and scale it otherwise the chances are you will have to change a lot of code or even re-design your application from scratch.
Hopefully the following simple steps will spell out the basics of using Windows Azure Cloud Services (Platform as a Service) and .Net to design your app architecture to scale.
Step 1 - Databases are HARD to scale. Sure you can add more and more data to a database but the more throughput you need, the more memory will be used, the more network bandwidth will be required and generally the slower everything will run. Databases are relatively expensive and scaling them across servers with replication etc. is difficult, expensive, error-prone and is basically best avoided. So what? Keep your database as a database. Do not perform computationally expensive operations in the database. Sure you can crunch data in a stored procedure but you're just eating up valuable CPU cycles, get it out of the database and do the work in a web service or web application instead.
Step 2 - Share your session. Windows Azure does a lot of the heavy lifting when it comes to scaling. You don't need to worry at all about load balancing, you simply increase the numbers of instances of your web application and Azure load balances them for you. This does mean that the same user accessing the same application might not always hit the same physical web server and that means you cannot keep session in memory - it will not be present on the other machines. You could use a database but that tends to be slower (and see step 1). Azure provides a mechanism to create cache roles (you can have as many of these as you need) and I have taken their suggestion to use at least 3. This allows 1 to die while another might be rebooting and the data is still safe. These are configured in the Azure cloud project settings. You access session in your .Net code in exactly the same way but you configure your session state to use a class called DistributedCacheSessionStateStoreProvider (from Microsoft), which is designed to invisibly work with your cache roles to store your session data. This session has a timeout like any other session and it does pretty much just work.
Step 3 - Because your web application is shared across multiple web servers, you should consider whether this is a problem for your workflow. All of your data should be in the (shared) database or the session so you should not get any other problems as long as you avoid doing crazy stuff in code to store local copies of things. In most instances, you do not have to write your application code any differently just because it is load balanced. You should set a common machine key in your web.config though to ensure that the same value is used for encrypting viewstates and the like.
Step 4 - Do anything resource intensive in the web application. These are the parts that are multiplied when scaling up so it makes sense to do the hard stuff here, not in the database or anything other shared service.
Step 5 - Use shared storage. Whereas you might normally store files or objects in the local file system, this is obviously not usually possible in a scaled environment since only one instance of the web application can access its local file system. Most (all?) cloud providers, including Azure, provide storage services that are accessed via URL. You can upload/retrieve/delete these objects in the usual way and they can be accessed either from code using one of the Azure helper dlls or directly via URL (for instance, your web pages can directly embed the links to images so that the request can easily go via a CDN or directly to the storage service without needing to access your web application on the way). These services are usually pretty cheap and the CDN functionality that many provide can also improve the performance of your web application by serving content from locations close to the end-user.
Step 6 - Start simple and add functionality. Some bugs are hard to track down and fix so be sensible! Start with a small basic app and add the features, one at a time, and make sure they work before adding another one. This way, you can sort out issues as they arise rather than peeling through several layers of unknowns to find out what's wrong.
Be warned that there are different versions of Azure floating around, especially in online examples. Something that maybe used to work 3 years ago might not work any more because it is deprecated in a new library. Other class names were re-used but did different things just to add to the confusion. Stack Overflow should help you find the answers you need!