This MSDN article authored by Mark Simms and Michael Thomassy provides thoughtful guidance based on real-world customer scenarios for building scalable applications on Windows Azure and SQL Database, leveraging the Platform-as-a-Service (PaaS) approach (such applications are built as Windows Azure Cloud Services, using web and worker roles).

Another related article I’d recommend is “On Designing and Deploying Internet-Scale Services” by James Hamilton.