Both are for slightly different purposes -
Amazon is infrastructure as a service, which means you don't have to purchase hardware, the networking equipment and maintain them. You will get virtual machines which will host UNIX operating system or windows OS and might simply install any framework, web server or applications you want on it to use for hosting internet apps or internet services or long-running processes. You are still responsible for maintaining the operating system which means that you still need to apply upgrade patches, protect against viruses, and are completely responsible to ensure that the OS does not crash.
Windows Azure is a platform-as-a-service - you basically get windows VMs like in Amazon, but the operating system management and the application framework is also completely abstracted away from you. It allows for automatic OS upgrades and maintenance. Also, this means you just load your app and it starts working - no need to worry about the low-level details.
While this reduces the control you have over what you can install and what you can't it gives you much lesser operational overhead since you don't need a lot of administrative effort in maintaining your instances. Also scaling up and down, load balancing, auto high uptime, all these are handled by the azure service controller (or the fabric controller).
SQL Azure is one step ahead during this direction - you don't even get to contemplate a VM, you just get a particular size DB in the cloud, and you pay for what you use. The data is seemingly triple replicated and hence there is very good reliability built in. It is much cheaper to have this at least for smaller databases than to have a full-blown instance for an SQL server.
MS is additionally introducing some options like windows server vms which can offer you a choice for having amazon like instances, but I personally like the current Azure better.
So to summarize - for brand new asp.net MVC applications, with SQL server backend, windows azure is a great platform to build on. On the opposite hand, if you've got an existing website that includes a ton of dependency on legacy components that don't seem to be supported by windows azure, Amazon is a far better choice. Also, Amazon will be slightly cheaper for an instance of the same size (though the pricing structures can vary depending on the commitment you want to give).
