Imagine that you need to set up a server to work as a Web Site hosting server, or email server, or some specific server, for example, you have a service like Dropbox(I mean you are the guy that owns Dropbox for example) and you want to set the server for it. In case that there are a lot of users you need to get a high speed internet to it, than take care of hardware not to fail so you don't have downtimes, hard disk space, software updates, all the technical stuff you need to do in order to keep a server...

So what Amazon EC2, Windows Azure... do: they take care of all that, you don't even know what happens behind there or you don't even know what hardware or software is sitting there, you never get to touch the server, you just put your servers and applications on it and they take care of the hardware, bandwidth... They set up the servers for you and take care of it(backups, additional hardware in case that something fails...)

Here is a link from Microsoft site where they explain uses cases: http://www.windowsazure.com/en-us/home/scenarios/saas/

I do want to point out that almost no serious site is using Windows Azure :)
The only more serious site that is using it is a Government site in one country...

Edit: P.S just a few days ago (29 February to 1 March) all Windows Azure servers failed and had a 9-12+ hour downtime because of a Microsoft miscalculation in the leap year...