I'm basically looking for auto-scaling front ends (MS MVC/ASP.NET) and DB (SQL Server). Today, we are using Rackspace (formerly Mosso) for this, but I'm not very pleased with the performance we are seeing and they don't seem to be improving the service much at all these days.
Your best bet would be to look into Windows Azure. They provide dynamically scalable facilities for .NET applications and SQL data sources. See a writeup I did here: http://joehavelick.com/post/2010/02/10/Cloud-Computing-on-the-Windows-Azure-Platform.aspx