The only real pain point I have is the fact that the Amazon EC2 servers are all behind a NAT.
As far as web applications are concerned the use of NAT is not really a problem and in fact the ability to dynamically assign the public elastic IP's is a benefit. In my case my application is a SIP based one that needs to place the public IP address in the SIP header and NAT is annoying to cope with.
I have a silverlight application hosted on an IIS instance on a Amazon EC2 server and it works fine, no issues. I do use a cross domain policy file and a custom authentication mechanism and In both cases it's just the same as a normal Windows server.
I haven't used the SimpleDB so can't comment on it but if you're looking at hosting a media library I suspect the Elastic Block Service (EBS) is what you're after. I have sometimes had a few difficulties getting my Windows server to recognise an EBS drive and have had to use the Windows Disk Manager to recognise the drive but once that's done it works perfectly and is the same as any normal Windows drive except that you can scale it up to tera or pera bytes.