Multiple Amazon EC2 With Load Balancing And MongoDB

Using a middle layer usually negates the benefits of a distributed CDN, as you will only have a few nodes in large central data centers serving the content instead of many edge-cache nodes. If your plan truly requires piping the content through a server first then skip CloudFront and just stick with S3, as you will not get any benefits from it.

A better approach is to formalize what you mean by "security etc." and see if you can get away with using the Authentication and Access Controls that are built into the CDN. S3 and Cloudfront use signatures to provide extensive control over who can access the content, and you can even create time limited URLS that will expire after a few hours or minutes. These special urls can be created by your website so that users can't deep link to the content or share the link with someone else, and usually provide good enough security that still lets you benefit from edge caching.

I am not very familiar with the Amazon documentation, but Windows Azure also has the concept of Shared Access Signatures that let you delegate permissions for uploading or downloading without ever releasing your private keys. There was a recent Cloud Cover episode on Channel 9 that does a great job of describing how to create shared access signatures and how they can be used to secure content and delegate permissions on a CDN.

Is it possible/feasible/reasonable to use HAProxy to load-balance three SQL Server 2008 database servers?

No, it isn't. And even if you could use Windows NLB, that wouldn't work reliably either.

When your application servers talk to the SQL Server, the TDS communications protocol holds 'state'. Think about transactions or long-running queries for example.

HAProxy and NLB do not understand TDS. If you just used HAProxy to load balance in TCP/IP mode, then the connection could be switched over right in the middle of a transaction -- giving you highly unpredictable results and possibly data loss.

AFAIK, there is no plug'n'play load balancer for SQL Server.